AQL subquery

Use an AQL subquery as a data source that is referred to, or searched by the main query. Use the FROM or IN clause to refine your AQL query by referring to the data that is retrieved by the subquery.

A subquery is a nested or inner query that is referenced by the main query. The subquery is available in the following formats:

SELECT <field/s> FROM (<AQL query expression>)
This query uses the FROM clause to search the output (cursor) of the subquery.
SELECT <field/s> FROM events WHERE <field> IN (<AQL query expression>)
This query uses the IN clause to specify the subquery results that match values from the subquery search. This subquery returns only one column. You can specify the results limit but the maximum is 10,000 results.

Subquery examples

The nested SELECT statement in parenthesis is the subquery. The subquery is run first and it provides the data that is used by the main query. The main query SELECT statement retrieves the user names from the output (cursor) of the subquery

SELECT username FROM  
(SELECT * FROM events 
WHERE username IS NOT NULL 
LAST 60 MINUTES)

The following query returns records where the user name from the Ariel database matches values in the subquery.

SELECT * FROM events 
WHERE username IN 
(SELECT username FROM events 
LIMIT 10 LAST 5 MINUTES) LAST 24 HOURS

The following query returns records where the source IP address from the Ariel database matches the destination IP address in the subquery.

SELECT * FROM EVENTS 
WHERE sourceip IN 
(SELECT destinationip FROM events)

The following query returns records where the source IP address from the Ariel database matches the source IP addresses that are returned in the subquery. The subquery filters the data for the main select statement by locating internal hosts that interacted with high-risk entities. The query returns hosts that communicated with any hosts that interacted with high-risk entities.

SELECT sourceip AS 'Risky Hosts' FROM events 
WHERE destinationip IN (SELECT sourceip FROM events 
WHERE eventdirection = 'L2R'
AND REFERENCESETCONTAINS('CriticalWatchList', destinationip) 
GROUP BY sourceip) 
GROUP BY sourceip last 24 hours