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 ?
Hi Upendra,
The report you desired looks like based on per session? Since each user could log in/out several time per day, it make more sense if we use start_time/end_time to record first/last seen time. Besides, Some fields like "mac address" are not available in event log. Below dataset will give you dial-up ipsec user info with start/end time, duration, bandwidth usage info.
select coalesce(xauthuser_agg, user_agg) as 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, string_agg(distinct xauthuser_agg, ' ') as xauthuser_agg, string_agg(distinct user_agg, ' ') as user_agg, 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, nullifna(`xauthuser`) as xauthuser_agg, nullifna(`user`) as user_agg, 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, xauthuser_agg, user_agg, tunnelid order by tunnelid)### t group by devid, vd, remip, tunnelid) tt group by user_src, remip having sum(bandwidth)>0 order by duration desc
Regards,
hz
Dear Sir,
Works, Thanks a lot.
Can we get destination ip also. (Which IP Address of LAN Network Client VPN User had Access?)
Regards
Upendra Makwana.
I tried to copy paste the syntax above in my dataset but when click the test button "No Data" appeared. Any guess?
Fortigate Newbie
Hi, did you set log type to event? I modified this dataset from upcoming 5.2.2, and it will use action in both tunnel-up/down and tunnel stats. I have tested it OK with FOS 4.3/5.0/5.2 log on FAZ5.2.1.
Fullmoon wrote:
I tried to copy paste the syntax above in my dataset but when click the test button "No Data" appeared. Any guess?
Please go to FortiView->Log View->Event->VPN and check if you can see logs there. If you can, then try to filter logs by action=tunnel-down or action=tunnel-stats, remember choose correct time period and set "limit" to "All" in the bottom. If you can't see any logs there, pls check your FGT settings.
Fullmoon wrote:hzhao_FTNT wrote:I tried to copy paste the syntax above in my dataset but when click the test button "No Data" appeared. Any guess?
Pls see the attached file. Im not sure if the syntax is case sensitive
This field is not available in event log. Pls try other predefined dataset with traffic log type.
Upendra wrote:Dear Sir,
Works, Thanks a lot.
Can we get destination ip also. (Which IP Address of LAN Network Client VPN User had Access?)
Regards
Upendra Makwana.
Hi Upendra,
From your screenshot, "Date" is not a good choice, since start/end time could not be the same day, and user can get enough info from start/end time. Besides, MacID/DST ip/ location are not available in event log.
For your second chart, up/down time is not recommend. For example, if you select "Yesterday" as report period, but there is one VPN connect: tunnel-up time is the day before yesterday, and tunnel-down time is today. You will not able to see any records for this connection in your report.
Try below dataset:
select outintf as PORT, from_dtime(min(s_time)) as "Start time", from_dtime(max(e_time)) as "End time", sum(duration) as duration, sum(bandwidth) as usage, remip as "LogIN Source IP" from (select outintf, remip, 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 outintf, tunnelid, remip, 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 remip, outintf, tunnelid order by tunnelid)### t group by remip, outintf,tunnelid) tt group by outintf, remip having sum(bandwidth)>0 order by duration desc
regards,
hz
Upendra wrote:Hi hzhao
Please help to create Dataset (Query) as per attached format.
[attachImg]https://forum.fortinet.com/download.axd?file=0;121959&where=message&f=omega.jpg[/attachImg]
Regards
Upendra Makwana.
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 |
---|---|
1710 | |
1093 | |
752 | |
447 | |
231 |
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.