Tips for creating AQL queries for dashboard charts
Tips to help you create AQL queries and dashboard charts more easily cover aliases, time and date formats, and transformation lookup functions.
- Any AQL statement that contains a time criteria can use the predefined SYSTEM:time
range parameter. Then, when the widget is used in a dashboard, a time selector enables
the user to display a particular time range in time-based charts on the dashboard. The following
table compares examples of AQL queries that contain time criteria using specific time ranges versus
using the SYSTEM:time range parameter:
Table 1. Examples of AQL statements that contain time criteria, using specific time ranges and the SYSTEM:time range parameter AQL examples that contain a specific time range AQL examples that contain the SYSTEM:time range parameter SELECT * FROM events WHERE userName IS NULL LAST 1 HOURS
SELECT * FROM events WHERE userName IS NULL {SYSTEM:timerange}
SELECT * FROM events WHERE userName IS NULL START '2014-04-25 15:51:20' STOP '2014-04-25 17:00:20'
SELECT * FROM events WHERE userName IS NULL {SYSTEM:timerange}
- Any AQL statement that doesn't contain a time criteria such as START, STOP, or LAST runs only for 5 minutes.
- Use proper date and year formats in your queries, especially in a
GLOBALVIEW. Dates are not automatically appended; for example, use
StartTime, 'YYYY-MM-dd HH:mm'
. - Use the millisecond time format in the Time in the AQL query; for
example,
starttime-starttime%60000
. - Don't use a transformation lookup function in an IN operator in a WHERE clause. The IN operator
specifies multiple values in the WHERE clause, but can cause performance issues when you run the
query. For example, the following query can cause performance
issues:
Where logsourcetypename(deviceType) IN ('a','b')
- Always use aliases to ensure that your field names don't vary from one QRadar® version to another. For
example, in the following query, Active Offense Sum is an alias for the
SUM_Active Offense Count field.
select ("SUM_Active Offense Count" / 2) as 'Active Offense Sum', ("SUM_Dormant Offense Count" / 2) as 'Dormant Offense Sum', "Time" * 1000 as 'sTime' from GLOBALVIEW('Offenses Over Time','NORMAL') order by "Time" desc last 2 days
- Scatter geo charts display geographic locations to indicate the IP source and destination of
detected malicious activities. To collect this information, use the
geo_json
option of the AQLGEO::LOOKUP
function. For example:GEO::LOOKUP(sourceip, 'geo_json') AS 'geoSource', GEO::LOOKUP(destinationip, 'geo_json') AS 'geoDest',
- Choropleth charts are shaded in proportion to the amount of malicious activity detected in each
geographic region. To collect this information, use the
country
option of the AQLGEO::LOOKUP
function. For example:GEO::LOOKUP(sourceip, 'country') AS 'geoCountry',
Set the 'Location type' to 'Country/Region' to match what the MaxMind GeoIP database uses.
- Go to the IBM® QRadar Knowledge Center. In the Ariel Query Language (AQL) section of the Reference chapter, see the following topics: "Overview of Ariel Query Language", "AQL logical and comparison operators", "System performance query examples", "Events and flows query examples", "Reference data query examples", and "User and network monitoring query examples".