Report per user day

[sistemastda]

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

[Debbie_FTNT]

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 :\

[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 · Accepted solution]

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.

[Timmy]

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