| Solution |
In the example below, the inbound (source) and outbound (destination) bandwidth is calculated for both wan1 and wan2 interfaces
- Create the following SQL dataset that will be used to generate a report:
SELECT dom, 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 dom, 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 dom, firewall, interface
ORDER BY dom
)###
UNION ALL
###(
SELECT
$flex_timescale(itime) AS dom, 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 dom, firewall, interface
ORDER BY dom
)###) t
GROUP BY dom, firewall, interface
ORDER BY dom
- Use the dataset above in the newly created Chart (Table) and then in Report
- The report interface filter must not be used for specifying the interface as they are already part of the query
* in some cases, $DAY_OF_MONTH AS dom can be used instead of $flex_timescale(itime) AS dom
** Change interface name(s) appropriately in srcintf IN ('wan1', 'wan2') and dstintf IN ('wan1', 'wan2')
Related article:
Technical Tip: How to generate data sampling report
|
