FAZ bandwidth query per vlan

[vittore]

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

[srajeswaran]

FAZ by default has following dataset - "traffic-Interface-Bandwidth-Usage" Did you try using this?

[vittore]

Thanks for your suggestion. How to filter traffic-Interface-Bandwidth-Usage only for traffic from/to wan?

[vittore · Accepted solution]

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