FortiAnalyzer
FortiAnalyzer can receive logs and Windows host events directly from endpoints connected to EMS, and you can use FortiAnalyzer to analyze the logs and run reports.
farhanahmed
Staff
Staff
Article Id 298648
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`

 

1.png

 

1.png

 

  1. 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.png

 

4.png

 

  1. Go to: Reports -> Report Definitions -> All Reports -> Report -> Create New.

5.png

 

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

    6.png

     

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

    7.png

     

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

    8.png

     

    9.png

     

    10.png

     

  • Run the report from the Generated Reports tab.
  1.  

    11.png

     

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

    12.png

     

     

    • The generated report will be as seen here:

     

    13.png

     

Related articles: