I needed to build a chart, that replicates the FortiView Vpn stats page for an older version of the analyzer. I'm really close, but it's not showing user names --- can someone help find the mistake?
select coalesce(xauthuser, user, ipstr(`remip`)) as user_src, remip, t_type as tunneltype, from_dtime(min(s_time)) as start_time, sum(duration) as duration, sum(bandwidth) as bandwidth, sum(traffic_in) as traffic_in, sum(traffic_out) as traffic_out from (select devid, vd, remip, xauthuser, user, t_type, tunnelid, min(s_time) as s_time, max(e_time) as e_time, (case when min(s_time)=max(e_time) then max(max_duration) else max(max_duration)-min(min_duration) end) as duration, (case when min(s_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(s_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 from ###(select devid, vd, remip, xauthuser, user, (case when tunneltype like 'ipsec%' then 'ipsec' else tunneltype end) as t_type, tunnelid, 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 'ssl%' or (tunneltype like 'ipsec%' and not (tunnelip is null or (tunnelip='0.0.0.0' and logver is null)))) and action in ('tunnel-stats', 'tunnel-down', 'tunnel-up') and tunnelid is not null group by devid, vd, remip, xauthuser, user, t_type, tunnelid)### t group by devid, vd, remip, xauthuser, user, t_type, tunnelid) tt group by xauthuser, user, remip, tunneltype having sum(bandwidth)>0 order by duration 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.
try replacing user with `user`
Thanks! Worked like a charm.
working with Fortinet - we corrected a couple mistakes in the dataset.
the below takes the top authenticated vpn logins default chart, and breaks it down. The Top Authenticated is the VPN - Summary View, and this is what you get when you 'dig down'.
---
select coalesce(xauthuser, `user`, ipstr(`remip`)) as user, remip, t_type as tunneltype, tunnelid, from_dtime(min(s_time)) as start_time, sum(duration) as duration, sum(bandwidth) as bandwidth, sum(traffic_in) as traffic_in, sum(traffic_out) as traffic_out from (select remip, xauthuser, `user`, t_type, tunnelid, min(s_time) as s_time, max(e_time) as e_time, (case when min(s_time)=max(e_time) then max(max_duration) else max(max_duration)-min(min_duration) end) as duration, (case when min(s_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(s_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 from ###(select remip, xauthuser, `user`, (case when tunneltype like 'ipsec%' then 'ipsec' else tunneltype end) as t_type, tunnelid, 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 'ssl%' or (tunneltype like 'ipsec%' and not (tunnelip is null or (tunnelip='0.0.0.0' and logver is null)))) and action in ('tunnel-stats', 'tunnel-down', 'tunnel-up') and tunnelid is not null group by remip, xauthuser, `user`, t_type, tunnelid)### t group by remip, xauthuser, `user`, t_type, tunnelid) tt group by xauthuser, `user`, remip, tunneltype, tunnelid having sum(bandwidth)>0 order by duration desc
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 |
---|---|
1705 | |
1093 | |
752 | |
446 | |
230 |
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.