Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 
 

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. A subquery is accessible only by using API and is not yet available for use in searches from the Log Activity or Network Activity tabs. 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

Related Documentation