Page Comparison

...

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 column field 2 of the subquery to filter the data in the query. Matching the columns 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 column 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 columnsfields. The remaining fields must be only those whose data type will match the fields indicated in the main query.

...

• 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 columnsfields. For example, if we want to filter a string column field in the main query, we need to use a subquery that results in a string columnfield. However, the names of the columns fields in the subquery and the main query can be different.
  from <table1>
where <column> <field> in (
from <table2>
where now() - 5m < eventdate < now()
group every - by <column><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).
  from <table1>
where (<column1<field1, column2>field2>) in (
from <table2>
where now() - 5m < eventdate < now()
group every - by <column1<field1, column2>field2>)
select *

• The subquery may contain additional fields, as long as they are added after the ones that match the main query.
  from <table1>
where <column1> <field1> in (
from <table2>
where now() - 5m < eventdate < now()
group every - by <column1<field1, column2field2, column3>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 columns fields in the table, or specify the required columns fields after the select operator.

• The subquery may come from the same table as the main query, but also from a different one.

...

...

Expand
title Subquery that includes more fields than the main query
This example works because the first column field in the subquery list (userid) matches the type of the column 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 *

...

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 column 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 *

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 column field values of your subquery with the ones in your main query and show them as a list in a new columnfield.

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.

Check below a query example that references the field userid of the subquery (named inner), which is the field used to perform the match (in red). The final query will show the userid column field of the main query plus the inner column field, which will show the corresponding values of the columns fields in our subquery separated by commas (in this case, a count and the average of the values in the srcPort column field, marked in green in the example).

...

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.

...