Hello Sir,
Can anybody help to create IPSec Client VPN Report as per attached screenshot.
Regards
Upendra Makwana.
Solved! Go to Solution.
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.
It depends on your FGT settings. Please go to FortiView-> Event->VPN, search action=tunnel-stats, if you can see results there, then your report results should be accurate. Otherwise, FAZ will only count the total bytes when "action=tunnel-down". For example, if the tunnel was up 7 days ago and down yesterday, and your report period is yesterday, then it will show 7 days total bytes on your report.
To enable tunnel-stats VPN log, pls run below CLI on FGT: config system settings set vpn-stats-log ipsec ssl set vpn-stats-period 300 end
Kamal9 wrote:Thank you very much for the query, it's work perfectly, but it is normal to get very big bandwidth like what i have in the screen shot ?
Hello,
Thnak you hzhao_FTNT for the query, but it give me some ambiguous information like a very big Bandwidth (192GB) for a single user of VPN in just two days, also i get two user name (user user2) in the column src_user instead just one. please see the attachments screenshots, i use FortiAnalyzer 200D with v5.2.2-build0706 firmware version
Hi, my dataset is for multiple users from same remote ip. If you need distinct user, pls try:
select user_src, remip, from_dtime(min(s_time)) as start_time, from_dtime(max(e_time)) as end_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,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_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, coalesce(nullifna(`xauthuser`), nullifna(`user`),'Unknown') as user_src, 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 'ipsec%' and not (tunnelip is null or (tunnelip='0.0.0.0' and coalesce(logver, 0)!=52)) and action in ('tunnel-stats', 'tunnel-down', 'tunnel-up') and tunnelid is not null group by devid, vd, remip, user_src, tunnelid order by tunnelid)### t group by devid, vd, remip, tunnelid,user_src) tt group by user_src, remip having sum(bandwidth)>0 order by duration desc
It depends on your FGT settings. Please go to FortiView-> Event->VPN, search action=tunnel-stats, if you can see results there, then your report results should be accurate. Otherwise, FAZ will only count the total bytes when "action=tunnel-down". For example, if the tunnel was up 7 days ago and down yesterday, and your report period is yesterday, then it will show 7 days total bytes on your report.
To enable tunnel-stats VPN log, pls run below CLI on FGT: config system settings set vpn-stats-log ipsec ssl set vpn-stats-period 300 end
Kamal9 wrote:Thank you very much for the query, it's work perfectly, but it is normal to get very big bandwidth like what i have in the screen shot ?
Pls try:
select outintf as PORT, from_dtime(min(s_time)) as "Start time", from_dtime(max(e_time)) as "End time", sum(duration) as duration from (select outintf, 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 from ###(select outintf, 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 from $log where $filter and subtype='vpn' and tunneltype like 'ipsec%' and not (tunnelip is null or (tunnelip='0.0.0.0' and coalesce(logver, 0)!=52)) and action in ('tunnel-stats', 'tunnel-down', 'tunnel-up') and tunnelid is not null group by outintf, tunnelid order by tunnelid)### t group by outintf,tunnelid) tt group by outintf order by duration desc
Start time will take the first available time in report period, i.e. if up time is in report period, it will record tunnel-up time, otherwise, it will record the first time of tunnel-stats. Similar for end time.
hz
Upendra wrote:Hi HZ
Thanks you very very much, Query works as per my requirement. Appreciate for prompt support.
Please help to create dataset for WAN1, WAN2 up and down. as per attached screen shot.
[attachImg]https://forum.fortinet.com/download.axd?file=0;122032&where=message&f=omega1.jpg[/attachImg]
Regards
Upendra Makwana
Includes "Date" info in this dataset:
select outintf as PORT, dom as Date, from_dtime(min(s_time)) as "Start time", from_dtime(max(e_time)) as "End time", sum(duration) as duration from (select outintf, dom, 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 from ###(select outintf, $DAY_OF_MONTH as dom, 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 from $log where $filter and subtype='vpn' and tunneltype like 'ipsec%' and not (tunnelip is null or (tunnelip='0.0.0.0' and coalesce(logver, 0)!=52)) and action in ('tunnel-stats', 'tunnel-down', 'tunnel-up') and tunnelid is not null group by outintf, dom, tunnelid order by tunnelid)### t group by outintf,dom,tunnelid) tt group by outintf,dom order by dom desc
Hi Upendra,
Suggest to query traffic log in your case for duration time. I have added bandwidth and session info in below dataset, you can remove them if you don't need.
select srcintf as PORT, dom as Date, from_dtime(min(s_time)) as "First seen", from_dtime(max(e_time)) as "Last seen", sum(bandwidth) as bandwidth, sum(traffic_in) as traffic_in, sum(traffic_out) as traffic_out, sum(sessions) as sessions, sum(duration) as duration from ###(select srcintf, $DAY_OF_MONTH as dom, min(coalesce(dtime, 0)) as s_time, max(coalesce(dtime, 0)) as e_time, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(rcvdbyte, 0)) as traffic_in, sum(coalesce(sentbyte, 0)) as traffic_out, count(*) as sessions, sum(duration) as duration from $log where $filter and logid_to_int(logid) not in (4, 7, 14) group by srcintf, dom )### t group by srcintf, dom order by dom, srcintf
Regards,
hz
Hi Hz
Query works, Thanks for giving your valuable time for me.
Regards
Upendra
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 |
---|---|
1673 | |
1083 | |
752 | |
446 | |
226 |
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.