- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
FAZ bandwidth query per vlan
We have a FortiAnalyzer and want to create a new dataset with bandwidth consumption for the previous month based on vlans port. The bandwidth consumption refers to traffic from/to vlan to/from wan port.
E.g.
vlan | consumed |
vlan10 | 123 |
vlan20 | 456 |
... | ... |
So query intercepts "internet usage" of vlan# for the previous month.
Can someone help us?
Thanks in advance.
VZ
Solved! Go to Solution.
- Labels:
-
FortiAnalyzer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I got it :) port1 is our wan interface:
WITH qry AS (
SELECT
dom AS dom_s,
devid AS devid_s,
vd AS vd_s,
srcintf,
dstintf,
total_sent,
total_rcvd
FROM
###(
SELECT
$DAY_OF_MONTH AS dom,
devid,
vd,
srcintf,
dstintf,
sum(COALESCE(sentbyte, 0)) AS total_sent,
sum(COALESCE(rcvdbyte, 0)) AS total_rcvd,
sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS total
FROM
$log
WHERE
$filter
AND (logflag & 1 > 0)
AND nullifna(srcintf) IS NOT NULL
AND nullifna(dstintf) IS NOT NULL
AND (srcintf='port1' OR dstintf='port1')
GROUP BY
dom,
devid,
vd,
srcintf,
dstintf
HAVING
sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) > 0
ORDER BY
total DESC
)### t
)
SELECT
intf,
sum(download)+ sum(upload) AS bandwidth
FROM
(
SELECT
COALESCE(t1.dom_s, t2.dom_s) AS dom,
COALESCE(t1.devid_s, t2.devid_s) AS devid,
COALESCE(t1.vd_s, t2.vd_s) AS vd,
COALESCE(t1.srcintf, t2.dstintf) AS intf,
sum(
COALESCE(t1.total_sent, 0) + COALESCE(t2.total_rcvd, 0)
) AS download,
sum(
COALESCE(t2.total_sent, 0) + COALESCE(t1.total_rcvd, 0)
) AS upload
FROM
qry t1
FULL JOIN qry t2 ON t1.dom_s = t2.dom_s
AND t1.srcintf = t2.dstintf
GROUP BY
dom,
devid,
vd,
intf
) t
WHERE
$filter-drilldown AND
intf like 'vlan%'
GROUP BY
intf
ORDER BY
intf
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
FAZ by default has following dataset - "traffic-Interface-Bandwidth-Usage" Did you try using this?
Suraj
- Have you found a solution? Then give your helper a "Kudos" and mark the solution.
Created on 08-02-2023 06:43 AM Edited on 08-02-2023 06:46 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your suggestion. How to filter traffic-Interface-Bandwidth-Usage only for traffic from/to wan?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I got it :) port1 is our wan interface:
WITH qry AS (
SELECT
dom AS dom_s,
devid AS devid_s,
vd AS vd_s,
srcintf,
dstintf,
total_sent,
total_rcvd
FROM
###(
SELECT
$DAY_OF_MONTH AS dom,
devid,
vd,
srcintf,
dstintf,
sum(COALESCE(sentbyte, 0)) AS total_sent,
sum(COALESCE(rcvdbyte, 0)) AS total_rcvd,
sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS total
FROM
$log
WHERE
$filter
AND (logflag & 1 > 0)
AND nullifna(srcintf) IS NOT NULL
AND nullifna(dstintf) IS NOT NULL
AND (srcintf='port1' OR dstintf='port1')
GROUP BY
dom,
devid,
vd,
srcintf,
dstintf
HAVING
sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) > 0
ORDER BY
total DESC
)### t
)
SELECT
intf,
sum(download)+ sum(upload) AS bandwidth
FROM
(
SELECT
COALESCE(t1.dom_s, t2.dom_s) AS dom,
COALESCE(t1.devid_s, t2.devid_s) AS devid,
COALESCE(t1.vd_s, t2.vd_s) AS vd,
COALESCE(t1.srcintf, t2.dstintf) AS intf,
sum(
COALESCE(t1.total_sent, 0) + COALESCE(t2.total_rcvd, 0)
) AS download,
sum(
COALESCE(t2.total_sent, 0) + COALESCE(t1.total_rcvd, 0)
) AS upload
FROM
qry t1
FULL JOIN qry t2 ON t1.dom_s = t2.dom_s
AND t1.srcintf = t2.dstintf
GROUP BY
dom,
devid,
vd,
intf
) t
WHERE
$filter-drilldown AND
intf like 'vlan%'
GROUP BY
intf
ORDER BY
intf