Hi
i'm using below dataset. My problem is timestamp. Example user ihsan connected today but log show date August 28.
I connected August 28 and August 31. I want to see last connection time. Can you help me?
select coalesce(xauthuser_agg, user_agg, ipstr(`remip`)) as f_user, t_type as tunneltype, from_dtime(min(e_time)) as end_time, sum(total_num) as total_num, sum(duration) as duration from (select string_agg(distinct xauthuser_agg, ' ') as xauthuser_agg, string_agg(distinct user_agg, ' ') as user_agg, t_type, devid, vd, remip, tunnelid, min(e_time) as s_time, max(e_time) as e_time, (case when min(e_time)=max(e_time) then max(max_duration) else max(max_duration)-min(min_duration) end) as duration, (case when min(e_time)=max(e_time) then max(max_traffic_in)+max(max_traffic_out) else max(max_traffic_in)-min(min_traffic_in)+max(max_traffic_out)-min(min_traffic_out) end) as bandwidth, (case when min(e_time)=max(e_time) then max(max_traffic_in) else max(max_traffic_in)-min(min_traffic_in) end) as traffic_in, (case when min(s_time)=max(e_time) then max(max_traffic_out) else max(max_traffic_out)-min(min_traffic_out) end) as traffic_out, sum(tunnelup) as total_num from ###(select nullifna(`xauthuser`) as xauthuser_agg, nullifna(`user`) as user_agg, devid, vd, remip, (case when tunneltype like 'ipsec%' then 'ipsec' else tunneltype end) as t_type, tunnelid, sum((case when action='tunnel-up' then 1 else 0 end)) as tunnelup, min(coalesce(dtime, 0)) as s_time, max(coalesce(dtime, 0)) as e_time, max(coalesce(duration,0)) as max_duration, min(coalesce(duration,0)) as min_duration, min(coalesce(sentbyte, 0)) as min_traffic_out, min(coalesce(rcvdbyte, 0)) as min_traffic_in, max(coalesce(sentbyte, 0)) as max_traffic_out, max(coalesce(rcvdbyte, 0)) as max_traffic_in from $log where $filter and subtype='vpn' and (tunneltype like 'ipsec%' or tunneltype like 'ssl%') and action in ('tunnel-up', 'tunnel-stats', 'tunnel-down') and tunnelid is not null group by xauthuser_agg, user_agg, devid, vd, remip, t_type, tunnelid)### t group by t_type, devid, vd, remip, tunnelid having max(tunnelup) > 0) tt group by f_user, tunneltype having sum(bandwidth) > 0 order by total_num desc
Nominating a forum post submits a request to create a new Knowledge Article based on the forum post topic. Please ensure your nomination includes a solution within the reply.
Please change from_dtime(min(e_time)) as end_time to from_dtime(max(e_time)) as end_time in main query.
hz
Select Forum Responses to become Knowledge Articles!
Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.
User | Count |
---|---|
1702 | |
1092 | |
752 | |
446 | |
228 |
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.
Copyright 2024 Fortinet, Inc. All Rights Reserved.