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

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.

1. 

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

1. 

• Go to the Editor, insert the newly created chart using the Insert Chart option, and select Apply.

1. 

   

   

• Run the report from the Generated Reports tab.

1. 

• After the report runs successfully, there will be options to download the report in HTML, PDF, XML, CSV, and JSON formats.

1. 

   • 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