← All cheat sheets

QRADAR AQL

Plain-text reference · 4 KB. Read it, search it (Ctrl-F) or print it.

AQL (Ariel Query Language) queries the Ariel database in IBM QRadar: the
"events" table (log events) and the "flows" table (network flows). Syntax is
SQL-like. Run from the Log Activity "Advanced Search" box or the API.

STATEMENT SHAPE#

SELECT <fields> FROM <events|flows>
WHERE <conditions>
GROUP BY <fields>
ORDER BY <field> [ASC|DESC]
LIMIT <n>
LAST <n> <MINUTES|HOURS|DAYS>
  -- or --
START '<yyyy-MM-dd HH:mm:ss>' STOP '<yyyy-MM-dd HH:mm:ss>'

Order matters: SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... LIMIT
... LAST/START-STOP (time clause goes last).

COMMON NORMALIZED FIELDS (events)#

QIDNAME(qid)            human-readable event name
LOGSOURCENAME(logsourceid)  the log source
CATEGORYNAME(category)  event category name
sourceip                source IP
destinationip           destination IP
sourceport / destinationport
username                normalized user
eventcount              events rolled into this record
magnitude               QRadar magnitude score
starttime / endtime     epoch millis (use time functions to format)
payload                 raw event payload (UTF8 below to search it)

Note: QIDNAME(), LOGSOURCENAME(), CATEGORYNAME() are functions that resolve
IDs to names -- use them in SELECT to make output readable.

TIME RANGE#

... LAST 24 HOURS
... LAST 30 MINUTES
... START '2026-10-01 00:00:00' STOP '2026-10-02 00:00:00'
Always include a time clause; unbounded queries are slow/expensive.

FILTERING (WHERE)#

WHERE sourceip = '10.0.0.5'
WHERE destinationport = 3389
WHERE username = 'jdoe'
WHERE sourceip = '10.0.0.5' AND destinationport IN (22, 3389)
WHERE QIDNAME(qid) ILIKE '%failed%'      case-insensitive LIKE
WHERE username IS NOT NULL
WHERE INCIDR('10.0.0.0/8', sourceip)     IP in CIDR range
WHERE "AMQP Message" = 'x'               quote fields with spaces

SEARCHING THE RAW PAYLOAD#

WHERE UTF8(payload) ILIKE '%mimikatz%'
WHERE UTF8(payload) MATCHES '.*powershell.*-enc.*'
(UTF8(payload) decodes the raw bytes so you can text-search them.)

AGGREGATION / GROUP BY#

SELECT sourceip, COUNT(*) AS cnt
FROM events
WHERE QIDNAME(qid) ILIKE '%authentication failure%'
GROUP BY sourceip
ORDER BY cnt DESC
LIMIT 20
LAST 1 HOURS

Aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX(),
UNIQUECOUNT(field) (distinct count), FIRST(), LAST().

USEFUL FUNCTIONS#

DATEFORMAT(starttime,'yyyy-MM-dd HH:mm:ss')  format epoch to readable time
NOW()                                        current time (epoch ms)
STRLEN(field)                                string length
LOWER() / UPPER()
CONCAT(a, b)
INCIDR(cidr, ip)                             boolean, IP within CIDR
ASSETHOSTNAME(ip, NOW())                     resolve IP to asset hostname
NETWORKNAME(sourceip)                        QRadar network hierarchy name

EXAMPLE QUERIES (defensive)#

# Top sources of authentication failures (brute-force triage)
SELECT sourceip, username, COUNT(*) AS fails
FROM events
WHERE CATEGORYNAME(category) ILIKE '%Authentication%'
  AND QIDNAME(qid) ILIKE '%fail%'
GROUP BY sourceip, username
ORDER BY fails DESC LIMIT 25 LAST 6 HOURS

# RDP activity to internal hosts
SELECT DATEFORMAT(starttime,'HH:mm:ss') AS t, sourceip, destinationip, username
FROM events
WHERE destinationport = 3389
ORDER BY starttime DESC LIMIT 100 LAST 24 HOURS

# Events by log source (coverage / data-flow check)
SELECT LOGSOURCENAME(logsourceid) AS src, COUNT(*) AS cnt
FROM events GROUP BY src ORDER BY cnt DESC LAST 1 HOURS

# Payload hunt for encoded PowerShell
SELECT DATEFORMAT(starttime,'yyyy-MM-dd HH:mm:ss') AS t, sourceip, username
FROM events
WHERE UTF8(payload) ILIKE '%-enc%'
  AND UTF8(payload) ILIKE '%powershell%'
ORDER BY starttime DESC LIMIT 50 LAST 24 HOURS

# Outbound flows by bytes (exfil triage) -- note FROM flows
SELECT sourceip, destinationip, SUM(sourcebytes) AS out_bytes
FROM flows
WHERE NOT INCIDR('10.0.0.0/8', destinationip)
GROUP BY sourceip, destinationip
ORDER BY out_bytes DESC LIMIT 25 LAST 1 HOURS

TIPS#

- Prefer normalized fields over payload parsing when they exist (faster).
- UNIQUECOUNT() is good for "how many distinct users from this IP".
- Keep the LAST/START-STOP window tight; Ariel scans are time-bound.
- ILIKE is case-insensitive LIKE; MATCHES uses regex.

Defensive reference on CyberRamen. Offensive / red-team sheets live on OffensiveRamen.com.