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 doesn't contain a time criteria such as start, stop, or last runs only for 5 minutes.
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

Use the millisecond time format in the Time in the AQL query; for example, starttime-starttime%60000.
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'.
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".