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

Field Type Description

logsourceid

NUMBER

Internal ID of the log source. Use LOGSOURCENAME() to resolve to a human-readable name.

devicetype

NUMBER

Internal ID of the log source type. Use LOGSOURCETYPENAME() to resolve.

qid

NUMBER

QRadar event ID. Use QIDNAME() to resolve to the event name.

category

NUMBER

QRadar category ID. Use CATEGORYNAME() to resolve. HIGHLEVELCATEGORYNAME() is not available in QRadar 7.5.0 UP13.

severity

NUMBER (0-10)

How serious the event is. 7-10 is high, 4-6 medium, 0-3 low.

credibility

NUMBER (0-10)

How trustworthy the source is. Low credibility often indicates noisy or unreliable sources.

relevance

NUMBER (0-10)

How relevant the event is to the network environment.

magnitude

NUMBER (0-10)

Composite of severity, credibility, and relevance. Primary driver of offense generation.

devicetime

TIMESTAMP

Device-reported event time. Use for time filtering. STARTTIME was unreliable in QRadar 7.5.0 UP13.

eventcount

NUMBER

Number of bundled events represented by the record.

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.

Approach Syntax

No time filter (all available data)

Omit the WHERE clause. Returns all Ariel data up to the retention limit.

Specific epoch range

WHERE devicetime BETWEEN 1775529395000 AND 1775719679000

LAST N DAYS (not supported in 7.5.0 UP13)

Do not use. Causes parse error.

AQL Functions

Function Available in 7.5.0 UP13 Purpose

LOGSOURCENAME(logsourceid)

Yes

Resolve log source ID to name.

LOGSOURCETYPENAME(devicetype)

Yes

Resolve device type ID to vendor or technology name.

QIDNAME(qid)

Yes

Resolve QID to event name.

CATEGORYNAME(category)

Yes

Resolve category ID to category name.

HIGHLEVELCATEGORYNAME(category)

No

High-level category helper not available and causes parse error.

MIN() / MAX() / COUNT() / SUM()

Yes

Standard aggregate functions.

AQL Gotchas

Production-verified against QRadar 7.5.0 UP13 at CHLA.
Gotcha Detail

Default window is 5 minutes

AQL without a time clause only queries the last 5 minutes. Always specify a time range explicitly.

LAST N DAYS not supported

Causes parse error in QRadar 7.5.0 UP13. Use devicetime BETWEEN instead.

STARTTIME BETWEEN returns no results

Field was not reliably populated. Use devicetime instead.

devicetime epoch is non-standard

Values were in the ~1.775E12 range. Always run MIN/MAX devicetime before applying filters.

HIGHLEVELCATEGORYNAME invalid

Not available in QRadar 7.5.0 UP13. Use CATEGORYNAME only.

offenses is not an AQL table

Offenses are only accessible via REST API at /api/siem/offenses.

GROUP BY required for COUNT

Any non-aggregate column in SELECT must appear in GROUP BY.

TIMESTAMP(6) WITH TIME ZONE causes DPY-3022

When reading results via 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.

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

Query Purpose

SELECT MIN(devicetime) AS earliest, MAX(devicetime) AS latest FROM events

Confirm available data window. Always run first.

SELECT COUNT(*) AS TotalRawEvents, SUM(eventcount) AS TotalEventCount FROM events

Validate total event volume.

SELECT LOGSOURCENAME(logsourceid), LOGSOURCETYPENAME(devicetype), COUNT(*) AS EventCount FROM events GROUP BY logsourceid, devicetype ORDER BY EventCount DESC

Quick source inventory with type.

SELECT CATEGORYNAME(category), COUNT(*) AS EventCount FROM events GROUP BY category ORDER BY EventCount DESC

Category breakdown.

SELECT severity, COUNT(*) AS EventCount FROM events GROUP BY severity ORDER BY severity DESC

Severity distribution.

SELECT magnitude, COUNT(*) AS EventCount FROM events GROUP BY magnitude ORDER BY magnitude DESC

Magnitude distribution.

SELECT QIDNAME(qid), COUNT(*) AS EventCount FROM events GROUP BY qid ORDER BY EventCount DESC

Top event names.

SELECT sourceip, COUNT(*) AS EventCount FROM events GROUP BY sourceip ORDER BY EventCount DESC

Source IP hotspots.

SELECT view_name FROM all_views WHERE owner = 'DATACONNECT' ORDER BY view_name

List ISE DataConnect views.

SELECT column_name, data_type FROM all_tab_columns WHERE owner = 'DATACONNECT' AND table_name = 'VIEW_NAME' ORDER BY column_id

Inspect view schema.

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