Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
mwkirk
New Contributor

Create a VPN Tracking Report

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

 

 

1 Solution
AtiT
Valued Contributor

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.

AtiT

View solution in original post

AtiT
14 REPLIES 14
AtiT
Valued Contributor

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.

AtiT

AtiT
neonbit
Valued Contributor

Confirmed it works on 5.2.2.

 

Nice dataset!

mwkirk
New Contributor

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
Valued Contributor

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.

AtiT

AtiT
Fullmoon
Contributor III

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

Fortigate Newbie

Fortigate Newbie
mwkirk
New Contributor

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
Valued Contributor

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

 

AtiT

AtiT
Fullmoon
Contributor III

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?

Fortigate Newbie

Fortigate Newbie
mwkirk
New Contributor

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

Announcements

Select Forum Responses to become Knowledge Articles!

Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.

Labels
Top Kudoed Authors