Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
sistemastda
New Contributor II

Report per user day

Good morning,

I have a MV Fortianalyzer, I saw that I can generate the report of time used by the user, but my doubt is if I can pull it retroactively.

 

viewtopic.php?f=15&t=19269

 

I need to get a report of the connected time of the users per day since 01/02/2022 I saw the general option that takes the total. but I need the consumption per day. can someone help me.

Regards

1 Solution
Debbie_FTNT

Hey sistemastda,

the dataset would have to look roughly like this, to my understanding:

 

Select user, day, min(timestamp) as first_activity, max(timestamp) as last_activity

from

###(select coalesce(user,unauthuser,srcip) as user, itime as timestamp, $DAY_OF_MONTH as day

from $log where $filter group by user)###

group by user

order by day

 

select user, day, first timestamp/last timestamp from
-> select user (or unauthuser/srcip, whatever is present), timestamp, day of the month

-> group by user (all entries for one user are grouped together)

group by user (all entries for one user are grouped together)

order by day (within grouping for user, order by day)

 

The dataset probably will NOT function if used exactly as above; it would need some finetuning and modification to meet your exact purposes.

+++ Divide by Cucumber Error. Please Reinstall Universe and Reboot +++

View solution in original post

4 REPLIES 4
Debbie_FTNT
Staff
Staff

Hey sistemastda,

- FortiAnalyzer can pull information however far back your database goes (check under Log View; at the bottom it will show you the log database containing logs going back x days)

- there are no charts/reports for connected time per user per day; creating one would be pretty complex

- there are some charts that provide sessions or bandwidth per day of the month (you would run that with a timespan of first day of the month to last day of the month). You could create a report with those charts, or use a pre-defined report, and filter for a specific user, to get the sessions or bandwidth of that user for each day of the month.

Other than that, it would take a lot of work to build and test a custom dataset, then map it to a chart and use that chart in a FortiAnalyzer report.

If you are familiar with SQL and FortiAnalyzer, I can provide a very rough sketch of what such a dataset would need to look like, but actually creating it and testing it would take several hours :\

+++ Divide by Cucumber Error. Please Reinstall Universe and Reboot +++
sistemastda

Hello,

If you can pass me the sketch to test I would appreciate it. So I try to do through SQL.

Thanks for the help.

Regards

Debbie_FTNT

Hey sistemastda,

the dataset would have to look roughly like this, to my understanding:

 

Select user, day, min(timestamp) as first_activity, max(timestamp) as last_activity

from

###(select coalesce(user,unauthuser,srcip) as user, itime as timestamp, $DAY_OF_MONTH as day

from $log where $filter group by user)###

group by user

order by day

 

select user, day, first timestamp/last timestamp from
-> select user (or unauthuser/srcip, whatever is present), timestamp, day of the month

-> group by user (all entries for one user are grouped together)

group by user (all entries for one user are grouped together)

order by day (within grouping for user, order by day)

 

The dataset probably will NOT function if used exactly as above; it would need some finetuning and modification to meet your exact purposes.

+++ Divide by Cucumber Error. Please Reinstall Universe and Reboot +++
Timmy
New Contributor

Can someone post the SQL query that works in this situation for the custom dataset?  I'm looking to produce an identical type report.

 

Thanks!

 

Tony

Announcements

Select Forum Responses to become Knowledge Articles!

Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.

Labels
Top Kudoed Authors