Help with custom report

[theG]

Hi Guys... SoI' ve gone through the standard charts / datasets and I can' t seem to find one that will give me browsing history. I' m running Fortianalyzer 100c with v5.0.5 I need to be able to check on browsing history for specific users. What websites and when...can anybody help me with this please?? thanks

[Rfornell]

Any ideas when trying to create the dataset I would get this? ERROR: each UNION query must have the same number of columns LINE 1: ...12608918-wlog-1394098200-d3a548" union all select * from " FG... Using this with web filter selected SELECT COALESCE(NULLIFNA(`user`), `srcip`) AS user, TO_TIMESTAMP(`itime`)::timestamp AS time, `hostname`||`url` AS url, `catdesc` FROM $log ^

[AtiT]

Hi, I copy-paste your dataset and its working on my side. The error is writing something about " union" . Do you use the UNION in your dataset? If yes then it seems that you don not have the same number of columns in the SELECTs.

[fsheriff]

Hello,

Guys this thread has helped me a lot, so thanks a lot for your posts.

I have question regarding the data sheets for the blocked and allowed apps, i encountered two error when i imported your file.

1) ipstr keyword needed to be added before srcip

2) Status column is missing, i removed this line and then i worked fine.

But the generated reported is showing only the application blocked is there a way i can generate a report based on the blocked URL's and source user.

Any help will be highly appreciated.

Regards,

[hzhao_FTNT · Accepted solution]

Hi there,

`status` field was changed to `action` after FAZ 5.0.7.

For FOS 5.0 log, please query traffic log use utmaction!='blocked' for allowed traffic

For FOS 5.2&4.3 log, please query webfilter log and use action!='blocked' for allowed traffic

To generate a report based on the blocked URL's and source user, please try:

select hostname, user_src, sum(requests) as requests from (###(select hostname, coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, count(*) as requests from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter') and hostname is not null and utmaction='blocked' group by hostname, user_src order by requests desc)### union all ###(select hostname, coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, count(*) as requests from $log-webfilter where $filter and (eventtype is null or logver = 52) and action='blocked' group by hostname, user_src order by requests desc)###) t group by hostname, user_src order by requests desc

This dataset should work for 4.3,5.0&5.2 log

Regards,

hz

[fsheriff]

Helooo,

Thanks a lot for the quey, it works like a charm.

I was not expecting a response since the post was so old, really appreciate your time and concern.

Checking out your other posts to see more sulotions and under stand the query rules

you have good one mate!!