Page Comparison

Table of Contents

maxLevel	2
type	flat

Overview

...

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 filter 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.

...

How does it work in LINQ?

You have to insert the full subquery enclosed in brackets () and preceded by the clause where <field(s)> in, functioning as the second argument of a filtering operation in the main query. You have to get rid of any unrelated field in the subquery by grouping your data or creating the required fields. The remaining fields must be only those whose data type will match the fields indicated in the main query.

Code Block

from <table1> 
[clauses]
 where <field(s)> in (
  from <table2> 
  [clauses] 
  where time expression < eventdate < time expression 
  group every - by / select <field(s) matching outer field(s)>)
select <field(s)>

Be careful with the allocation of the brackets and always ensure to finish the clause, especially when the subquery is very long and contains a variety of operations.

Guidelines

There are some considerations we need to take into account when working with subqueries:

The data types of the resulting fields in the subquery and the fields indicated in the main query must match. You can do this by grouping your data or creating the required fields. For example, if we want to filter a string field in the main query, we need to use a subquery that results in a string field. However, the names of the fields in the subquery and the main query can be different.
Code Block
from <table1> where <field> in ( from <table2> where now() - 5m < eventdate < now() group every - by <field>) select *
You can match the data of the main query and subquery through more than one field, as long as their data types match and are indicated in the proper order (the first one matches the first one, and so on).
Code Block
from <table1> where (<field1, field2>) in ( from <table2> where now() - 5m < eventdate < now() group every - by <field1, field2>) select *
The subquery may contain additional fields, as long as they are added after the ones that match the main query.
Code Block
from <table1> where <field1> in ( from <table2> where now() - 5m < eventdate < now() group every - by <field1, field2, field3>) select *
You must add the fields you want to see in the final query results using the operator select. Include select * to add all the fields in the table, or specify the required fields after the select operator.
The subquery may come from the same table as the main query, but also from a different one.

Time range selection

In case you want to explicitly specify a time range for your subquery data, you must include it using the following syntax:

where time expression < eventdate < time expression.

Following this format, you can specify either times relative to now or absolute dates.

This is an example with relative dates:

Code Block
from demo.ecommerce.data where statusCode in ( from demo.ecommerce.data where statusCode = "404" where now()- 5m < eventdate < now() group every - by statusCode) select method, statusCode, eventdate

And the following is an example with absolute dates. As you can see, it is necessary to use the Parse date (parsedate) operation to transform a given string representing a date into a timestamp. Check the operation article for more details and valid formats.

Code Block

from siem.logtrust.web.activity
where userid in(
from siem.logtrust.web.error
where not userid = ""
where parsedate("21/04/2020 21:00","DD/MM/YYYY HH:mm") < eventdate < parsedate("21/04/2020 21:40","DD/MM/YYYY HH:mm")
group every - by userid)
select *

It is possible to specify a time range for the subquery different than the one specified for the main query. Also, note that If you do NOT include a time range in your subquery, it will default to the same time range as the main query.

Note

Including a temporal grouping in your subquery

Note that if you include a temporal grouping in your subquery (for example, group every 30m by <field1>) you may not get any data. This is because a temporal grouping will return an eventdate prior to the time specified (in the example before, 30m). That will be compared to the main query time range and will likely return no results.

For this reason, it is recommended to add no-time based groupings (group every -) to your subqueries.

Query examples

Check below examples of subqueries that cover the different cases explained in the guidelines above. After each query, there is a capture that shows the result of adding them as source queries in an Activeboard widget. Go here to learn more about Activeboards and how to use them.

Expand

title	Subquery from the same table as the main query

Code Block
from demo.ecommerce.data where statusCode in ( from demo.ecommerce.data where statusCode = "404" where now()- 5m < eventdate < now() group every - by statusCode) select method, statusCode, eventdate

Image Removed

...

title	Subquery from a table different than the one in the main query

...

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:

Code Block
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.

Note

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:

Subquery using two columns to filter

Code Block

title

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.

activity

navigation
where

now() - 12h < eventdate < now()

'2024-09-10 07:21:35' <eventdate< '2024-09-12 12:21:35'
group every - by

country

domain)

where isnotnull(Country) select bytesTransferred, timeTaken

Image Removed

Expand

group every 10m by username, domain
select count()

...

And this is an example using tuples:

This example works because the first field in the subquery list (userid) matches the type of the field indicated in the main query

Code Block

from siem.logtrust.web.activity

where

select (

username

srcPort,

domain

serverPort) as tuple
where tuple in (
	from siem.logtrust.web.navigation

	where

origin

serverPort =

"redada"

	where

now

today()-

2h

5d

<eventdate<now()

< eventdate
	group

every -

by

userEmail

srcPort,

domain

serverPort)

group every

5m

1h by

username

srcPort,

domain

serverPort, tuple
select count()

Image Removed

Expand

title	Subquery that includes more fields than the main query

...

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.

Subquery with the column name different than the

Code Block

title

from siem.logtrust.web.activity

where userid in (

select has((from siem.logtrust.web.navigation select srcPort, userEmail
where

not

eventdate

userid =

> "

" where now() -1h < eventdate <now() group every - by userid, domain) select *

Image Removed

Expand

2024-09-01"), srcPort) as c1
where c1 = true

...

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

Code Block

from siem.logtrust.web.activity
where

username in

(
	from siem.logtrust.web.navigation

	where

now

today()-

1d

5d

<eventdate<now()

< eventdate
	select

userEmail

referer) -> url
select

eventdate

url,

username

Image Removed

Expand

title	Checking that the subquery results are contained in one of the main query fields

All the subquery examples above are used to filter the main query results in order to get only the events that match the subquery data. However, you can also check that your query results contain the resulting values of your subquery. In the example below, we uploaded the CSV below to our Devo domain, which contains several URI parts, in order to get those main query events that contain them in the uri field.

View file

name	uri_parts.csv

To do it, you must use the following syntax:

Code Block
from demo.ecommerce.data where set (( from my.upload.uri.parts select message)) in uri select *

Image Removed

urihost(url)

Expand

title	Subquery using a select clause to expose a field

Code Block

...

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>])

Code Block

from siem.logtrust.web.activity
group by username
select (from siem.logtrust.web.

activity

navigation

select

((

from

siem.logtrust.web.navigation

group

every - by userEmail select count()) as inner) select inner[username] as nav

where timestamp("2023-11-06 07:30:00") <= eventdate < timestamp("2023-11-06 13:30:00")
      group by

username, nav

Image Removed

Expose data from the subquery to the main query

You can correlate specific field values of your subquery with the ones in your main query and show them as a list in a new field.

To do it, you must encapsulate the subquery between another set of parentheses and add a name to it (inner in the example below). Then, you must reference the required subquery fields that we are going to use to perform the match between brackets [ ], preceded by the name we gave to the subquery.

...

userEmail, level
)[username] as userInSubq

...

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

Code Block

from siem.logtrust.web.activity
where userid in((

select 'siem.logtrust' as to_search
select '{user@devo.com}' as to_search2
select to_search2 in (from siem.logtrust.web.activity group username select username) as search_in_another_table
group by username, level
select (
    from siem.logtrust.web.navigation
where not userid = "" where now()-1h <eventdate<now() group every - by userid
select count()
select avg(srcPort)) as inner)
select userid, inner[userid]

...

Dealing with case sensitivity

Subqueries are case-sensitive, that is to say, the fields you use to match the main query and the subquery must have the same case.

In case you are working with strings and you're not sure if the fields you use for the match have the same case, you can use the Upper case (upper) or Lower case (lower) operations to transform them to the same type (uppercase or lowercase). In the example below, we transformed the uri field in both queries to lowercase using the Lower case (lower) operation:

Code Block

from demo.ecommerce.data
where lower(uri) in (
from demo.ecommerce.data
where now()- 5m < eventdate < now()
group every - by lower(uri))
select *by userEmail, level
        )[username] as userInSubq
select (
            from siem.logtrust.web.navigation
            group by userEmail, level
            select userEmail, count()
        )[username, level] as match

...

Versions Compared

Old Version 11

New Version Current

Key

Overview

What is a subquery?

How does it work in LINQ?

Guidelines

Time range selection

Query examples

How do subqueries work in the Data search area?

Non-contextual

Time-bounded in the past

Limitations of subqueries in Data search

Examples

Subquery with `in`

Subquery with `has`

Subquery with `at`

Expose data from the subquery to the main query

Dealing with case sensitivity

Page Comparison

Versions Compared

Old Version 11

New Version Current

Key

Overview

What is a subquery?

How does it work in LINQ?

Guidelines

Time range selection

Query examples

How do subqueries work in the Data search area?

Non-contextual

Time-bounded in the past

Limitations of subqueries in Data search

Examples

Subquery with in

Subquery with has

Subquery with at

Expose data from the subquery to the main query

Dealing with case sensitivity

Subquery with `in`

Subquery with `has`

Subquery with `at`