Page Comparison

...

...

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

...

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.

...