Skip to main content
Terrence_M
Terrence_M
New Member
October 14, 2017
Question

Can you help me fix the user name showing up as postgres>

  • October 14, 2017
  • 2 replies
  • 4802 views

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

    2 replies

    RobertReynolds
    RobertReynolds
    New Member
    October 15, 2017

    try replacing user with `user`

     

     

    Terrence_M
    Terrence_MAuthor
    New Member
    October 20, 2017

    Thanks! Worked like a charm.

    Terrence_M
    Terrence_MAuthor
    New Member
    October 22, 2017

    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

    Terms & ConditionsAccessibility statement