Function _qs

tools/scan_bot_traffic.py:95–216

(source: str)

def _qs(source: str) -> dict[str, str]:
    return {
        # 1. Top user agents.
        "top_user_agents": f"""
            SELECT
                JSONExtractString(raw, 'message_json', 'request', 'headers', 'User-Agent', 1) AS ua,
                count() AS count,
                uniqExact(JSONExtractString(raw, 'message_json', 'request', 'client_ip')) AS distinct_ips,
                round(countIf(JSONExtractInt(raw, 'message_json', 'status') BETWEEN 400 AND 499) / count(), 3) AS p_4xx,
                round(countIf(JSONExtractInt(raw, 'message_json', 'status') BETWEEN 500 AND 599) / count(), 3) AS p_5xx
            FROM {source}
            GROUP BY ua
            ORDER BY count DESC
            LIMIT 50
            FORMAT JSONEachRow
        """,
        # 2. Top client IPs.
        "top_client_ips": f"""
            SELECT
                JSONExtractString(raw, 'message_json', 'request', 'client_ip') AS ip,
                any(JSONExtractString(raw, 'message_json', 'request', 'headers', 'Cf-Ipcountry', 1)) AS country,
                count() AS count,
                uniqExact(JSONExtractString(raw, 'message_json', 'request', 'headers', 'User-Agent', 1)) AS distinct_uas,
                round(countIf(JSONExtractInt(raw, 'message_json', 'status') BETWEEN 400 AND 499) / count(), 3) AS p_4xx,
                topK(1)(JSONExtractString(raw, 'message_json', 'request', 'headers', 'User-Agent', 1))[1] AS top_ua_for_ip
            FROM {source}
            GROUP BY ip
            ORDER BY count DESC
            LIMIT 50
            FORMAT JSONEachRow
        """,
        # 3. Top /24 buckets.
        "top_slash24": f"""
            SELECT
                IPv4NumToString(toUInt32(intDiv(IPv4StringToNum(JSONExtractString(raw, 'message_json', 'request', 'client_ip')), 256) * 256)) AS slash24,
                count() AS count,
                uniqExact(JSONExtractString(raw, 'message_json', 'request', 'client_ip')) AS distinct_ips,
                uniqExact(JSONExtractString(raw, 'message_json', 'request', 'headers', 'User-Agent', 1)) AS distinct_uas
            FROM {source}
            WHERE match(JSONExtractString(raw, 'message_json', 'request', 'client_ip'), '^[0-9]+\\\\.[0-9]+\\\\.[0-9]+\\\\.[0-9]+$')
            GROUP BY slash24
            ORDER BY count DESC
            LIMIT 50
            FORMAT JSONEachRow
        """,
        # 4. Top /explain cmd shapes (first token + arg-count signature).
        "top_cmd_shapes": f"""
            SELECT
                shape,
                count() AS count,
                uniqExact(JSONExtractString(raw, 'message_json', 'request', 'headers', 'User-Agent', 1)) AS distinct_uas,
                uniqExact(JSONExtractString(raw, 'message_json', 'request', 'client_ip')) AS distinct_ips
            FROM (
                SELECT
                    raw,
                    decodeURLFormComponent(extractURLParameter(JSONExtractString(raw, 'message_json', 'request', 'uri'), 'cmd')) AS cmd_decoded,
                    concat(
                        splitByChar(' ', cmd_decoded)[1],