Technical Tip: How to create a VPN report for users' connection and disconnection time

[farhanahmed]

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.

Solution

1. 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",

     (

       to_char(from_itime("itime"), 'YYYY-MM-DD HH24:MI')

     ) 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`

   

   

2. 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.
   
   

   

   

3. 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