A network device called FortiAnalyzer, there are ready-made queries for obtaining internet usage reports, and we can also customize them ourselves(uses postgresql). For example, this ready-made query gives us the total bandwidth amount (and etc like threat_block. but sorts by bandwidth, its important) used by source users within the date ranges we specify (before running the query in the GUI).

However, I want to customize it to also find out the destination IP (dstip) and destination interface (dstintf) that each source user has spent the most bandwidth on from their total bandwidth amount. (That is, from each source user’s total bandwidth data, only find out the destination that used the most bandwidth). I made the following update, but in this form, it does not give me the total bandwidth amount spent by the source and sort accordingly. It sorts by the bandwidth usage to the destinations. What I want is to show the total bandwidth usage of the source user at the end, sort by it, and also find the highest destination for this record in the total source bandwidth usage.

Could you assist with this?

The example ready-made;

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

And this is the what I customized;

SELECT
  f_user,
  string_agg(DISTINCT srcintf, ',') AS srcintf,
  string_agg(DISTINCT dev_src, ',') AS dev_src,
  sum(bandwidth) AS bandwidth,
  sum(sessions) AS sessions,
  string_agg(DISTINCT dstip :: TEXT, ',') AS dstip,
  string_agg(DISTINCT dstintf, ',') AS dstintf
FROM
  (
    SELECT
      f_user,
      srcintf,
      dev_src,
      bandwidth,
      sessions,
      dstip,
      dstintf,
      ROW_NUMBER() OVER (
        PARTITION BY f_user
        ORDER BY
          bandwidth DESC
      ) AS rnk
    FROM
      (
        SELECT
          COALESCE(
            nullifna(user),
            nullifna(unauthuser),
            ipstr(srcip)
          ) AS f_user,
          srcintf,
          COALESCE(srcname, srcmac) AS dev_src,
          sum(
            COALESCE(sentdelta, sentbyte, 0) + COALESCE(rcvddelta, rcvdbyte, 0)
          ) AS bandwidth,
          sum(
            CASE
              WHEN (logflag & 1 > 0) THEN 1
              ELSE 0
            END
          ) AS sessions,
          dstip,
          dstintf
        FROM
          $log-traffic
        WHERE
          $filter
          AND (logflag & (1 | 32) > 0)
        GROUP BY
          f_user,
          srcintf,
          dev_src,
          dstip,
          dstintf
      ) t
  ) ranked_data
WHERE
  rnk = 1
GROUP BY
  f_user
ORDER BY
  bandwidth DESC