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
eventsWHERE
<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)
SELECT * FROM events
WHERE username IN
(SELECT username FROM events
LIMIT 10 LAST 5 MINUTES) LAST 24 HOURS
SELECT * FROM EVENTS
WHERE sourceip IN
(SELECT destinationip FROM events)
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