Create a VPN Tracking Report

[mwkirk]

I have looked through the canned reports and don't see a way to get this information from FortiAnalyzer.  I need a report that will run daily that gives a report of all the VPN Login/Logouts or possibly list all of the VPN logins with a duration of the login. There are some pretty decent looking reports but most of them are TopN reports.  I opened a ticket with Fortinet support and was told this requires a custom data set and that support doesn't help with creating them.  Currently running v5.2.1-build0662 141212.

Thanks

Mike

[mwkirk]

Ok with the help I received here I got a report going and it has been running for a bit now.  Then my customer asked me yesterday "When are you going to fix the VPN Report?".  So, my reply was "Fix What?"  Anyway after looking at it when an SSL VPN user connects it is showing a lot of connects disconnects for what appears the same session.   It shows the same connect time then all these disconnect times.  I am not sure what is going on with it.  I have attached an example.  I have blanked out the username/source IP but all the username/source IP is the same for all the entries on  the screenshot that I attached.

[AtiT]

Hello mwkirk,

The reason is the follows I think:

Let's say the user started a tunnel on 23.3.2015 at 22:00 and closed the tunnel on 24.3.2015 at 02:00. (4 hours)

Your report is generated at 09:00 on 24.3.2015 and you set Yesterday (in our case it means for 23.3.2015) for report generation.

So the tunnel-down information is in the logs on 24.3.2015 and you are generating report for 23.3.2015 so it cannot be obtained in the report.

The magic thing is comming now. If you generated the report a day after it means on 25.3.2015 at 09:00 the Yesterday means 24.3.2015 you will find the tunnel-down information for the tunnel started on 23.3.2015 at 22:00 so this start time will appear in the report with duration of 4 hours.

That means that the report generated on 25.3.2015 for 24.3.2015 will contain data for 23.3.2015.

Probably we cannot do anything to fix it.

[AtiT]

Hello Fullmoon,

Check whether the dataset run for Event log.

Try to use the dataset only inside the dataset:

 SELECT UPPER(`xauthuser`) AS user_src,  IPSTR(`remip`) AS remote_ip,  `xauthgroup` AS vpn_group,  `duration` AS d,  SUM(COALESCE(`rcvdbyte`, 0)) AS traffic_in,  SUM(COALESCE(`sentbyte`, 0)) AS traffic_out,  SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0)) AS bandwidth  FROM $log  WHERE $filter  AND `subtype`='vpn'  AND `tunneltype`='ipsec'  AND `action`='tunnel-down'  AND NULLIFNA(IPSTR(`tunnelip`)) IS NOT NULL  AND NULLIFNA(`xauthgroup`) IS NOT NULL  GROUP BY user_src, remote_ip, vpn_group, d  ORDER BY user_src

After than try to delete the lines:

 AND NULLIFNA(IPSTR(`tunnelip`)) IS NOT NULL  AND NULLIFNA(`xauthgroup`) IS NOT NULL

Do you see any output? Probably you will see the tunnelip or the xauthgroup as N/A. Search for the reason why.

Probably you will see the static site-2-site IPSec logs also (if you have any).

The dataset is written that the IPSec is established with the XAUTH user group selected.

[mwkirk]

Any ideas on why it shows so many connections on the SSL side for what appears to be the same session?

[m_lucchetti]

Guys sorry, is there a way to have a new version of this Dataset (which is amazing !!!!!!) which includes also SSL Vpn's ? Or is it possible to have the same Dataset for SSL Vpn's ?

SELECT user_src, remote_ip, vpn_group, vpn_group, TO_CHAR((SUM(d) || 'second')::interval, 'HH24:MI:SS') AS dur, SUM(traffic_in) AS tr_in, SUM(traffic_out) AS tr_out, SUM(bandwidth) AS bw FROM ( SELECT UPPER(`xauthuser`) AS user_src, IPSTR(`remip`) AS remote_ip, `xauthgroup` AS vpn_group, `duration` AS d, SUM(COALESCE(`rcvdbyte`, 0)) AS traffic_in, SUM(COALESCE(`sentbyte`, 0)) AS traffic_out, SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0)) AS bandwidth FROM $log WHERE $filter AND `subtype`='vpn' AND `tunneltype`='ipsec' AND `action`='tunnel-down' AND NULLIFNA(IPSTR(`tunnelip`)) IS NOT NULL AND NULLIFNA(`xauthgroup`) IS NOT NULL GROUP BY user_src, remote_ip, vpn_group, d ORDER BY user_src ) AS a GROUP BY user_src, remote_ip, vpn_group ORDER BY bw DESC