QRadar AQL Reference

SELECT COUNT(*) as total_events
FROM events
LAST 1 HOURS

-- AND: Both conditions must be true
WHERE sourceip = '10.50.1.1' AND destinationport = 22

-- OR: Either condition can be true
WHERE destinationport = 22 OR destinationport = 3389

-- NOT: Negate condition
WHERE NOT destinationport = 22

-- Grouping with parentheses
WHERE (sourceip = '10.50.1.1' OR sourceip = '10.50.1.2')
  AND destinationport = 443

-- Check if IP is in reference set
SELECT sourceip, destinationip, username
FROM events
WHERE sourceip IN (SELECT * FROM REFERENCE_SET('Suspicious_IPs'))
LAST 24 HOURS

-- Check if NOT in reference set (whitelist)
SELECT sourceip, username
FROM events
WHERE sourceip NOT IN (SELECT * FROM REFERENCE_SET('Approved_Servers'))
LAST 24 HOURS

-- Multiple reference sets
SELECT sourceip, destinationip
FROM events
WHERE sourceip IN (SELECT * FROM REFERENCE_SET('External_Attackers'))
  AND destinationip IN (SELECT * FROM REFERENCE_SET('Critical_Servers'))
LAST 7 DAYS

-- Query reference table (map)
SELECT sourceip, REFERENCE_TABLE('Asset_Info', 'owner', sourceip) as owner
FROM events
WHERE magnitude > 7
LAST 24 HOURS

-- Top failed logins by username
SELECT username, COUNT(*) as failed_count
FROM events
WHERE LOGSOURCETYPENAME(devicetype) ILIKE '%windows%'
  AND qidname(qid) ILIKE '%failed%'
  AND username IS NOT NULL
GROUP BY username
ORDER BY failed_count DESC
LIMIT 25
LAST 24 HOURS

-- Potential brute force: 10+ failures from same source in 1 hour
SELECT sourceip,
       username,
       COUNT(*) as attempts,
       MIN(starttime) as first_attempt,
       MAX(starttime) as last_attempt
FROM events
WHERE qidname(qid) ILIKE '%logon failure%'
  OR qidname(qid) ILIKE '%authentication failed%'
GROUP BY sourceip, username
HAVING COUNT(*) >= 10
ORDER BY attempts DESC
LAST 1 HOURS

-- Find successful logins that followed failures
SELECT username, sourceip,
       SUM(CASE WHEN qidname(qid) ILIKE '%failed%' THEN 1 ELSE 0 END) as failures,
       SUM(CASE WHEN qidname(qid) ILIKE '%success%' THEN 1 ELSE 0 END) as successes
FROM events
WHERE (qidname(qid) ILIKE '%logon%' OR qidname(qid) ILIKE '%authentication%')
  AND username IS NOT NULL
GROUP BY username, sourceip
HAVING failures >= 5 AND successes >= 1
LAST 24 HOURS

-- Logins outside business hours (before 6 AM or after 8 PM)
SELECT username, sourceip, destinationip,
       qidname(qid),
       starttime
FROM events
WHERE qidname(qid) ILIKE '%logon%success%'
  AND (EXTRACT(HOUR FROM starttime) < 6
       OR EXTRACT(HOUR FROM starttime) >= 20)
  AND username IS NOT NULL
ORDER BY starttime DESC
LAST 7 DAYS

-- Service accounts logging in interactively
SELECT username, sourceip, qidname(qid), payload
FROM events
WHERE username ILIKE 'svc_%'
  AND qidname(qid) ILIKE '%interactive%logon%'
LAST 24 HOURS

-- Top talkers by bytes transferred
SELECT sourceip,
       SUM(totalbytes) as total_bytes,
       COUNT(*) as flow_count
FROM flows
GROUP BY sourceip
ORDER BY total_bytes DESC
LIMIT 25
LAST 24 HOURS

-- Large outbound transfers to external IPs
SELECT sourceip AS internal,
       destinationip AS external,
       destinationport,
       APPLICATIONNAME(applicationid) as app,
       SUM(sourcebytes) as bytes_out
FROM flows
WHERE INCIDR('10.0.0.0/8', sourceip)
  AND NOT INCIDR('10.0.0.0/8', destinationip)
  AND NOT INCIDR('172.16.0.0/12', destinationip)
  AND NOT INCIDR('192.168.0.0/16', destinationip)
GROUP BY sourceip, destinationip, destinationport, applicationid
HAVING SUM(sourcebytes) > 100000000  -- 100 MB
ORDER BY bytes_out DESC
LAST 24 HOURS

-- Single source hitting many ports on single destination
SELECT sourceip, destinationip,
       UNIQUECOUNT(destinationport) as unique_ports,
       COUNT(*) as connection_attempts
FROM flows
GROUP BY sourceip, destinationip
HAVING UNIQUECOUNT(destinationport) > 50
ORDER BY unique_ports DESC
LAST 1 HOURS

-- High volume DNS queries (potential tunneling)
SELECT sourceip,
       COUNT(*) as dns_queries,
       SUM(totalbytes) as total_bytes
FROM flows
WHERE destinationport = 53
GROUP BY sourceip
HAVING COUNT(*) > 1000
ORDER BY dns_queries DESC
LAST 1 HOURS

-- Non-standard protocols (not TCP/UDP/ICMP)
SELECT sourceip, destinationip,
       PROTOCOLNAME(protocolid) as protocol,
       protocolid,
       COUNT(*) as occurrences
FROM flows
WHERE protocolid NOT IN (1, 6, 17)  -- Not ICMP, TCP, UDP
GROUP BY sourceip, destinationip, protocolid
ORDER BY occurrences DESC
LAST 24 HOURS

SELECT sourceip AS "Source",
       destinationip AS "Destination",
       COUNT(*) AS "Connections",
       SUM(totalbytes) AS "Total Bytes"
FROM flows
WHERE destinationport = 445
  AND sourceip <> destinationip
  AND INCIDR('10.0.0.0/8', sourceip)
  AND INCIDR('10.0.0.0/8', destinationip)
GROUP BY sourceip, destinationip
HAVING COUNT(*) > 10
ORDER BY "Connections" DESC
LAST 24 HOURS

SELECT sourceip AS "Source",
       destinationip AS "Destination",
       COUNT(*) AS "Sessions",
       MIN(starttime) AS "First Seen",
       MAX(starttime) AS "Last Seen"
FROM flows
WHERE destinationport = 3389
  AND INCIDR('10.0.0.0/8', sourceip)
GROUP BY sourceip, destinationip
ORDER BY "Sessions" DESC
LAST 24 HOURS

SELECT sourceip AS "Source",
       destinationip AS "Destination",
       destinationport AS "Port",
       COUNT(*) AS "Connections"
FROM flows
WHERE destinationport IN (5985, 5986)
GROUP BY sourceip, destinationip, destinationport
ORDER BY "Connections" DESC
LAST 24 HOURS

-- Large transfers to external IPs
SELECT sourceip AS "Internal",
       destinationip AS "External",
       destinationport AS "Port",
       APPLICATIONNAME(applicationid) AS "Application",
       SUM(sourcebytes) AS "Bytes Out",
       ROUND(SUM(sourcebytes) / 1048576, 2) AS "MB Out"
FROM flows
WHERE NOT INCIDR('10.0.0.0/8', destinationip)
  AND NOT INCIDR('172.16.0.0/12', destinationip)
  AND NOT INCIDR('192.168.0.0/16', destinationip)
GROUP BY sourceip, destinationip, destinationport, applicationid
HAVING SUM(sourcebytes) > 100000000  -- 100 MB
ORDER BY "Bytes Out" DESC
LAST 24 HOURS

-- Regular interval connections (potential beaconing)
SELECT sourceip, destinationip, destinationport,
       COUNT(*) as connection_count,
       AVG(sourcebytes) as avg_bytes
FROM flows
WHERE NOT INCIDR('10.0.0.0/8', destinationip)
GROUP BY sourceip, destinationip, destinationport
HAVING COUNT(*) > 100 AND AVG(sourcebytes) < 1000
ORDER BY connection_count DESC
LAST 24 HOURS

-- PowerShell/CMD activity
SELECT sourceip, username, payload
FROM events
WHERE payload ILIKE '%powershell%'
  OR payload ILIKE '%cmd.exe%'
  OR payload ILIKE '%wscript%'
  OR payload ILIKE '%cscript%'
  OR payload ILIKE '%mshta%'
  OR payload ILIKE '%regsvr32%'
ORDER BY starttime DESC
LIMIT 100
LAST 24 HOURS

-- LSASS access attempts
SELECT sourceip, username, payload
FROM events
WHERE payload ILIKE '%lsass%'
  OR payload ILIKE '%mimikatz%'
  OR payload ILIKE '%sekurlsa%'
  OR payload ILIKE '%wdigest%'
ORDER BY starttime DESC
LAST 24 HOURS

-- All admin/privileged account activity
SELECT username, sourceip,
       qidname(qid) as event_type,
       logsourcename(logsourceid) as source,
       COUNT(*) as count
FROM events
WHERE (username ILIKE 'admin%'
       OR username ILIKE '%_admin'
       OR username ILIKE 'root'
       OR username ILIKE 'administrator')
  AND username IS NOT NULL
GROUP BY username, sourceip, qid, logsourceid
ORDER BY count DESC
LAST 24 HOURS

-- Windows audit policy changes
SELECT username, sourceip, qidname(qid), payload
FROM events
WHERE qidname(qid) ILIKE '%policy%change%'
  OR qidname(qid) ILIKE '%audit%config%'
ORDER BY starttime DESC
LAST 7 DAYS

-- Account creation, deletion, modification
SELECT username, sourceip,
       qidname(qid) as event_type,
       payload
FROM events
WHERE qidname(qid) ILIKE '%user account%created%'
  OR qidname(qid) ILIKE '%user account%deleted%'
  OR qidname(qid) ILIKE '%user account%changed%'
  OR qidname(qid) ILIKE '%password%changed%'
  OR qidname(qid) ILIKE '%group%member%added%'
ORDER BY starttime DESC
LAST 7 DAYS

-- BAD: Full table scan
SELECT * FROM events WHERE payload ILIKE '%error%' LAST 30 DAYS

-- GOOD: Specific fields, reasonable time range
SELECT sourceip, username, payload
FROM events
WHERE payload ILIKE '%error%'
  AND magnitude > 5
LIMIT 1000
LAST 24 HOURS