QRadar AQL

Authoritative AQL query patterns for extracting QRadar event inventory, severity distribution, migration inputs, and offense-oriented views.

Ariel Query Language (AQL) is QRadar’s structured query language for the Ariel event and flow database. AQL syntax is SQL-like but not SQL. It has QRadar-specific functions, clauses, and behavioral differences. Queries can be submitted via the Log Activity console UI or via the REST API. See QRadar API for API submission workflow.

Field Model

logsourceid — internal ID of the log source
-- NUMBER type. Use LOGSOURCENAME(logsourceid) to resolve to a human-readable name.
SELECT LOGSOURCENAME(logsourceid) AS LogSource FROM events LIMIT 5
devicetype — internal ID of the log source type
-- NUMBER type. Use LOGSOURCETYPENAME(devicetype) to resolve.
SELECT LOGSOURCETYPENAME(devicetype) AS LogSourceType FROM events LIMIT 5
qid — QRadar event ID
-- NUMBER type. Use QIDNAME(qid) to resolve to the event name.
SELECT QIDNAME(qid) AS EventName FROM events LIMIT 5
category — QRadar category ID
-- NUMBER type. Use CATEGORYNAME(category) to resolve.
-- HIGHLEVELCATEGORYNAME() is NOT available in QRadar 7.5.0 UP13.
SELECT CATEGORYNAME(category) AS Category FROM events LIMIT 5
severity — how serious the event is (0-10)
-- 7-10 is high, 4-6 medium, 0-3 low
SELECT severity, COUNT(*) AS EventCount FROM events GROUP BY severity ORDER BY severity DESC
credibility — how trustworthy the source is (0-10)
-- Low credibility often indicates noisy or unreliable sources
SELECT credibility, COUNT(*) AS cnt FROM events GROUP BY credibility ORDER BY credibility DESC
relevance — how relevant the event is to the network environment (0-10)
SELECT relevance, COUNT(*) AS cnt FROM events GROUP BY relevance ORDER BY relevance DESC
magnitude — composite of severity, credibility, and relevance (0-10)
-- Primary driver of offense generation
SELECT magnitude, COUNT(*) AS EventCount FROM events GROUP BY magnitude ORDER BY magnitude DESC
devicetime — device-reported event time, use for time filtering
-- TIMESTAMP type. STARTTIME was unreliable in QRadar 7.5.0 UP13.
SELECT MIN(devicetime) AS earliest, MAX(devicetime) AS latest FROM events
eventcount — number of bundled events represented by the record
SELECT SUM(eventcount) AS TotalEventCount FROM events

Time Filtering

AQL without a time clause queries only the last 5 minutes of Ariel data by default. Always be explicit about time range.

devicetime was the reliable time field in QRadar 7.5.0 UP13. STARTTIME was unreliable and queries using it returned no results. LAST N DAYS syntax was not supported in QRadar 7.5.0 UP13.

Epoch values in QRadar 7.5.0 UP13 were non-standard, roughly in the 1.775E12 range. Always run a MIN/MAX devicetime query first to confirm the actual data window before applying filters.

No time filter — returns all Ariel data up to the retention limit
-- Omit the WHERE clause to get all available data
SELECT COUNT(*) AS TotalRawEvents FROM events
Specific epoch range — devicetime BETWEEN for precise windows
SELECT LOGSOURCENAME(logsourceid) AS LogSource, COUNT(*) AS EventCount
FROM events
WHERE devicetime BETWEEN 1775529395000 AND 1775719679000
GROUP BY logsourceid
ORDER BY EventCount DESC
LAST N DAYS — NOT supported in QRadar 7.5.0 UP13
-- Do NOT use. Causes parse error in QRadar 7.5.0 UP13.
-- Use devicetime BETWEEN instead.

AQL Functions

LOGSOURCENAME(logsourceid) — resolve log source ID to name (available in 7.5.0 UP13)
SELECT LOGSOURCENAME(logsourceid) AS LogSource FROM events LIMIT 5
LOGSOURCETYPENAME(devicetype) — resolve device type ID to vendor or technology name (available in 7.5.0 UP13)
SELECT LOGSOURCETYPENAME(devicetype) AS LogSourceType FROM events LIMIT 5
QIDNAME(qid) — resolve QID to event name (available in 7.5.0 UP13)
SELECT QIDNAME(qid) AS EventName FROM events LIMIT 5
CATEGORYNAME(category) — resolve category ID to category name (available in 7.5.0 UP13)
SELECT CATEGORYNAME(category) AS Category FROM events LIMIT 5
HIGHLEVELCATEGORYNAME(category) — NOT available in 7.5.0 UP13, causes parse error
-- Do NOT use. Not available in QRadar 7.5.0 UP13.
-- Use CATEGORYNAME(category) instead.
MIN / MAX / COUNT / SUM — standard aggregate functions (available in 7.5.0 UP13)
SELECT MIN(devicetime) AS earliest, MAX(devicetime) AS latest, COUNT(*) AS total FROM events

AQL Gotchas

Production-verified against QRadar 7.5.0 UP13 at CHLA.
Default window is 5 minutes — always specify a time range explicitly
-- Without a time clause, AQL only queries the last 5 minutes
-- Always add: WHERE devicetime BETWEEN <start> AND <end>
LAST N DAYS not supported — causes parse error in QRadar 7.5.0 UP13
-- Wrong: WHERE LAST 7 DAYS
-- Right: WHERE devicetime BETWEEN 1775529395000 AND 1775719679000
STARTTIME BETWEEN returns no results — field was not reliably populated
-- Wrong: WHERE STARTTIME BETWEEN ...
-- Right: WHERE devicetime BETWEEN ...
devicetime epoch is non-standard — values in the ~1.775E12 range
-- Always run MIN/MAX devicetime before applying filters
SELECT MIN(devicetime) AS earliest, MAX(devicetime) AS latest FROM events
HIGHLEVELCATEGORYNAME invalid — not available in QRadar 7.5.0 UP13
-- Wrong: HIGHLEVELCATEGORYNAME(category)
-- Right: CATEGORYNAME(category)
offenses is not an AQL table — only accessible via REST API
# Use the REST API at /api/siem/offenses instead
curl -k -H "SEC: $QRADAR_TOKEN" -H "Accept: application/json" \
  "https://$QRADAR_HOST/api/siem/offenses"
GROUP BY required for COUNT — non-aggregate columns must appear in GROUP BY
-- Wrong: SELECT sourceip, COUNT(*) FROM events
-- Right: SELECT sourceip, COUNT(*) FROM events GROUP BY sourceip
TIMESTAMP(6) WITH TIME ZONE causes DPY-3022 — avoid in oracledb thin mode
-- Avoid selecting timezone timestamp columns directly
-- Use plain TIMESTAMP(6) or cast
Ariel retention approximately 3 days at CHLA
-- Approximately 3 days of data were available
-- Extend retention or pull from archive for longer windows

Data Shaping for Pandas

When exporting for pandas processing:

  • Avoid TIMESTAMP(6) WITH TIME ZONE columns. They cause DPY-3022 in oracledb thin mode.

  • Use COUNT(*) rather than eventcount for grouped aggregations.

  • Lookup functions such as LOGSOURCENAME, QIDNAME, and CATEGORYNAME resolve at query time.

  • Results via the REST API return as JSON. Use pd.json_normalize(data["events"]) to load them into a DataFrame.

  • Column names in results match the AS aliases defined in the query.

Full inventory export for pandas — all lookup functions resolved at query time
SELECT
  LOGSOURCENAME(logsourceid)    AS LogSource,
  LOGSOURCETYPENAME(devicetype) AS LogSourceType,
  QIDNAME(qid)                  AS EventName,
  CATEGORYNAME(category)        AS Category,
  severity,
  credibility,
  relevance,
  magnitude,
  COUNT(*)                      AS EventCount
FROM events
GROUP BY logsourceid, devicetype, qid, category,
         severity, credibility, relevance, magnitude
ORDER BY LogSource, EventCount DESC
Load JSON results into pandas DataFrame
import pandas as pd
import json

with open("/tmp/qradar_results.json") as f:
    data = json.load(f)

df = pd.json_normalize(data["events"])

Query Patterns

SELECT MIN(devicetime) AS earliest, MAX(devicetime) AS latest FROM events
SELECT COUNT(*) AS TotalRawEvents, SUM(eventcount) AS TotalEventCount FROM events
SELECT
  LOGSOURCENAME(logsourceid)    AS LogSource,
  LOGSOURCETYPENAME(devicetype) AS LogSourceType,
  QIDNAME(qid)                  AS EventName,
  CATEGORYNAME(category)        AS Category,
  severity,
  credibility,
  relevance,
  magnitude,
  COUNT(*)                      AS EventCount
FROM events
GROUP BY logsourceid, devicetype, qid, category,
         severity, credibility, relevance, magnitude
ORDER BY LogSource, EventCount DESC
SELECT
  LOGSOURCENAME(logsourceid) AS LogSource,
  COUNT(*)                   AS EventCount
FROM events
GROUP BY logsourceid
ORDER BY EventCount DESC
SELECT DISTINCT
  LOGSOURCENAME(logsourceid)    AS LogSource,
  LOGSOURCETYPENAME(devicetype) AS LogSourceType
FROM events
SELECT
  CATEGORYNAME(category) AS Category,
  COUNT(*)               AS EventCount
FROM events
GROUP BY category
ORDER BY EventCount DESC
SELECT
  severity,
  COUNT(*) AS EventCount
FROM events
GROUP BY severity
ORDER BY severity DESC
SELECT
  magnitude,
  COUNT(*) AS EventCount
FROM events
GROUP BY magnitude
ORDER BY magnitude DESC
SELECT
  LOGSOURCENAME(logsourceid) AS LogSource,
  QIDNAME(qid)               AS EventName,
  CATEGORYNAME(category)     AS Category,
  severity,
  magnitude,
  COUNT(*)                   AS EventCount
FROM events
WHERE magnitude >= 7
GROUP BY logsourceid, qid, category, severity, magnitude
ORDER BY magnitude DESC, EventCount DESC
SELECT
  QIDNAME(qid) AS EventName,
  COUNT(*)     AS EventCount
FROM events
GROUP BY qid
ORDER BY EventCount DESC
SELECT sourceip, COUNT(*) AS EventCount
FROM events
GROUP BY sourceip
ORDER BY EventCount DESC
SELECT
  LOGSOURCENAME(logsourceid) AS LogSource,
  COUNT(*)                   AS EventCount
FROM events
WHERE devicetime BETWEEN 1775529395000 AND 1775719679000
GROUP BY logsourceid
ORDER BY EventCount DESC
SELECT
  LOGSOURCENAME(logsourceid)    AS LogSource,
  LOGSOURCETYPENAME(devicetype) AS LogSourceType,
  QIDNAME(qid)                  AS EventName,
  CATEGORYNAME(category)        AS Category,
  severity,
  credibility,
  relevance,
  magnitude,
  COUNT(*)                      AS EventCount
FROM events
GROUP BY logsourceid, devicetype, qid, category,
         severity, credibility, relevance, magnitude
ORDER BY LogSource, EventCount DESC
SELECT severity, credibility, relevance, magnitude FROM events LIMIT 5
SELECT CATEGORYNAME(category) AS Category FROM events LIMIT 5
SELECT
  LOGSOURCENAME(logsourceid)    AS LogSource,
  LOGSOURCETYPENAME(devicetype) AS LogSourceType
FROM events LIMIT 5
SELECT view_name
FROM all_views
WHERE owner = 'DATACONNECT'
ORDER BY view_name
SELECT column_name, data_type
FROM all_tab_columns
WHERE owner = 'DATACONNECT'
  AND table_name = 'RADIUS_AUTHENTICATIONS_WEEK'
ORDER BY column_id

Quick Reference

Data Window Discovery

Confirm available data window — always run first before applying time filters
SELECT MIN(devicetime) AS earliest, MAX(devicetime) AS latest FROM events
Validate total event volume — raw events vs bundled event count
SELECT COUNT(*) AS TotalRawEvents, SUM(eventcount) AS TotalEventCount FROM events

Source Inventory

Quick source inventory with type — log source names and device types ranked by volume
SELECT
  LOGSOURCENAME(logsourceid)    AS LogSource,
  LOGSOURCETYPENAME(devicetype) AS LogSourceType,
  COUNT(*)                      AS EventCount
FROM events
GROUP BY logsourceid, devicetype
ORDER BY EventCount DESC

Event Analysis

Category breakdown — event categories ranked by volume
SELECT
  CATEGORYNAME(category) AS Category,
  COUNT(*)               AS EventCount
FROM events
GROUP BY category
ORDER BY EventCount DESC
Severity distribution — event severity levels ranked highest to lowest
SELECT severity, COUNT(*) AS EventCount
FROM events
GROUP BY severity
ORDER BY severity DESC
Magnitude distribution — composite score driving offense generation
SELECT magnitude, COUNT(*) AS EventCount
FROM events
GROUP BY magnitude
ORDER BY magnitude DESC
Top event names — most frequent QRadar event types
SELECT
  QIDNAME(qid) AS EventName,
  COUNT(*)      AS EventCount
FROM events
GROUP BY qid
ORDER BY EventCount DESC

Network Analysis

Source IP hotspots — most active source IPs by event volume
SELECT sourceip, COUNT(*) AS EventCount
FROM events
GROUP BY sourceip
ORDER BY EventCount DESC

ISE DataConnect

List ISE DataConnect views — discover available RADIUS and endpoint data views
SELECT view_name
FROM all_views
WHERE owner = 'DATACONNECT'
ORDER BY view_name
Inspect view schema — column names and data types for a specific DataConnect view
SELECT column_name, data_type
FROM all_tab_columns
WHERE owner = 'DATACONNECT'
  AND table_name = 'VIEW_NAME'
ORDER BY column_id

Version gotchas for QRadar 7.5.0 UP13:

  • LAST N DAYS not supported. Use devicetime BETWEEN.

  • STARTTIME unreliable. Use devicetime.

  • HIGHLEVELCATEGORYNAME invalid. Use CATEGORYNAME only.

  • No time clause means only the last 5 minutes.

  • offenses is not an AQL table. Use the REST API.

See Also