Technical Tip: How to create a VPN report for users' connection and disconnection time
Description
This article describes creating a report to track VPN users' connection and disconnection times, for FortiAnalyzer versions 7.2 and 7.4. Below is a separate section for FortiAnalyzer v7.6.
| User | VPN Status | Time |
| User a | Connected | 2024-01-30 04:36 |
| User a | Disconnected | 2024-01-30 15:02 |
| User b | Connected | 2024-01-29 04:46 |
| User b | Disconnected | 2024-01-29 07:09 |
Scope
FortiAnalyzer v7.2, v7.4, v7.6.
Solution
- Go to Reports -> Report Definitions -> Datasets -> Create New -> give a name to the dataset, then select Log Type 'Event' and use the following dataset in the Query section.
SELECT
`user` AS "User",
(
CASE
WHEN `action` = 'tunnel-up' THEN 'Connected'
ELSE 'Disconnected'
END
) AS "VPN Status",
$calendar_time(itime) AS `Time Stamp`
FROM
$log
WHERE
$filter
AND (
(`action` = 'tunnel-up')
OR (`action` = 'tunnel-down')
)
GROUP BY
`Time Stamp`,
`action`,
`user`
ORDER BY
`user`,
`Time Stamp`
If the above query shows the IP addresses in the 'USER' column, then it may be because FortiGate is functioning either as an XAuth server or client, and logs are showing the user name as 'xauthuser'. Then replace 'user' with 'xauthuser' in the SQL query to see the usernames instead of IP addresses.
For FortiAnalyzer version 7.6, a slightly modified dataset can be used (it includes login, logout, and duration of a user as well) :
SELECT
a.`user`,
a.login,
b.logout,
b.duration,
b.transferred_bytes
FROM
###(
SELECT
`user`,
min(from_dtime(dtime)) AS login,
tunnelid
FROM
$log-event
WHERE
action = 'tunnel-up'
and $filter
GROUP BY
`user`,
tunnelid
)### a,
###(
SELECT
max(from_dtime(dtime)) AS logout,
duration,
sum(sentbyte + rcvdbyte) AS transferred_bytes,
tunnelid
FROM
$log-event
WHERE
action = 'tunnel-down'
and $filter
GROUP BY
duration,
tunnelid
HAVING
sum(sentbyte + rcvdbyte) > 0
)### b
WHERE
a.tunnelid = b.tunnelid
ORDER BY
login,
`user`
- Go to Reports -> Report Definitions -> Chart Library -> Create New -> Give a name and use the newly created dataset from the Dataset option. Under the Show Top option, set '0' to include all results in the report.
- Go to: Reports -> Report Definitions -> All Reports -> Report -> Create New.
- Give a name to the report and select Create From Blank, select 'Save to Folder' as well (All Reports by default), and select OK.
- This will open a window to edit the report settings. Set the desired settings and select Apply. Refer to the doc for details: Reports Settings tab.
- Go to the Editor, insert the newly created chart using the Insert Chart option, and select Apply.
- Run the report from the Generated Reports tab.
- After the report runs successfully, there will be options to download the report in HTML, PDF, XML, CSV, and JSON formats.
-
- The generated report will be as seen here:
Related documents: