Help with Dataset for Showing User Sessions for Certain Web Filter Categories

user0848271 · ‎12-15-2015

Greetings all. New Fortinet user here.

I'm hoping one of you SQL query gurus could help out with a custom dataset which meets the following requirements:

- Lists all individual blocked session details for specified web filter categories.

- Session details include: category, timestamp, user, srcip, dstip, hostname, and url.

- Sort is ascending by category and then timestamp.

- Should be able to edit the categories on the fly before running the report.

Let me know if there's more information needed.

Thanks in advance.

hzhao_FTNT · ‎12-15-2015

For FGT 5.2 log, try:

select catdesc, $calendar_time as date_time, coalesce(nullifna(`user`), 'Unknown') as username, srcip, dstip, hostname, url from $log-webfilter where $filter and (eventtype is null or logver>=52) and hostname is not null and catdesc is not null and action='blocked' order by catdesc, date_time desc

Before run report, go to report-> Advanced settings->Filter, search catdesc in filter list, and add one or more entries in "Value". Apply and run report.

View solution in original post

hzhao_FTNT · ‎12-15-2015

For FGT 5.2 log, try:

select catdesc, $calendar_time as date_time, coalesce(nullifna(`user`), 'Unknown') as username, srcip, dstip, hostname, url from $log-webfilter where $filter and (eventtype is null or logver>=52) and hostname is not null and catdesc is not null and action='blocked' order by catdesc, date_time desc

Before run report, go to report-> Advanced settings->Filter, search catdesc in filter list, and add one or more entries in "Value". Apply and run report.

user0848271 · ‎12-16-2015

Works perfectly. Thank you :)