FortiAnalyzer - needed help with custom report

[barisben]

Hi,

I am hoping someone can help me out in setting up a custom report. I want to get a report via FortiAnalyzer that includes source user source interface or vlan and destination user destination interface or vlan bandwidth information. How can I do this?

Thanks in advance,

[Sheikh]

Hello @barisben 

Take a look at this article 

regards,

Sheikh

[jasonhong]

Hi,

You can refer to below KB guide on how to create your own report using the Chart Builder Tool.

https://community.fortinet.com/t5/FortiAnalyzer/Technical-Tip-Create-a-custom-report-using-Chart-Bui...

[achu]

Hi @barisben 

To get a report via FortiAnalyzer that includes source user, source interface or VLAN, destination user, destination interface or VLAN, and bandwidth information, you can follow these steps:

1. Go to Reports > Report Definitions > Templates.
2. You can clone existing template such as "traffic-Interface-Bandwidth-Usage" and add destination for the variables etc. as needed.
3. Click on the More dropdown and select Create Report to generate the report.
4. Once the report is available, you can view it in the desired format (PDF, HTML, CSV, XML) to access the information you need.

By selecting the appropriate template and generating the report, you should be able to access the source and destination user, interface or VLAN, and bandwidth information as per your requirement.

[barisben]

Actually, I think I was misunderstood. In other words, I do not want to take action regarding a specific source or destination. That is the what I want to customize chart "Top Source with Details by Bandwidth". For example, for the user in 1st line, I want to see which destination IP and destination port this source uses this bandwidth on the most. Towards which destination ip and destination port did source use the most bandwidth in this total bandwidth? Therefore, the correlation between source and destination is important for this query. As you can see;

And the dataset in this chart is "360-security-Top-Source-Details";

SELECT
  f_user,
  string_agg(DISTINCT srcintf, ',') AS srcintf,
  string_agg(DISTINCT dev_src, ',') AS dev_src,
  sum(threatwgt) AS threatweight,
  sum(threat_block) AS threat_block,
  (sum(threatwgt) -sum(threat_block)) AS threat_pass,
  sum(bandwidth) AS bandwidth,
  sum(sessions) AS sessions
FROM
  ###(
    SELECT
      COALESCE(
        nullifna(`user`),
        nullifna(`unauthuser`),
        ipstr(`srcip`)
      ) AS f_user,
      srcintf,
      max(COALESCE(srcname, srcmac)) AS dev_src,
      sum(threatwgt) AS threatwgt,
      sum(
        CASE
          WHEN (logflag & 2 > 0) THEN threatwgt
          ELSE 0
        END
      ) AS threat_block,
      sum(
        COALESCE(sentdelta, sentbyte, 0) + COALESCE(rcvddelta, rcvdbyte, 0)
      ) AS bandwidth,
      sum(
        CASE
          WHEN (logflag & 1 > 0) THEN 1
          ELSE 0
        END
      ) AS sessions
    FROM
      (
        SELECT
          `user`,
          unauthuser,
          srcip,
          srcintf,
          srcname,
          srcmac,
          threatweight_sum(threatwgts, threatcnts) AS threatwgt,
          sentdelta,
          sentbyte,
          rcvddelta,
          rcvdbyte,
          logflag
        FROM
          $log-traffic
        WHERE
          $filter
          AND (logflag & (1 | 32) > 0)
      ) t
    GROUP BY
      f_user,
      srcintf
    ORDER BY
      bandwidth DESC
  )### t
WHERE
  f_user IS NOT NULL
GROUP BY
  f_user
ORDER BY
  bandwidth DESC

[barisben]

I still need a solution.

[barisben]

I try to use WITH and gives an error "'group by' or 'order by' clause is expected in hcache" with the every solution I tried. This is the last query I tried; We also ask for help from people interested in SQL, but we cannot solve it. So FortiNet Support Global can't help us too. Who writes the datasets in the reports section? Isn't he/she an staff of Fortinet?

WITH CTE as (
        SELECT
             `user`, unauthuser, srcip, srcintf, srcname, srcmac
            , threatweight_sum(threatwgts, threatcnts) AS threatwgt
            , sentdelta, sentbyte, rcvddelta, rcvdbyte, logflag, dstip, dstintf
        FROM $log-traffic
        WHERE $filter
            AND (logflag & (1 | 32) > 0)
        )
SELECT
      fu.*
    , ud.dstip
    , ud.dstintf
    , ud.top_bandwidth
FROM (
    SELECT
          u.f_user
        , STRING_AGG(DISTINCT u.srcintf, ',') AS srcintf
        , STRING_AGG(DISTINCT u.dev_src, ',') AS dev_src
        , SUM(u.threatwgt) AS threatweight
        , SUM(u.threat_block) AS threat_block
        , (SUM(u.threatwgt) - SUM(threat_block)) AS threat_pass
        , SUM(u.bandwidth) AS bandwidth
        , SUM(u.sessions) AS sessions
    FROM (
        SELECT
              COALESCE(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) AS f_user
            , srcintf
            , MAX(COALESCE(srcname, srcmac)) AS dev_src
            , SUM(threatwgt) AS threatwgt
            , SUM(CASE WHEN (logflag & 2 > 0) THEN threatwgt ELSE 0 END) AS threat_block
            , SUM(COALESCE(sentdelta, sentbyte, 0) + COALESCE(rcvddelta, rcvdbyte, 0)) AS bandwidth
            , SUM(CASE WHEN (logflag & 1 > 0) THEN 1 ELSE 0 END) AS sessions
        FROM CTE
        GROUP BY
              f_user
            , srcintf
        ) u
    WHERE u.f_user IS NOT NULL
    GROUP BY u.f_user
    ) fu
INNER JOIN (
        SELECT
              f_user, dstip, dstintf, top_bandwidth
            , row_number() over(partition by f_user, dstip, dstintf order by top_bandwidth DESC) as rnk
        FROM (
            SELECT
                  COALESCE(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) AS f_user
                , dstip
                , dstintf
                , SUM(COALESCE(sentdelta, sentbyte, 0) + COALESCE(rcvddelta, rcvdbyte, 0)) AS top_bandwidth
            FROM CTE
            GROUP BY
                  f_user
                , dstip
                , dstintf
            ) d
    ) ud on fu.f_user = ud.f_user and ud.rnk = 1
ORDER BY fu.bandwidth DESC

[barisben]

I've tried like this and didn't get any error but it says there is no matching log data. Is there any problem with the query? Anyone has related to queries?

SELECT
  fu.f_user,
  string_agg(DISTINCT fu.srcintf, ',') AS srcintf,
  string_agg(DISTINCT fu.dev_src, ',') AS dev_src,
  SUM(fu.bandwidth) AS bandwidth,
  string_agg(DISTINCT ud.dstip :: TEXT, ',') AS dstip,
  string_agg(DISTINCT ud.dstintf, ',') AS dstintf
FROM
  (
    SELECT
      u.f_user,
      STRING_AGG(DISTINCT u.srcintf, ',') AS srcintf,
      STRING_AGG(DISTINCT u.dev_src, ',') AS dev_src,
      SUM(u.threatwgt) AS threatweight,
      SUM(u.threat_block) AS threat_block,
      (SUM(u.threatwgt) - SUM(threat_block)) AS threat_pass,
      SUM(u.bandwidth) AS bandwidth,
      SUM(u.sessions) AS sessions
    FROM
      (
        SELECT
          COALESCE(
            nullifna(`user`),
            nullifna(`unauthuser`),
            ipstr(`srcip`)
          ) AS f_user,
          srcintf,
          MAX(COALESCE(srcname, srcmac)) AS dev_src,
          SUM(threatwgt) AS threatwgt,
          SUM(
            CASE
              WHEN (logflag & 2 > 0) THEN threatwgt
              ELSE 0
            END
          ) AS threat_block,
          SUM(
            COALESCE(sentdelta, sentbyte, 0) + COALESCE(rcvddelta, rcvdbyte, 0)
          ) AS bandwidth,
          SUM(
            CASE
              WHEN (logflag & 1 > 0) THEN 1
              ELSE 0
            END
          ) AS sessions
        FROM
          (
            SELECT
              `user`,
              unauthuser,
              srcip,
              srcintf,
              srcname,
              srcmac,
              threatweight_sum(threatwgts, threatcnts) AS threatwgt,
              sentdelta,
              sentbyte,
              rcvddelta,
              rcvdbyte,
              logflag
            FROM
              $log-traffic
            WHERE
              $filter
              AND (logflag & (1 | 32) > 0)
          ) sq
        GROUP BY
          f_user,
          srcintf
      ) u
    WHERE
      u.f_user IS NOT NULL
    GROUP BY
      u.f_user
  ) fu
  INNER JOIN (
    SELECT
      f_user,
      dstip,
      dstintf,
      top_bandwidth,
      row_number() OVER(
        PARTITION BY f_user,
        dstip,
        dstintf
        ORDER BY
          top_bandwidth DESC
      ) AS rnk
    FROM
      (
        SELECT
          COALESCE(
            nullifna(`user`),
            nullifna(`unauthuser`),
            ipstr(`srcip`)
          ) AS f_user,
          dstip,
          dstintf,
          SUM(
            COALESCE(sentdelta, sentbyte, 0) + COALESCE(rcvddelta, rcvdbyte, 0)
          ) AS top_bandwidth
        FROM
          (
            SELECT
              `user`,
              unauthuser,
              srcip,
              sentdelta,
              sentbyte,
              rcvddelta,
              rcvdbyte,
              logflag,
              dstip,
              dstintf
            FROM
              $log-traffic
            WHERE
              $filter
              AND (logflag & (1 | 32) > 0)
          ) sq
        GROUP BY
          f_user,
          dstip,
          dstintf
      ) d
  ) ud ON fu.f_user = ud.f_user
  AND ud.rnk = 1
GROUP BY
  fu.f_user
ORDER BY
  bandwidth DESC