Technical Tip: Bandwidth Over Time for specific interface in FortiAnalyzer reports

Description

This article provides an example of a dataset that can be used to plot the traffic volume via specific FortiGate interface(s) on a Line-chart.

The query below provides inbound (source) and outbound (destination) traffic volume via the 'wan1' and 'wan2' interfaces of the FortiGate selected in the report's device filter.

Note that the values are based on the FortiGate traffic logs, which include only traffic that has matched firewall policies with 'Log All Traffic' enabled. Depending on the environment, FortiOS configuration, hardware platform, and ASIC type, there may be a significant difference between the traffic volume recorded in the FortiGate traffic logs and the one measured directly at the NICs.

Scope

FortiAnalyzer v7.4.x or older.

Solution

• Create a new custom dataset. Select Log Type = FortiGate -> Traffic, and copy the SQL query below: 

SELECT
  timestamp,
  firewall,
  interface,
  sum(byte_sent) AS total_sent,
  sum(byte_rcvd) AS total_rcvd,
  sum(byte_total) AS total
FROM
  (
    ###(
      SELECT
        $flex_timescale(itime) AS timestamp,
        devid AS firewall,
        srcintf AS interface,
        sum(COALESCE(sentbyte, 0)) AS byte_sent,
        sum(COALESCE(rcvdbyte, 0)) AS byte_rcvd,
        sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS byte_total
      FROM
        $log-traffic
      WHERE
        $filter
        AND srcintf IN ('wan1', 'wan2')
      GROUP BY
        timestamp,
        firewall,
        interface
      ORDER BY
        timestamp
    )###
    UNION ALL
    ###(
      SELECT
        $flex_timescale(itime) AS timestamp,
        devid AS firewall,
        dstintf AS interface,
        sum(COALESCE(sentbyte, 0)) AS byte_sent,
        sum(COALESCE(rcvdbyte, 0)) AS byte_rcvd,
        sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS byte_total
      FROM
        $log-traffic
      WHERE
        $filter
        AND dstintf IN ('wan1', 'wan2')
      GROUP BY
        timestamp,
        firewall,
        interface
      ORDER BY
        timestamp
    )###
  ) t
GROUP BY
  timestamp,
  firewall,
  interface
ORDER BY
  timestamp

• Create a new 'Line' Chart and then use the dataset in the chart. For example:

• Create a Report (or edit an existing report), insert the Chart in the Editor tab, and apply the changes when done.

• Access the report Settings tab to select the devices and time range.

Notes:

The report interface filter should not be used in this example, as the interfaces are already defined in the WHERE clauses of the query. If other interfaces are required, change both srcintf IN ('wan1', 'wan2') and dstintf IN ('wan1', 'wan2') in the dataset.

Or, remove the 'AND dstintf IN ('wan1', 'wan2')' from both 'WHERE' clauses. Then configure the interface filters in the report or chart settings.

The macro '$flex_timescale(itime)' provides variable time points depending on the time range of the report). It is mostly used for line-charts in which the resolution of the X-axis should change for better visualization.

To create static time points (for a table-chart), use (for example) '$hour_of_day', '$HOUR_OF_DAY', '$DAY_OF_MONTH' or some of the similar timescale macros. For more details, see Technical Tip: How to create FortiAnalyzer reports using custom SQL queries.

Throughput utilization billing reporting can be useful. This report enables users to generate the throughput consumption reporting for billing purposes by utilizing interface bandwidth consumption information logged by FortiGate. More details can be found Throughput utilization billing reporting or Technical Tip: How to generate - Throughput Utilization Billing Report.

Related articles:

• Technical Tip: How to generate data sampling report

• Technical Tip: How to create FortiAnalyzer reports using custom SQL queries 

• Technical Tip: Examples of how the FortiAnalyzer hcache query affects the report accuracy