Skip to main content
Theideman_FTNT
Staff
Theideman_FTNT
Staff
February 22, 2018
Solved

Creating Custom Web Usage report

  • February 22, 2018
  • 1 reply
  • 14762 views

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.

       

    Best answer by AtiT

    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.

    1 reply

    AtiT
    AtiT
    New Member
    February 23, 2018

    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:

     

    Theideman_FTNT
    Staff
    Theideman_FTNTAuthor
    Staff
    February 23, 2018

    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?

    AtiT
    AtiTAnswer
    New Member
    February 26, 2018

    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.

    Terms & ConditionsAccessibility statement