Write Sub-Queries

Sub-queries allow the child query to provide a dynamic filter to its parent queries. Sub-queries are evaluated first, and the result is then used in the parent query.

• You can nest sub-queries inside one another as well as a particular query having multiple sub-queries at the same level.
• Sub-queries by default inherit the global time range, but you can override it using the time between T1 and T2 syntax, if required.
• Sub-queries are restricted to return only the first 2000 matches as input to its parent. Other results are truncated.
• They have a maximum timeout of 30 seconds to complete.
• All of the fields returned by a sub-query must match the fields in the parent query by name. Otherwise, it will result in an error.
• You can use sub-queries only inside a search command.
• You can use all the commands inside a sub-query except cluster, clustersplit, clustercompare, fieldsummary, delete, classify, highlight, and highlightrows.

Examples:

• Chart the traffic from the IP blacklist over time:

  [searchlookup table=ip_blacklist | distinct ip | rename ip as 'host address'] | timestats count

• List the most purchased products for the top users of an e-commerce site:

  'Log Source'='WLS Access Logs' status=200 action=purchase ['Log Source'='WLS Access Logs' status=200 action=purchase | stats count by 'Host (Client Address)' | top limit=1 'Host(Client Address)' | fields -*, 'Host (Client Address)'] | lookup table=products select 'product name' using productid | stats count, distinctcount(productId), unique('product name') by 'Host (Client Address)'

• Find Top 4 OS Process IDs with highest sum:

  [ *|stats sum('OS Process ID') as OSprocessidSum by 'OS Process ID' | top 4 OSprocessidSum | fields -OSprocessidSum ] | stats count by 'OS Process ID', 'Log Source', 'Host Name(Server)'

• Show all the logs from the target with the most fatal severity logs:

  * and [ Severity = fatal | stats count by Target | top limit = 1 Count | fields -Count]