Fortianalyzer Custom Query Help

[dnurse]

Hi,

I'm trying to get a query that shows each user's ssl or ipsec vpn session login and logout time and the bandwidth used. The columns in the output should look like the attached capture.jpg. I think I am close with the query below. Please tell me if i am wrong.

1) I am seeing several entries with no username in the user_agg column

2) I would like to have a bandwidth column 

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, from_dtime(min(coalesce(dtime, 0))) as s_time, from_dtime(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, tunnel