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:timerange 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:timerange parameter:
    Table 1. Examples of AQL statements that contain time criteria, using specific time ranges and the SYSTEM:timerange parameter
    AQL examples that contain a specific time range
    SELECT *
    FROM events WHERE userName IS NULL 
     LAST 1 HOURS 
    SELECT *
    FROM events WHERE userName IS NULL 
     START '2024-04-25 15:51:20' 
    STOP '2024-04-25 17:00:20' 
    Tip: STOP represents the current time.
  • Any AQL statement that doesn't contain a time criteria such as START, STOP, or LAST returns data that represents the last 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 AQL GEO::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 AQL GEO::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".