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

[iyotov]

Description

This article provides examples of custom SQL datasets which 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 'fortilogd' CLI commands, 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 CLI Reference

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 in 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).

Note:

• The drilldown chart applies a hardcoded logic to exclude the entries with 0 and NULL values in the 'Volume' column. Which 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