Detect Service Exhaustion Flood in IBM QRadar

// Branch 1: HTTP flood detection from web access logs
SELECT
    DATEFORMAT(starttime, 'yyyy-MM-dd HH:mm') AS time_bucket,
    sourceip AS source_ip,
    LONG(destinationip) AS destination_ip,
    'HTTP_Access_Log' AS detection_source,
    COUNT(*) AS total_requests,
    COUNT(DISTINCT "URL") AS unique_uris,
    SUM(CASE WHEN LONG("HTTP Response Code") >= 500 THEN 1 ELSE 0 END) AS status_5xx,
    SUM(CASE WHEN LONG("HTTP Response Code") = 429 THEN 1 ELSE 0 END) AS status_429,
    COUNT(DISTINCT "User Agent") AS distinct_user_agents,
    ROUND(COUNT(*) / 300.0, 2) AS requests_per_second,
    CASE
        WHEN COUNT(DISTINCT "URL") = 1 AND COUNT(*) > 500 THEN 'Single-Resource HTTP Flood'
        WHEN COUNT(DISTINCT "User Agent") = 1 AND COUNT(*) > 1000 THEN 'Simple HTTP Flood'
        WHEN SUM(CASE WHEN LONG("HTTP Response Code") >= 500 THEN 1 ELSE 0 END) > 100 THEN 'Service Degradation via HTTP Flood'
        ELSE 'HTTP Request Flood'
    END AS flood_type,
    CASE
        WHEN COUNT(*) > 2000 OR SUM(CASE WHEN LONG("HTTP Response Code") >= 500 THEN 1 ELSE 0 END) > 500 THEN 'High'
        WHEN COUNT(*) > 1000 OR SUM(CASE WHEN LONG("HTTP Response Code") >= 500 THEN 1 ELSE 0 END) > 200 THEN 'Medium'
        ELSE 'Low'
    END AS severity
FROM events
WHERE
    starttime > NOW() - 3600000
    AND LOGSOURCETYPEID IN (
        SELECT id FROM SYS_LOGSOURCETYPE
        WHERE name IN ('Apache HTTP Server', 'Microsoft IIS', 'nginx', 'AWS Application Load Balancer')
    )
GROUP BY
    DATEFORMAT(starttime, 'yyyy-MM-dd HH:mm'),
    sourceip,
    destinationip
HAVING
    COUNT(*) > 500 OR SUM(CASE WHEN LONG("HTTP Response Code") >= 500 THEN 1 ELSE 0 END) > 100
ORDER BY total_requests DESC

UNION ALL

// Branch 2: Network/firewall flood alerts — Palo Alto, Fortinet, Check Point
SELECT
    DATEFORMAT(starttime, 'yyyy-MM-dd HH:mm') AS time_bucket,
    sourceip AS source_ip,
    LONG(destinationip) AS destination_ip,
    LOGSOURCENAME(logsourceid) AS detection_source,
    COUNT(*) AS total_requests,
    COUNT(DISTINCT destinationport) AS unique_uris,
    COUNT(*) AS status_5xx,
    0 AS status_429,
    0 AS distinct_user_agents,
    0.0 AS requests_per_second,
    CONCAT('Network_Alert: ', QIDNAME(qid)) AS flood_type,
    'High' AS severity
FROM events
WHERE
    starttime > NOW() - 3600000
    AND (
        LOWER(QIDNAME(qid)) LIKE '%flood%'
        OR LOWER(QIDNAME(qid)) LIKE '%denial%'
        OR LOWER(QIDNAME(qid)) LIKE '%dos%'
        OR LOWER(QIDNAME(qid)) LIKE '%renegotiation%'
        OR LOWER(QIDNAME(qid)) LIKE '%ssl%flood%'
        OR LOWER(QIDNAME(qid)) LIKE '%connection%limit%'
        OR LOWER(QIDNAME(qid)) LIKE '%rate%limit%'
    )
    AND severity >= 7
    AND LOGSOURCETYPEID IN (
        SELECT id FROM SYS_LOGSOURCETYPE
        WHERE name IN ('Palo Alto PA Series', 'Fortinet FortiGate', 'Check Point FireWall-1', 'Cisco ASA')
    )
GROUP BY
    DATEFORMAT(starttime, 'yyyy-MM-dd HH:mm'),
    sourceip,
    destinationip,
    logsourceid,
    qid
HAVING COUNT(*) > 5
ORDER BY total_requests DESC