Sample AQL Queries

Use Ariel Query Language (AQL) queries to retrieve data from the Ariel database based on specific criteria.

Use the following query syntax, and adhere to the clause order, when you build an AQL query:

[SELECT *, column_name, column_name]
[FROM table_name]
[WHERE search clauses]
[GROUP BY column_reference*]
[HAVING clause]
[ORDER BY column_reference*]
[LIMIT numeric_value]
[TIMEFRAME]

Note:

When you use a GROUP BY or ORDER BY clause to sort information, you can reference column_names from your existing SELECT statement only.

Note:

By default, if the TIMEFRAME value is not specified, the query runs against the last five minutes of Ariel data.

Remember to use single quotation marks to specify literal values or variables and use double quotation marks for column names that contain spaces or non-ASCII characters:

Single quotation marks

Use single quotation marks when you reference the beginning and end of a string, as shown in these examples:

username LIKE ’%User%'

sourceCIDR= ’192.0.2.0’

TEXT SEARCH = ’VPN Authenticated user'

QIDNAME(qid) AS 'Event Name'

Double quotation marks

Use double quotation marks when column names contain spaces or non-ASCII characters, as shown in these examples:

Custom property names with spaces, such as “Account Security ID”.

Values that have non-ASCII characters.

Simple AQL Queries

Table 1: Simple AQL Queries
Basic AQL Commands	Comments
`SELECT * FROM events LAST 10 MINUTES`	Returns all the fields from the events table that were sent in the last 10 minutes..
`SELECT sourceip,destinationip FROM events LAST 24 HOURS`	Returns the `sourceip` and `destinationip` from the events table that were sent in the last 24 hours.
`SELECT * FROM events START ’2017 01 01 9:00:00’ STOP ’2017 01 01 10:20:00’`	Returns all the fields from the events table during that time interval.
`SELECT * FROM events limit 5 LAST 24 HOURS`	Returns all the fields in the events table during the last 24 hours, with output limited to five results.
`SELECT * FROM events ORDER BY magnitude DESC LAST 24 HOURS`	Returns all the fields in the events table sent in the last 24 hours, sorting the output from highest to lowest magnitude.
`SELECT * FROM events WHERE magnitude >= 3 LAST 24 HOURS`	Returns all the fields in the events table that have a magnitude that is less than three from the last 24 hours.
`SELECT * FROM events WHERE sourceip = ’192.0.2.0’ AND destinationip = ’198.51.100.0’ START ’2017 01 01 9:00:00’ STOP ’2017 01 01 10:20:00’`	Returns all the fields in the events table that have the specified source IP and destination IP within the specified time period.
`SELECT * FROM events WHERE INCIDR(’192.0.2.0/24’, sourceip)`	Returns all the fields in the events table where the source IP address is within the specified CIDR IP range.
`SELECT * FROM events WHERE username LIKE ’%roul%’`	Returns all the fields in the events table where the user name contains the example string. The percentage symbols (%) indicate that the user name can match a string of zero or more characters.
`SELECT * FROM events WHERE username ILIKE ’%ROUL%’`	Returns all the fields in the events table where the user name contains the example string, and the results are case-insensitive. The percentage symbols (%) indicate that the user name can match a string of zero or more characters.
`SELECT sourceip,category,credibility FROM events WHERE (severity > 3 AND category = 5018)OR (severity < 3 AND credibility > 8)`	Returns the `sourceip`, `category`, and `credibility` fields from the events table with specific severity levels, a specific category, and a specific credibility level. The AND clause allows for multiple strings of types of results that you want to have.
`SELECT * FROM events WHERE TEXT SEARCH ’firewall’`	Returns all the fields from the events table that have the specified text in the output.
`SELECT * FROM events WHERE username ISNOT NULL`	Returns all the fields in the events table where the username value is not null.