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 7.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 7.2, 7.4, 7.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`
For FortiAnalzyer 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 articles:
- Technical Tip: How to create FortiAnalyzer reports using custom SQL queries
- Technical Tip: How to monitor the remote VPN users’ working hours
- Technical Tip: Create a custom report using Chart Builder Tool from Log View
- Creating a Custom report from FortiView (Export to Report Chart)
- Docs: VPN event logs
