...
| 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 Image Added |
| Expand |
|---|
| title | Subquery from a table different than the one in the main query |
|---|
|
| Code Block |
|---|
from demo.ecommerce.data
select mmcountry(clientIpAddress) as Country
where not Country in (
from siem.logtrust.web.activity
where now() - 12h < eventdate < now()
group every - by country)
where isnotnull(Country)
select bytesTransferred, timeTaken |
Image Removed Image Added |
| Expand |
|---|
| title | Subquery using two columns to filter |
|---|
|
| Code Block |
|---|
from siem.logtrust.web.activity
where (username, domain) in (
from siem.logtrust.web.navigation
where origin = "redada"
where now()-2h <eventdate<now()
group every - by userEmail, domain)
group every 5m by username, domain
select count() |
Image Removed Image Added |
| Expand |
|---|
| title | Subquery that includes more fields than the main query |
|---|
|
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 userid in (
from siem.logtrust.web.navigation
where not userid = ""
where now() -1h < eventdate <now()
group every - by userid, domain)
select * |
Image Removed Image Added |
| Expand |
|---|
| title | Subquery with the column name different than the one of the main query |
|---|
|
| Code Block |
|---|
from siem.logtrust.web.activity
where username in (
from siem.logtrust.web.navigation
where now()-1d <eventdate<now()
select userEmail)
select eventdate, username |
Image Removed Image Added |
| 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 |
|---|
| page | Subqueries |
|---|
| space | latest |
|---|
|
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 * |
|
| Expand |
|---|
| title | Subquery using a select clause to expose a field |
|---|
|
| Code Block |
|---|
from siem.logtrust.web.activity
select ((
from siem.logtrust.web.navigation
group every - by userEmail
select count()) as inner)
select inner[username] as nav
group by username, 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.
...
