Dataset for Last Connection Time !!!

[ihsan]

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

[hzhao_FTNT]

Please change from_dtime(min(e_time)) as end_time to from_dtime(max(e_time)) as end_time in main query.

hz