---
title: "Subqueries"
canonical: "https://docs.devo.com/space/latest/95191366/Subqueries"
format: markdown
---
> Macro (toc)

## 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*.

![image](media://724b187c-2a6a-4899-8bcb-839462ba1df3)

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](https://docs.devo.com/space/latest/95126538/Creating+alert+definitions#Restriction-4:-subqueries).
- 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:

```sql
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](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95204913), [union tables](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95125981), or [aggregation tasks](https://devodocs.atlassian.net/wiki/spaces/latest/pages/94763942).
- Subqueries are not allowed in [OData requests](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95205258).
- If you use subqueries in your [alerts](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95126498) or [Activeboards](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95207188), 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:

```sql
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](media://0f341199-b526-4dc2-8105-00681f253de9)

And this is an example using tuples:

```sql
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()
```

![image-20240917-121510.png](media://9680707d-4ede-4f8b-b3af-afb1bb3cdd18)

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

```sql
from siem.logtrust.web.activity
select has((from siem.logtrust.web.navigation select srcPort, userEmail
where eventdate > "2024-09-01"), srcPort) as c1
where c1 = true
```

![image-20240917-122722.png](media://79dd375d-04b5-491e-98fc-a204b2a90007)

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

```sql
from siem.logtrust.web.activity
where (
	from siem.logtrust.web.navigation
	where today()-5d < eventdate
	select referer) -> url
select url, urihost(url)
```

![image-20240917-123909.png](media://a8924bf9-78fb-4cbc-bc68-c587648a4328)

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

```sql
from siem.logtrust.web.activity
group by username
select (from siem.logtrust.web.navigation
      where timestamp("2023-11-06 07:30:00") <= eventdate < timestamp("2023-11-06 13:30:00")
      group by userEmail, level
)[username] as userInSubq
```

![image-20240917-133109.png](media://3794e689-872c-4ed6-baf7-5c2ab1abafac)

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

```sql
from siem.logtrust.web.activity
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
        group by userEmail, level
        )[username] as userInSubq
select (
            from siem.logtrust.web.navigation
            group by userEmail, level
            select userEmail, count()
        )[username, level] as match
```

![image-20240917-124801.png](media://55949635-bc56-4894-b52f-1aa2f50e7a64)