Skip to main content
Sumo Logic

Lab 5 - Using Subqueries to filter

This lab teaches you how to use the Subquery operator to dynamically change a keyword with the results of a different query.
In this lab, we’ll be using the Subquery operator to dynamically change a keyword with the result of a different query.

 

Let's say we want to run: _sourceCategory=Labs/Apache/Access and "127.0.0.1" to identify all messages in Apache access for a given IP address.

However, we'd like to automatically change that IP address to one with the most "File does not exist..." errors in the Apache Error logs. We can find that IP address by running a second query (the child query). This way, we can compare the IP address with the most errors at any given point in time with its “regular” activity:

Subquery concept.jpg

1. First, let’s create the parent query, which will be just the scope of data without any keywords.

_sourceCategory=Labs/Apache/Access

2. Next, we’ll create a second query to return one IP address associated with the most “File does not exist: /usr/htdocs” errors from the Apache error logs source category. This will become the child query.

_sourceCategory=labs/apache/error and "File does not exist: /usr/htdocs"
| parse "[*] [*] [client *]" as Time,Error,IP // Parses the IP address into a field
| count by IP // Aggregates the results by IP address
| topk(1,_count) // Displays the IP with the most “File does not exist: /usr/htdocs” errors

3. Now we’ll use the subquery operator to combine the child and parent query. Again, for this example, the child query will be used to generate a keyword for the parent query. The syntax we’re going to use can be simplified to:

<Parent Query>
[subquery: <Child Query>
| compose IP keywords
]

4. Finally, we’ll combine our child and parent query with the subquery syntax:

_sourceCategory=Labs/Apache/Access
[subquery:_sourceCategory=labs/apache/error and "File does not exist: /usr/htdocs"
| parse "[*] [*] [client *]" as Time,Error,IP
| count by IP 
| topk(1,_count) | compose IP keywords
]

In addition to keywords, subquery can be used in where and if filter operation. Visit Subqueries on our documentation page for details.