Skip to main content
M1kemclain247
M1kemclain247
Visitor III
March 13, 2025
Question

FortiAnalyzer Custom Report for Browsing Usage based on Subnet Filters

  • March 13, 2025
  • 4 replies
  • 1867 views

Hi , I'm trying to setup a custom Report to show all users browsing activity in a customers network. 
so for example user A - consumed maybe 5gb on whatsapp - 2gb office updates etc... just to break down what their doing on the network at a glance.  and filter based on the subnet their in so for guest users see what's going execs etc... 

based on subnets e.g.: 

VLAN1 - LAN  (10.214.11.0/24)
VLAN2 - CCTV (10.213.11.0/24)
VLAN3 - GUEST (10.212.11.0/24)
VLAN4 - CORP WIFI (10.211.11.0/24)

Essentially i want to be able to just generate a report and specify the subnet when running the report. 
I have tried doing this with a custom dataset but as soon as i specify the Subnet the report generates blank content. 
Running - FAZVM64 KVM - v7.4.6 build2588 (Mature)


 

 

 

SELECT   user_src,   hostname AS website,   SUM(minutes) AS total_time_minutes,   SUM(bandwidth) AS total_bandwidth FROM   (     SELECT       count(dtime) AS minutes,       ipstr(srcip) AS user_src,       hostname,       SUM(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS bandwidth     FROM       $log-traffic     WHERE       $filter       AND srcip::inet >= '10.214.11.0'::inet       AND srcip::inet <= '10.214.11.255'::inet     GROUP BY       user_src,       hostname   ) t GROUP BY   user_src,   website ORDER BY   user_src,   total_time_minutes DESC

 

 

 



This is a sample SQL query i have tried to come up , but can only get it working when i specify inside the dataset the subnets like this. 
chart_execs.png

The Report shows this which is perfect - but noticing its also capturing some public IPs as sources. so assuming something wrong with my SQL query. 

report_execs.png

If anyone has some pointers on how i can do this effectively - and still have the ability to customize before executing the report choosing the subnet i want to run the report for - i haven't quite understood how i can pass that variable into the dataset. as seen below 
Subnets.png


FortiAnalyzer 

4 replies

Stephen_G
Staff & Editor
Stephen_G
Staff & Editor
March 16, 2025

Hello,


Thank you for using the Community Forum. I will seek to get you an answer or help. We will reply to this thread with an update as soon as possible.


Thanks,

Stephen_G - Fortinet Community Team
    Stephen_G
    Staff & Editor
    Stephen_G
    Staff & Editor
    March 19, 2025

    Hi,

     

    We're still looking for an answers or help. In the meantime, if anyone has any input on the topic, it's very welcome!

    Stephen_G - Fortinet Community Team
    M1kemclain247
    M1kemclain247Author
    Visitor III
    March 19, 2025

    Let me add this for context :

    running below query in the dataset: you can see an output of the query correctly showing results here
    Dataset Query.png

    SELECT   user_src,   srcip,   hostname AS website,   SUM(minutes) AS total_time_minutes,   SUM(bandwidth) AS total_bandwidth FROM   (     SELECT       count(dtime) AS minutes,       COALESCE(         nullifna(`user`),         nullifna(`unauthuser`)       ) AS user_src,       ipstr(`srcip`) AS srcip,       hostname,       sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS bandwidth     FROM       $log-traffic     WHERE       $filter       AND hostname IS NOT NULL       AND logid_to_int(logid) NOT IN (4, 7, 14)       AND (         countweb > 0         OR (           (             logver IS NULL             OR logver < 52           )           AND (             hostname IS NOT NULL             OR utmevent IN (               'webfilter',               'banned-word',               'web-content',               'command-block',               'script-filter'             )           )         )       )     GROUP BY       user_src,       srcip,       hostname   ) t GROUP BY   user_src,   srcip,   website HAVING   SUM(bandwidth) > 20 ORDER BY   user_src,   total_time_minutes DESC

     

    if i select the subnet in the report settings before running - it generates blank reports. 
    However if i run without specifying any subnet it returns full results - but its so large we want to only do it for specific subnets of users. 
    Specify subnet.png

      FROM       $log-traffic     WHERE       $filter


    $Filter - i would assume would contain some variables parsed into the final query when we run the report. so my assumption is that maybe the way that's being interpreted is invalid or something wrong with the syntax maybe - if there was a way we could view what the final preview query running is - that could lead us into a direction to find what's going wrong? 

     

    Example 1: Report generated with subnet specified: returns empty report 
    Blank Report.png

     

    Example 2: Report generated without specifying any subnet filter. works fine. 
    Full Report.png


    czamudio
    Staff
    czamudio
    Staff
    March 27, 2025

    Hi,

    it's a bug,  when using some netmasks, it is expected to  be fixed on 7.4.7 

     

    M1kemclain247
    M1kemclain247Author
    Visitor III
    May 10, 2025

    Hi,

    Thank you for the update.

    Could you provide clarity on when version 7.4.7 is expected to be released? The customer has been actively attempting to leverage FortiAnalyzer to generate various custom reports for their corporate environment. However, we've consistently encountered challenges, including discrepancies, inaccuracies, and empty results when applying subnet filters, particularly in the browsing usage reports and several other custom datasets.

     

    Given the importance of accurate reporting for our customer's operational needs, it's critical that we understand the timeline and whether these reporting-related issues, including subnet filtering inaccuracies, will be comprehensively addressed in the upcoming release.

     

    Your assistance and clarification on this matter would be greatly appreciated.

    Terms & ConditionsAccessibility statement