Technical Tip: How to generate a FortiAnalyzer report for the received log volume

Description

This article provides examples of custom SQL datasets that can be used to track the log volume received by the FortiAnalyzer.

The benefit of using a report, instead of the Dashboard widget or the CLI command 'diagnose fortilogd logvol-adom [<adom_name>|all]', is that the data can be retrieved for more than a week back.

Since the data is based on the FortiAnalyzer local event log (locallog), its quotas (separate from the device log retention policy) must also be configured accordingly:

locallog

Scope

FortiAnalyzer v7.0*, v7.2,  v7.4, v7.6**.

Solution

Start by creating a new custom Dataset under Reports -> Report Definitions  -> Datasets -> + Create New. The system Local Event log is only accessible from the 'root' ADOM, so the reports should be created in 'root':

The log_id of the volume stats is log_id='0030039004', and the data is available in 3 different formats of the message(msg) field:

msg=Used log GB/Day for Jul 28, 2025: 141.96 MB  msg=Log volume for Jul 28, 2025: 141.96 MB, 6 day(s) average: 167.14 MB  msg=Log volume of ADOM root on Jul 28, 2025: 14.73 KB, 6 days average: 15.05 KB

The msg column can be selected directly in the SQL query of the datasets to print a simple list, similar to the License Information widget, but for more days.

Query:

SELECT   msg FROM   $log WHERE   $filter   AND log_id = '0030039004'   AND msg LIKE 'Used log GB%'

Chart:

Result after adding the chart to a report:

The other two message formats can be used in a similar simplistic way, or some parsing and filtering can be added to allow the plotting of different chart types.

For example, the following query can generate a drilldown chart with the received volume per-ADOM (v7.2 and v7.4:(

Dataset query (Log Type should be FortiAnalyzer -> Local Event:(

SELECT   adom,   from_itime(`itime` -86400) :: date as datestamp,   COALESCE(convert_unit_to_num(regexp_replace("msg", '^.*: ([^,]+),.*$', '\1')), 0) AS volume FROM   $log WHERE   $filter   AND adom IS NOT NULL   AND log_id = '0030039004' GROUP BY   adom,   datestamp,   volume ORDER BY   datestamp,   adom

Chart settings with the above dataset: 

After inserting the chart in a report layout, configure the report settings as required.

Note: 

The FortiAnalyzer 'local event' log cannot be explicitly selected in the report settings. Select 'All Devices' instead, and under Advanced Settings, disable the option 'Print Device List' to avoid confusion.

In the generated report, the result should look similar to this:

Here, the Subtotal is effectively representing the total log volume received during the respective day by all active ADOMs, and the Total at the end of the table is the total log volume received by all ADOMs for the whole time range of the report (7 days in this example).

Notes:

• The drilldown chart applies a hardcoded logic to exclude the entries with 0 and NULL values in the 'Volume' column. This is why some ADOMs may be missing if they received no new logs within the report time range.
• Since the event logs for the volume stats are generated at the beginning of each day, they include the data of the previous day. So, generating a report with a time range, for example, July 20-26, will include the stats for July 19-25 in reality. Just something to have in mind when configuring the report.

Version notes for the above drilldown example:

* In version 7.0, the volume stats (log_id='0030039004') have no 'adom' column. So, additional regex is needed to parse the ADOM name from the 'msg' column, as well as different filters in the WHERE clause to exclude the global stats:

SELECT   regexp_replace("msg", '^.*Log volume of ADOM ([^ ]+).*$', '\1') AS adom,   from_itime(`itime` -86400) :: date AS datestamp,   COALESCE(convert_unit_to_num(regexp_replace("msg", '^.*: ([^,]+),.*$', '\1')), 0) AS volume FROM   $log WHERE   $filter   AND "msg" NOT LIKE 'Log volume for %'   AND "msg" NOT LIKE 'Used log GB%'   AND log_id = '0030039004' GROUP BY   msg,   datestamp,   volume ORDER BY   datestamp,   adom

** In version 7.6, some SQL functions from the previous versions are PostgreSQL-specific and are not available in the ClickHouse database. 

The following modifications are needed to convert the datestamp and the volume units:

SELECT   adom,   formatDateTime((`itime`-86400), '%F') as datestamp,   CAST(     coalesce(       (         CASE           WHEN regexp_replace(msg, '^.*: ([^,]+),.*$', '\1') LIKE '% TB' THEN CAST(             regexp_replace(msg, '^.*: ([0-9.]+) TB,.*$', '\1') AS DOUBLE PRECISION           ) * 1024 * 1024 * 1024 * 1024           WHEN regexp_replace(msg, '^.*: ([^,]+),.*$', '\1') LIKE '% GB' THEN CAST(             regexp_replace(msg, '^.*: ([0-9.]+) GB,.*$', '\1') AS DOUBLE PRECISION           ) * 1024 * 1024 * 1024           WHEN regexp_replace(msg, '^.*: ([^,]+),.*$', '\1') LIKE '% MB' THEN CAST(             regexp_replace(msg, '^.*: ([0-9.]+) MB,.*$', '\1') AS DOUBLE PRECISION           ) * 1024 * 1024           WHEN regexp_replace(msg, '^.*: ([^,]+),.*$', '\1') LIKE '% KB' THEN CAST(             regexp_replace(msg, '^.*: ([0-9.]+) KB,.*$', '\1') AS DOUBLE PRECISION           ) * 1024           WHEN regexp_replace(msg, '^.*: ([^,]+),.*$', '\1') LIKE '% B' THEN CAST(             regexp_replace(msg, '^.*: ([0-9.]+) B,.*$', '\1') AS DOUBLE PRECISION           )           ELSE NULL         END       ),       0     ) AS BIGINT   ) AS volume FROM   $log WHERE   $filter   AND adom IS NOT NULL   AND log_id = '0030039004' GROUP BY   adom,   datestamp,   volume ORDER BY   datestamp,   adom