Skip to main content

Using subsearches

How to use a subquery​

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

source wineventlog-*
| 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 wineventlog-*: use data from source wineventlog-*.
  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 SAF 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 wineventlog-*
| 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.
Attention!

For more information about append command, see here

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.

Example:

source wineventlog-*
  | join type=inner event.action event.code [source wineventlog-* | 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 events based on the action field event.action.keyword=logged-out. The results of this subquery will be used to merge with the original query.
Attention!

For more information about join command, see here