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

[AtiT · Accepted solution]

Hello,

Try the dataset below for the log type Event:

SELECT UPPER(`user`) AS user_src, IPSTR(`remip`) AS remote_ip, `group` AS vpn_group, TO_CHAR(TO_TIMESTAMP(`itime`)::timestamp, 'YYYY-MM-DD HH24:MI') AS time, TO_CHAR((`duration` || 'second')::interval, 'HH24:MI:SS') AS dur FROM $log WHERE $filter AND `subtype`='vpn' AND `tunneltype`='ssl-web' AND `action`='tunnel-down' GROUP BY user_src, remote_ip, vpn_group, time, dur ORDER BY time

It is written for version 5.0.10 but probably it will be the same for 5.2x.

[neonbit]

Confirmed it works on 5.2.2.

Nice dataset!

[mwkirk]

Yup...Works fine.  This is great and has gotten almost to where i want to be.  I have a combo of SSL and IPSEC so I modified the first line to be something like this:

Select UPPER(Case when tunneltype='ssl-tunnel' then user ELSE xauthuser END) AS user_src,

This is because user is not populated for an IPSEC tunnel with the username but xauthuser is and vice-versa.  The thing I am not getting is that when doing this for the SSL connections I am getting just POSTGRES for the username but then the IPSEC ones are populating fine.

I also would like to have something like a VPN connect time and a VPN disconnect time which I am thinking I would need to somehow subtract the duration from the Timestamp to get the original connect time.

This is really great though...just need to get a few bugs ironed out for this environment.

Thanks!!

[AtiT]

Hello mwkirk,

The reason you get postgres and not the username is that you are using a simple user in your dataset. I recommend to use `user`. I recommend to use `` for every column name - this is a ALT+96 ASCII character (under ESC key on keyboard - mainly).

I am using two datasets for VPN usage one for SSL and one for IPSEC. I put them here, maybe it will help you to set you own dataset or give you some idea.

1) the sslvpn dataset is in this thread at the begining. The only note for the dataset is that it is working for web-ssl or for the tunnel-ssl when the Web mode is also enabled. In this case the fortigate will log two types of logs, one for web mode and one for tunnel mode.

If you have tunnel mode enbaled only, you shoud modify the dataset to:

AND `tunneltype`='ssl-tunnel'

For the IPSec VPN the dataset is:

SELECT user_src, remote_ip, 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

If someone find some mistake in the dataset(s) I will be happy to know about it. Thanks.

[Fullmoon]

AtiT wrote:

For the IPSec VPN the dataset is:

SELECT user_src, remote_ip, 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

hi AtiT, we highly appreciate your skills, it is very useful. Just a favor on the above syntax could  you please add also the "Date" when IPSEC tunnel established?

heres the outcome of my report based on your syntax

Username   ip add        Tunnel          Dur     tr_in    tr_out     bw

User1

124.x.x.y

IPSecTun

00:37:13

1,201,394

798,640

2,000,034

Regards,

Fullmoon

[mwkirk]

The way this would need to be done is to subtract the duration from the TimeStamp.  I have been trying to work that out so I can get a start time as well.  Been trying to get the math to work but been getting errors.  If I figure it out I will post it.

MK

[AtiT]

Hello,

Try this:

SELECT time AS start_time, user_src, remote_ip, 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 TO_CHAR(TO_TIMESTAMP(`itime`-`duration`)::timestamp, 'YYYY-MM-DD HH24:MI') AS time,  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 time, user_src, remote_ip, vpn_group, d  ORDER BY user_src ) AS a GROUP BY start_time, user_src, remote_ip, vpn_group ORDER BY bw DESC

[Fullmoon]

AtiT wrote:

Hello,

Try this:

SELECT time AS start_time, user_src, remote_ip, 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 TO_CHAR(TO_TIMESTAMP(`itime`-`duration`)::timestamp, 'YYYY-MM-DD HH24:MI') AS time, 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 time, user_src, remote_ip, vpn_group, d ORDER BY user_src ) AS a GROUP BY start_time, user_src, remote_ip, vpn_group ORDER BY bw DESC

Hi AtiT, why is that when I use this syntax NO DATA appeared in my Dataset?

[mwkirk]

Really Awesome.....I have it looking pretty much exactly the way I want it to now.  Ok...so now I add this as a chart in a Daily and Monthly VPN report.  Just started testing with the Daily one....Why does it pull data from several days back and not just Yesterday which is the timeframe I have the report setup for?

MK