Table of Contents | ||||
---|---|---|---|---|
|
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
. Includeselect *
to add all the fields in the table, or specify the required fields after theselect
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, For this reason, it is recommended to add no-time based groupings ( |
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 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:
Code Block | |
---|---|
title | Subquery using two columns to filterfrom 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 wherenow() - 12h < eventdate < now() '2024-09-10 07:21:35' <eventdate< '2024-09-12 12:21:35' group every - bycountry domain)where isnotnull(Country) select bytesTransferred, timeTaken |
Expand | |
group every 10m by username, domain
select count() |
...
And this is an example using tuples:
Code Block | ||
---|---|---|
from siem.logtrust.web.activity
whereselect (username srcPort,domain serverPort) as tuple where tuple in ( from siem.logtrust.web.navigation whereorigin serverPort ="redada" 8080 wherenow today()-2h 5d<eventdate<now() < eventdate groupevery - by userEmailsrcPort,domain serverPort) group every 5m1h byusername srcPort,domain serverPort, tuple select count() | ||
Expand | ||
|
...
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.
Code Block | |
---|---|
title | Subquery with the column name different than thefrom siem.logtrust.web.activity
where userid in (
select has((from siem.logtrust.web.navigation select srcPort, userEmail wherenot eventdateuserid = > "" where now() -1h < eventdate <now() group every - by userid, domain) select * |
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 wherenow today()-1d 5d<eventdate<now() < eventdate selectuserEmail referer) -> url selecteventdate url,username |
Expand | ||||||
---|---|---|---|---|---|---|
| ||||||
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.
To do it, you must use the following syntax:
|
Expand | ||
---|---|---|
| ||
Code Block | urihost(url) |
...
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 navigationselect (( from siem.logtrust.web.navigation group every - by userEmail
select count()) as inner)
select inner[username] as navwhere timestamp("2023-11-06 07:30:00") <= eventdate < timestamp("2023-11-06 13:30:00") group byusername, nav |
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 |
...