Subqueries

[ 1 What is a subquery? ] [ 2 How do subqueries work in the Data search area? ] [ 3 Limitations of subqueries in Data search ] [ 4 Examples ]

What is a subquery?

Devo provides a functionality that suppresses the boundaries between searches and removes certain limitations of a single search: subqueries. It consists of using the result of a query (subquery) to act as a condition in another query, thus making it possible to use information from different sources in a single consultation and further restrict the data to be retrieved.

For example, let's assume we have 2 different queries (query and subquery in the picture below) and we want to use the values in field 2 of the subquery to filter the data in the query. Matching the fields 2 in both queries, whose data belongs to the same type, we can filter the data in our query and get only those events that contain the values in the field 2 of the subquery.

In LINQ, subqueries are enclosed in the main query between parentheses, as you can see in the examples below.

How do subqueries work in the Data search area?

When you are working in the Data search area of Devo, queries containing subqueries must be:

Non-contextual

The content in both parts of a query including a subquery must be syntactically and semantically correct by themselves. That is to say, if you extract the subquery from a query, it must work independently. There is not information transfer between the query and the subquery.

Time-bounded in the past

Subqueries must be always time-bounded and be defined in the past.

Limitations of subqueries in Data search

You can use subqueries in the search window, include them in your alerts, and use them to define lookups. However, using subqueries in the search window presents some limitations that will be improved in future product releases.

You cannot create alerts using queries that contain subqueries. The only exception to this rule is the each-type alert, which allows the use of subqueries with specific limitations. Learn more here.
Currently, only one nesting level is allowed when you use subqueries in the Data search area. You can use several subqueries in the same query, but all of them must belong to the same nesting level, as you can see in this example:

from siem.logtrust.web.activity
where username in (
...)
where domain in (
...)

Fields included in your main query cannot be used in a subquery, or vice versa.
You cannot use subqueries inside special tables such as custom tables, union tables, or aggregation tasks.
Subqueries are not allowed in OData requests.
If you use subqueries in your alerts or Activeboards, note you cannot use the Go to query option to open the query in the search window.

Event limit

Note that there’s a 32000 event limit when you work with subqueries. If your query results exceed that limit, you will get a warning message indicating it.

Examples

Here you can find three use case examples with subqueries in LINQ that illustrate result sets that you can easily perform in your data tables.

Subquery with `in`

This is the most common use case for subqueries. The in operator is used to determine if the values of a specific field in a table (in the example below, username and domain) match any of the values in the set of results of a subquery.

For example:

from siem.logtrust.web.activity
where username in (
from siem.logtrust.web.navigation
where '2024-09-10 07:21:35' <eventdate< '2024-09-12 12:21:35'
group every - by userEmail)
where domain in (
from siem.logtrust.web.navigation
where '2024-09-10 07:21:35' <eventdate< '2024-09-12 12:21:35'
group every - by domain)
group every 10m by username, domain
select count()

And this is an example using tuples:

from siem.logtrust.web.activity
select (srcPort, serverPort) as tuple
where tuple in (
	from siem.logtrust.web.navigation
	where serverPort = 8080
	where today()-5d < eventdate
	group by srcPort, serverPort)
group every 1h by srcPort, serverPort, tuple
select count()

Subquery with `has`

The has operator works similar to the in operator, but the arguments are in different order. Instead of querying A in B, this syntax is used to say B has A.

In this example, we’re checking if the subquery results are contained in one of the main query fields:

Subquery with `at`

The at operation uses the result of the subquery as a table, in which all the columns except the last one are the key of the row, and the last one is the result.

Here's a simple example to illustrate the use of at in a subquery (as you can see, the syntax of the operation is the field_name between brackets: [<field_name>])

In this example, we are checking if the subquery results are contained in one of the main query fields: