Skip to main content
Upendra
Upendra
New Member
March 23, 2015
Solved

FortiAnalyzer Custom IPSec Client VPN Report

  • March 23, 2015
  • 6 replies
  • 32396 views

Hello Sir,

Can anybody help to create IPSec Client VPN Report as per attached screenshot.

Regards

Upendra Makwana.

 

Best answer by hzhao_FTNT

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 ?

6 replies

hzhao_FTNT
Staff
hzhao_FTNT
Staff
March 23, 2015

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

Upendra
UpendraAuthor
New Member
March 24, 2015

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.

 

 

Fullmoon
Fullmoon
New Member
March 24, 2015

 

 

I tried to copy paste the syntax above in my dataset but when click the test button "No Data" appeared. Any  guess?

hzhao_FTNT
Staff
hzhao_FTNT
Staff
March 24, 2015

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?

Upendra
UpendraAuthor
New Member
March 25, 2015

Hi hzhao

Please help to create Dataset (Query) as per attached format.

 

Regards

Upendra Makwana.

hzhao_FTNT
Staff
hzhao_FTNT
Staff
March 25, 2015

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.

Kamal9
Kamal9
New Member
June 9, 2015

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

Upendra
UpendraAuthor
New Member
March 26, 2015

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.

Regards

Upendra Makwana

 

hzhao_FTNT
Staff
hzhao_FTNT
Staff
March 26, 2015

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

 

hzhao_FTNT
Staff
hzhao_FTNT
Staff
March 26, 2015

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

Upendra
UpendraAuthor
New Member
March 27, 2015

Hi Hz

Thanks.

You create for vpn.

my requirement is wan1 and wan2 port are down from ISP. it mean when isp down and when up.

find attached screen shot for your reference.

Regards

Upendra Makwana.

 

hzhao_FTNT
Staff
hzhao_FTNT
Staff
March 27, 2015

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

Upendra
UpendraAuthor
New Member
March 30, 2015

Hi Hz

Query works, Thanks for giving your valuable time for me.

Regards

Upendra

Terms & ConditionsCookie settingsAccessibility statement