Skip to main content
PullThePin
New Member
March 26, 2020
Solved

modify "vpn-Top-SSL-Web-Users-By-Bandwidth-and-Avail" to show all ssl vpn users?

  • March 26, 2020
  • 1 reply
  • 2765 views

I need to create a report in Fortianalyzer which shows the details found in the dataset "vpn-Top-SSL-Web-Users-By-Bandwidth-and-Avail". However I need to have it list all the ssl vpn users, not just the top users. Can you please tell me how to do this?

 

This is the query for the data set:

select user_src, remip as remote_ip, from_dtime(min(s_time)) as start_time, sum(bandwidth) as bandwidth, sum(traffic_in) as traffic_in, sum(traffic_out) as traffic_out from (select devid, vd, remip, user_src, tunnelid, min(s_time) as s_time, max(e_time) as e_time, (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, coalesce(nullifna(`user`), ipstr(`remip`)) as user_src, tunnelid, min(coalesce(dtime, 0)) as s_time, max(coalesce(dtime, 0)) as e_time, 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='ssl-tunnel' and action in ('tunnel-stats', 'tunnel-down', 'tunnel-up') and coalesce(nullifna(`user`), ipstr(`remip`)) is not null and tunnelid is not null group by devid, vd, user_src, remip, tunnelid)### t group by devid, vd, user_src, remip, tunnelid) tt group by user_src, remote_ip having sum(bandwidth)>0 order by bandwidth desc

    Best answer by abelio

    Hi,

    query (dataset)   is ok;

    You'll need to adjust the chart calling it in order to obtain the report.

     

    1 reply

    abelio
    SuperUser
    abelioAnswer
    SuperUser
    March 27, 2020

    Hi,

    query (dataset)   is ok;

    You'll need to adjust the chart calling it in order to obtain the report.

     

    PullThePin
    New Member
    March 27, 2020

    Thank you so much for your help! I didn't realize that I could modify the chart to show additional users.