Skip to main content
Version: 4.3

Using subsearches

How to use a subquery​

The example uses a subquery that searches the winlog_auth data source for login and logout events and then displays the results in a table with the action type, username, and event timestamp.

source winlog_auth
source winlog_auth
| search
  [ source winlog_auth
  | search (event.action.keyword="logged-out" OR event.action.keyword="logged-in")
  | table event.action.keyword, user.name
  | format ]
| stats values(user.name) by event.action

  1. source winlog_auth: use data from source winlog_auth
  2. | search [source winlog_auth | search (event.action.keyword="logged-out" OR event.action.keyword="logged-in") | table event.action.keyword, user.name | format ]: This is a subquery that filters the data to only login and logout events and outputs only two fields: event.action.keyword and user.name. The subquery results are formatted using the command format, so that they can be used in an external request | stats values(user.name) by event.action: This part of the query uses the stats command to aggregate the data. The results are grouped by action (login or logout) and usernames are displayed for each action. This allows you to see what actions users have performed.

Adding events using append​

One way to use subqueries in Smart Monitor is to append events to the original result using the append command. However, subqueries can be used for more complex tasks. This is especially useful when you need to combine the results of two or more queries into a single result. A subquery with the append command is executed separately from the original query and its results are added to the result of the main query.

An example of how adding events using append works:

source winlog_auth
| search user.name="AllenLaura"
| append [source winlog_auth | search user.name="BakerBrian"]
  1. | search user.name="AllenLaura": filters the data so that only events in which user.name=AllenLaura remain
  2. | append [source winlog_auth | search user.name="BakerBrian"]: this is a subquery that filters events where user.name=BakerBrian. The results of this subquery are appended to the results of the original query using the append command
info

For more information about append command, see Smart Monitor Language

Enriching data with join​

The join command allows to combine the results of two queries based on common fields. The subquery used in the join command is intended to provide additional data that can be added to the original result.

source winlog_auth
 | join type=inner event.action event.code [source winlog_auth | search event.action.keyword="logged-out"]
  1. |join type=inner event.action event.code [source winlog_auth | search event.action.keyword="logged-out"]: This is a join operation that joins the results of two queries using common fields. We specify the join type inner, this means that only data that matches the join condition is selected. Therefore, we indicate the fields by which we will combine data: event.action and event.code
  2. [source winlog_auth | search event.action.keyword="logged-out"]: This is a subquery that filters log events based on the action action to keep only logged-out events. The results of this subquery will be used to join with the main query.
Attention!

For more information about join command, see Smart Monitor Language.