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

Creating Custom Web Usage report

I have a customer that would like a web usage report in a format like such;

 

Top source (user)

       Most requested site (or bandwidth used)

       2 most requested site (or bandwidth used)

       3 most requested site (or bandwidth used)

       4  etc...

       5  etc...

2nd top source (user)

       Most requested site (or bandwidth used)

       2  etc...

       3  etc...

 

I think you get the idea.

 

I've been messing with the charts and think either the "Ranked" or "Drilldown" might be the way, but not having any luck. Also have not found any good explanation on what "Ranked" or "Drilldown" means.

 

Any help would be appreciated.

       

Thomas Heideman
1 Solution
AtiT
Valued Contributor

Hello. Thank you.

It is hard to say how to learn to write queries. I am still not good at it. It is better to deploy a fortianalyzer VM trial version (free for 14 days) and test the SQL queries on that machine, not in production.

Also you can have a good inspiration from the predefined datasets and the documentation on the site:

https://docs.fortinet.com/fortianalyzer/reference

 

There is a Dataset Reference document.

AtiT

View solution in original post

AtiT
3 REPLIES 3
AtiT
Valued Contributor

Hello, I am not sure whether this is what you need but could you please look at this solution. Maybe it will give you (or someone) a hint or idea how to do it.

Currently I am testing the following dataset and chart settings. It is for domains, not websites, you can remove the root_domain() function from it.

 

replace the line: ELSE ROOT_DOMAIN(SPLIT_PART(`hostname`,':',1))

with the line:  ELSE SPLIT_PART(`hostname`,':',1)

 

Also you can remove the URL condition (  AND `url` !~* ' ....... ). I am removing the images, styles, scripts etc, from the results.

 

Webfilter dataset:

SELECT COALESCE(NULLIFNA(`user`), IPSTR(`srcip`)) AS user_src, (CASE SPLIT_PART(`hostname`,':',1)   WHEN IPSTR(`dstip`) THEN IPSTR(`dstip`)   ELSE ROOT_DOMAIN(SPLIT_PART(`hostname`,':',1)) END) AS website, COUNT(*) AS totalnum FROM $log WHERE $filter AND `action` IN ('passthrough') AND `url` !~* '(\.bmp|\.css|\.dtd|\.ico|\.gif|\.jpeg|\.jpg|\.js|\.json|\.jsp|\.png|\.svg|\.svgx|\.woff|\.woff2|\.xslt)$' GROUP BY user_src, website ORDER BY totalnum DESC

 

Chart settings and results:

 

AtiT

AtiT
Theideman_FTNT

Excellant. 

 

I tried it and it looks pretty good. 

 

A bigger question though, it seems like if you want to create these types of custom reports, knowing SQL to create Datasets seems to be the key. However, most (if not all) of my customer do not know SQL. Is there a good way to learn to create datasets, such as the one you created for this report?

Thomas Heideman
AtiT
Valued Contributor

Hello. Thank you.

It is hard to say how to learn to write queries. I am still not good at it. It is better to deploy a fortianalyzer VM trial version (free for 14 days) and test the SQL queries on that machine, not in production.

Also you can have a good inspiration from the predefined datasets and the documentation on the site:

https://docs.fortinet.com/fortianalyzer/reference

 

There is a Dataset Reference document.

AtiT

AtiT
Labels
Top Kudoed Authors