Document toolboxDocument toolbox

Subqueries

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 ( ...)

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()
image-20240917-121143.png

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:

 

 

 

Â