Examples of custom datasets

The following examples illustrate how to write custom datasets.

After you create the datasets, you can use them when you configure chart templates under Reports > Report Definitions > Chart Library. Configured charts can be selected when creating or modifying reports.

For more information on creating charts and reports, see the FortiAnalyzer Administration Guide.

Example 1: Packets denied by UTM for a source or destination matching '172.30.xx.xx':

1. Go to Reports > Report Definitions > Datasets, and click Create New.
2. Enter a name for the dataset, for example: packet_denied_by_UTM_or_dest.
3. Under Log Type, select Traffic.
4. Configure Time Period and Devices.
5. Under Query, enter the following:

   select vd, srcip, dstip, srcport, dstport, srcintf, dstintf, service, msg, count(*) as totalnum

   from $log

   where $filter and utmaction='block' and (ipstr(srcip) like '172.30%' or ipstr(dstip) like '172.30%')

   group by srcip, dstip, srcport, dstport, srcintf, dstintf, vd, service, msg

   order by totalnum desc

   

Example 2: Top 10 hosts that had the most packets dropped with error message 'no session matched' or 'replay packet(allow_err), drop':

1. Go to Reports > Report Definitions > Datasets, and click Create New.
2. Enter a name for the dataset, for example: top_10_packet_dropped_with_error.
3. Under Log Type, select Traffic.
4. Configure Time Period and Devices.
5. Under Query, enter the following:

   Select vd, srcip, dstip, srcport, dstport, srcintf, dstintf, service, msg, count(*) as totalnum

   from $log

   where $filter and (msg='no sessions matched' OR msg='replay packet(allow_err), drop')

   group by srcip, dstip, srcport, dstport, srcintf, dstintf, vd, service, msg

   order by totalnum desc

   

Example 3: Top 10 traffic shapers by dropped bytes

1. Go to Reports > Report Definitions > Datasets, and click Create New.
2. Enter a name for the dataset, for example: top_10_traffic_shapers_by_dropped_bytes.
3. Under Log Type, select Traffic.
4. Configure Time Period and Devices.
5. Under Query, enter the following:

   select shapersentname, shapingpolicyid, sum(coalesce(shaperdroprcvdbyte, 0)) as dropped_rcvd, sum(coalesce(shaperdropsentbyte, 0)) as dropped_sent, (sum(coalesce(shaperdroprcvdbyte, 0))+sum(coalesce(shaperdropsentbyte, 0))) as dropped_total

   from $log where $filter and (logflag&1>0) and shapingpolicyid is not null

   group by shapersentname, shapingpolicyid

   order by dropped_total desc

   limit 10