SSL-Tunnel VPN User Report

[prosysmike]

Hi All,

I need a granular report that shows logon times and logoff times for the SSL VPN Users.

The datasets I have been trying dont seem to be the most recent. Ideally I would like a report showing when users logged in, for how long and when they logged off and from which IP.

The standard report only seems to be providing me with two days data as the cache is sitting at 100% for that report, not sure if this will impact my duration of history for the report?

[chall_FTNT]

Logview >> Chart Builder won't include all the fields you are looking for.

Either use multiple predefined datasets or combine them into a customized one.

Predefined dataset: vpn-User-Login-history ... group by timestamp, devid, vd, remip, tunnelid

(has timestamp but not user or duration)

Predefined dataset: vpn-Top-Dial-Up-VPN-Users-By-Duration

... group by devid, vd, remip, t_type, tunnelid) tt where bandwidth>0 group by user_src, tunneltype order by duration desc

[AtiT]

Hello,

maybe something like this?

SELECT `user` AS vpn_user,             `remip` AS peerip,             `duration` AS vpn_dur,              FROM_ITIME(`itime`-`duration`) AS start_time,              FROM_ITIME(`itime`) AS end_time,             `tunneltype` AS vpn_type FROM $log WHERE $filter             AND `subtype`='vpn'             AND `tunneltype` IN ('ssl-tunnel','ssl-web')             AND `action` = 'tunnel-down'             AND COALESCE(NULLIFNA(`user`), IPSTR(`remip`)) IS NOT NULL             AND `tunnelid` IS NOT NULL GROUP BY vpn_user,                 peerip,                 vpn_dur,                 start_time,                 end_time,                 vpn_type

[chall_FTNT]

I think the WHERE condition `action` = 'tunnel-down' will be too restrictive. And usually there is an "Order by".

Otherwise, in theory, that looks okay.  

[chall_FTNT]

For more examples of SQL dataset customization, see:

https://fndn.fortinet.net/index.php?/forums/forum/79-fortianalyzer-sample-sql-queries/

note: for FNDN access, you will need the help of your Fortinet sales team to setup an account

[chall_FTNT]

Specifically, there is an example there that directly addresses your scenario: VPN Tunnel User Login-Logout Info

https://fndn.fortinet.net/index.php?/forums/topic/1485-vpn-tunnel-user-login-logout-info/

[jpalmer99 · Accepted solution]

Not all of us have access to that site.  Would it be possible to post information here? Thank you.