Description
This article describes creating a report to track VPN users' connection and disconnection times.
|
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.4.
Solution
- Go to Reports -> Report Definitions -> Datasets -> Create New -> give a name to the dataset then Select Log Type 'Event' and use the below 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`
- 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)
