Create a VPN Tracking Report

Confirmed it works on 5.2.2.

Nice dataset!

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!!

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.

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

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

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

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.

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.

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