Technical Tip: Excluding the intermediate traffic logs in report

[heng]

Description
This article described how to exclude the intermediate traffic log sent by FortiGate by fine tuning the SQL query dataset in the FortiAnalyzer.
The additional logs are 'interim' logs for long live sessions, it is  generated every 2 minutes and they are identified in the logs as logid=20 with action=accept.

This log type was introduced after FortiGate version 5.6 and above.
If not being excluded, those logs may cause incorrect traffic statistics in the generated report.
If there is no traffic in FortiGate within 2 minutes, the next packet received will trigger the log.
When a session is closed, the log entry will appear just before the expected log message with firewall action equal to action=close.

Solution
1) To filter out these log records in dataset, the following has to be added to the SQL query WHERE clause statement.
For FortiAnalyzer, pre-6.2 version’s dataset such as version 5.6 and 6.0, it do not have this added, it is possible to clone the pre-defined dataset or add in the custom dataset.

This is a special string where it will excludes the intermediate long session logs, records for session start and other types of logs that are excluded from the traffic statistics.

…
where $filter and (logflag&1>0)
…

Example:  

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 20

2) Alternatively, it is possible to do it from FortiGate to filter out this log ID from sending to FortiAnalyzer.  

# config log fortianalyzer filter
    set filter "logid(00020)"
    set filter-type exclude
end