Helpful ReplyHot!Create a VPN Tracking Report

Author
mwkirk
New Member
  • Total Posts : 18
  • Scores: 0
  • Reward points: 0
  • Joined: 2015/03/02 06:10:12
  • Status: offline
2015/03/02 19:41:00 (permalink)
0

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
AtiT
Platinum Member
  • Total Posts : 466
  • Scores: 40
  • Reward points: 0
  • Joined: 2012/04/18 12:13:27
  • Location: Prague / Czech Republic
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/03 07:29:49 (permalink) ☄ Helpfulby neonbit 2015/03/03 18:38:16
5 (1)
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
--------------------
NSE 8, CCNP R+S
#2
neonbit
Expert Member
  • Total Posts : 517
  • Scores: 67
  • Reward points: 0
  • Joined: 2013/07/02 21:39:52
  • Location: Dark side of the moon
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/03 18:38:06 (permalink)
0
Confirmed it works on 5.2.2.
 
Nice dataset!
#3
mwkirk
New Member
  • Total Posts : 18
  • Scores: 0
  • Reward points: 0
  • Joined: 2015/03/02 06:10:12
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/17 05:32:58 (permalink)
0
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!!
#4
AtiT
Platinum Member
  • Total Posts : 466
  • Scores: 40
  • Reward points: 0
  • Joined: 2012/04/18 12:13:27
  • Location: Prague / Czech Republic
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/17 08:12:05 (permalink)
0
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
--------------------
NSE 8, CCNP R+S
#5
Fullmoon
Platinum Member
  • Total Posts : 851
  • Scores: 13
  • Reward points: 0
  • Joined: 2010/08/02 18:02:10
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/20 01:28:38 (permalink)
0
AtiT
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
post edited by Fullmoon - 2015/03/20 01:35:24

Fortigate Newbie
#6
mwkirk
New Member
  • Total Posts : 18
  • Scores: 0
  • Reward points: 0
  • Joined: 2015/03/02 06:10:12
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/21 22:41:41 (permalink)
0
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
#7
AtiT
Platinum Member
  • Total Posts : 466
  • Scores: 40
  • Reward points: 0
  • Joined: 2012/04/18 12:13:27
  • Location: Prague / Czech Republic
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/22 12:54:22 (permalink)
5 (1)
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
--------------------
NSE 8, CCNP R+S
#8
mwkirk
New Member
  • Total Posts : 18
  • Scores: 0
  • Reward points: 0
  • Joined: 2015/03/02 06:10:12
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/23 11:33:15 (permalink)
0
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
#9
Fullmoon
Platinum Member
  • Total Posts : 851
  • Scores: 13
  • Reward points: 0
  • Joined: 2010/08/02 18:02:10
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/23 23:59:53 (permalink)
0
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
 




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

Fortigate Newbie
#10
AtiT
Platinum Member
  • Total Posts : 466
  • Scores: 40
  • Reward points: 0
  • Joined: 2012/04/18 12:13:27
  • Location: Prague / Czech Republic
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/25 08:52:38 (permalink)
0
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
--------------------
NSE 8, CCNP R+S
#11
AtiT
Platinum Member
  • Total Posts : 466
  • Scores: 40
  • Reward points: 0
  • Joined: 2012/04/18 12:13:27
  • Location: Prague / Czech Republic
  • Status: offline
Re: Create a VPN Tracking Report 2015/03/25 08:59:38 (permalink)
0
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.

AtiT
--------------------
NSE 8, CCNP R+S
#12
mwkirk
New Member
  • Total Posts : 18
  • Scores: 0
  • Reward points: 0
  • Joined: 2015/03/02 06:10:12
  • Status: offline
Re: Create a VPN Tracking Report 2015/05/06 04:55:07 (permalink)
0
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.

Attached Image(s)

#13
mwkirk
New Member
  • Total Posts : 18
  • Scores: 0
  • Reward points: 0
  • Joined: 2015/03/02 06:10:12
  • Status: offline
Re: Create a VPN Tracking Report 2015/06/15 08:05:07 (permalink)
0
Any ideas on why it shows so many connections on the SSL side for what appears to be the same session?
#14
m.lucchetti
New Member
  • Total Posts : 1
  • Scores: 0
  • Reward points: 0
  • Joined: 2019/08/20 00:29:12
  • Status: offline
Re: Create a VPN Tracking Report 2019/08/20 00:31:24 (permalink)
0
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
#15
Jump to:
© 2019 APG vNext Commercial Version 5.5