FortiAnalyzer自定义PostgreSQL查询：用户总带宽及最高带宽目标需求

需求：生成互联网使用报告，需展示每个源用户的总带宽用量（按该值降序排序），同时显示该用户总带宽中消耗最多的目标IP（dstip）和目标接口（dstintf）。

原自定义查询的问题 #

原查询中，ROW_NUMBER()是基于用户下单个(dstip,dstintf)的带宽值排序，外层sum(bandwidth)实际取的是该top1目标的带宽，而非用户的总带宽，导致排序和总带宽统计错误。

修正后的查询 #

WITH user_total AS (
    -- 计算每个用户的总带宽、总会话数等全局统计
    SELECT
        COALESCE(nullifna(`user`), nullifna(unauthuser), ipstr(srcip)) AS f_user,
        STRING_AGG(DISTINCT srcintf, ',') AS srcintf,
        STRING_AGG(DISTINCT COALESCE(srcname, srcmac), ',') AS dev_src,
        SUM(COALESCE(sentdelta, sentbyte, 0) + COALESCE(rcvddelta, rcvdbyte, 0)) AS total_bandwidth,
        SUM(CASE WHEN (logflag & 1 > 0) THEN 1 ELSE 0 END) AS total_sessions
    FROM $log-traffic
    WHERE $filter AND (logflag & (1 | 32) > 0)
    GROUP BY f_user
),
user_dst_stats AS (
    -- 计算每个用户下各目标IP+接口的带宽消耗
    SELECT
        COALESCE(nullifna(`user`), nullifna(unauthuser), ipstr(srcip)) AS f_user,
        dstip::TEXT,
        dstintf,
        SUM(COALESCE(sentdelta, sentbyte, 0) + COALESCE(rcvddelta, rcvdbyte, 0)) AS dst_bandwidth,
        -- 给每个用户下的目标按带宽降序排名
        ROW_NUMBER() OVER (PARTITION BY f_user ORDER BY SUM(COALESCE(sentdelta, sentbyte, 0) + COALESCE(rcvddelta, rcvdbyte, 0)) DESC) AS rnk
    FROM $log-traffic
    WHERE $filter AND (logflag & (1 | 32) > 0)
    GROUP BY f_user, dstip, dstintf
)
-- 关联总统计和top1目标数据
SELECT
    ut.f_user,
    ut.srcintf,
    ut.dev_src,
    ut.total_bandwidth AS bandwidth,
    ut.total_sessions AS sessions,
    uds.dstip,
    uds.dstintf
FROM user_total ut
JOIN user_dst_stats uds ON ut.f_user = uds.f_user
WHERE uds.rnk = 1
ORDER BY ut.total_bandwidth DESC;

关键说明 #

• user_total CTE：单独计算每个用户的总带宽、总会话数等全局指标，这部分是最终排序的依据
• user_dst_stats CTE：统计每个用户下每个目标IP+接口的带宽消耗，并用ROW_NUMBER()标记出每个用户下带宽最高的目标（rnk=1）
• 最后通过用户ID关联两个CTE，确保展示的是用户总带宽，同时附带其最高消耗的目标信息

内容的提问来源于stack exchange，提问作者Baris