Detailed report for all users per page

6 replies
8973 views

Dear All, this forum was helping me a lot, my question is previously our company was using ISA server and the reporting was pretty good and they need the same like report. Which is for each user they need to have site, category, hits, browsingtime, bandwidth. I tried preparing the report but the user will come on the left column and they need separate sheet for each user like in the attached report. This will be a great help if we can have it. Thank you for reading and helping me :)

Logs.JPG

jimmykcherianAnswer

New Member

Dear , I hope you got the result now, I did some more modification as follows.

1) create dataset with the query "

select (coalesce(nullifna(`user`), 'Unknown') || ' (IP : ' || coalesce(ipstr(srcip), 'Unknown') || ' * SI : ' || coalesce(nullifna(`srcname`),nullifna(`srcmac`)) || ')') as user_src, root_domain(hostname) as hostname, sum($browse_time) as browsetime, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and hostname is not null group by user_src, root_domain(hostname) having sum($browse_time)>0 order by user_src, hostname

"

2) create a chart library as attached and create a report with the chart

Let me know if you need more details.

hzhao_FTNT

Staff

Hi there, if predefined "User Report" doesn't work for you, maybe you can create a custom report, insert a macro "User Details", and add a custom chart below it. Run this report with each user filter.

Regards,

hz

jimmykcherianAuthor

New Member

Dear Hz, Thank you so much for the prompt reply, I did a dataset as follows and on chart library I select drill down as I know I can have 3 columns but arranged it by the queries dummy data. I am posting the details here so u can rectify me and it can be helpful to others also.

select distinct coalesce(nullifna(`user`)) as user_src, hostname, catdesc, '-' as seperator, 'hits' as comments, count(1) as hits, sum($browse_time2) as browsetime, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and hostname is not null group by user_src, hostname, catdesc having sum($browse_time2)>0 order by user_src, hostname, catdesc

pcraponi

New Member

jimmykcherian wrote:

select distinct coalesce(nullifna(`user`)) as user_src, hostname, catdesc, '-' as seperator, 'hits' as comments, count(1) as hits, sum($browse_time2) as browsetime, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and hostname is not null group by user_src, hostname, catdesc having sum($browse_time2)>0 order by user_src, hostname, catdesc

I'm using FortiAnalyzer 5.2.3 and your SQL give no results. I've created a dataset with Log Type TRAFFIC. What is you FAZ version?

hzhao_FTNT

Staff

browse_time2 is based on webfilter logs. If webfilter profile on FGT is "Allow" instead of "Monitor", you won't see any data from this dataset. Please change $browse_time2 to $browse_time which is from traffic logs.

regards,

hz

jimmykcherianAuthorAnswer

New Member

Dear , I hope you got the result now, I did some more modification as follows.

1) create dataset with the query "

select (coalesce(nullifna(`user`), 'Unknown') || ' (IP : ' || coalesce(ipstr(srcip), 'Unknown') || ' * SI : ' || coalesce(nullifna(`srcname`),nullifna(`srcmac`)) || ')') as user_src, root_domain(hostname) as hostname, sum($browse_time) as browsetime, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and hostname is not null group by user_src, root_domain(hostname) having sum($browse_time)>0 order by user_src, hostname

"

2) create a chart library as attached and create a report with the chart

Let me know if you need more details.

chart.JPG

pcraponi

New Member

like a charm....

Thank you