Skip to content

Reference: Operators

The next sections describe the different operators supported by Sekoia Operating Language (SOL).

Count rows

Use the count operator to count the number of rows returned by the statement.

<table name>
| count

Count the number of rows in the events table

events
| where timestamp > ago(30m)
| count

Select columns

Use the select operator to define the columns to retrieve from the table. The order of the columns defined in the query will define the order of the columns in the results.

<table name>
| select <column name 1>, <column name 2>

Select the columns host.name and source.ip from the events table

events
| select host.name, source.ip
| limit 100

Distinct

Use the distinct operator to list all the unique values of a column.

<table name>
| distinct <column name>

List the unique values of client.ip from the events table

events
| where timestamp > ago(24h)
| distinct client.ip
| limit 100

Where

Use the where operator to filter rows by a list of conditions. Use parenthesis and keywords and, or to define complex conditions.

<table name>
| where <conditions>

Filter the query by excluding events older than 5 days and retrieving only user agent from Mac

events
| where timestamp > ago(5d) and user_agent.device.name == 'Mac'
| limit 100

Filter the query by excluding events older than 5 days and retrieving only user agent from Mac or Android

events
| where timestamp > ago(5d) and (user_agent.device.name == 'Mac' or user_agent.device.name == 'Android')
| limit 100

Same as previous but with multiple where statements

events
| where timestamp > ago(5d)
| where user_agent.device.name == 'Mac' or user_agent.device.name == 'Android'
| limit 100

Nested query

Use the in operator to use the results of a previous query.

let query = <table name> | select <column name>;

<table name>
| where <column name> in query

Example

let chromium_browsers =  events
| where timestamp > ago(30d)
| where process.command_line contains " --type=renderer " and process.command_line contains " --extension-process "
| distinct process.command_line;

events
| where process.command_line in chromium_browsers
| aggregate count_agents=count_distinct(agent.id), executables=make_set(process.executable) by process.name
| order by count_agents

Sort results

Use the order by operator to sort rows by a column. The default sort order is descending.

<table name>
| order by  <column name> <desc | asc>

Order the rows by the timestamp column in ascending order

events
| order by timestamp asc
| limit 100

Order alerts by descending urgency and ascending first_seen_at

alerts
| select short_id, rule_name, urgency, first_seen_at
| order by urgency desc, first_seen_at asc
| limit 100

Limit results

Use the limit operator to retrieve the last n number of rows based on the current sort order.

<table name>
| limit <integer>

Get 1000 events from events table

events
| limit 1000

Get the Top n rows

Use the top operator to returns the first n rows sorted by the specified column.

<table name>
| top <integer> by <column name> [asc | desc]

Get the top 5 alerts with the most occurrences from alerts table in the last 7 days

alerts
| where created_at > ago(7d)
| top 5 by occurrences

Note that the query below is equivalent.

alerts
| order by occurrences desc
| limit 5

Create calculated columns

You can use select or extend operators to create calculated columns. Use select to specify the columns to display. When using extend, the calculated column is appended to the end of the table.

<table name>
| select <new column name> = <column name 1> + <column name 2>

Create a calculated column named total that sums the time_to_detect, time_to_respond and time_to_resolve values

alerts
| select total = time_to_detect + time_to_respond + time_to_resolve
| limit 100

Aggregate rows

Use the aggregate operator to group rows by a column and perform aggregations with a chosen function: count, sum, min, max, avg, count_distinct, make_set, countif.

<table name>
| aggregate <function> by <column name>

Count the number of events per asset in the events table

events
| aggregate count() by sekoiaio.any_asset.name
| limit 100

Note that you can specify a column name for the aggregation. In the example below, the column name is defined as total.

events
| aggregate total = count() by sekoiaio.any_asset.name
| limit 100

Count the number of events per source.ip and per action.outcome in the events table

events
| where timestamp >= ago(24h) and event.category == 'authentication'
| aggregate count() by source.ip, action.outcome

Sum the values of 'time_to_detect' column in the alerts table

alerts
| aggregate sum(time_to_detect)
| limit 100

Retrieve the minimum value of 'time_to_detect' column in the alerts table

alerts
| aggregate min(time_to_detect)
| limit 100

Retrieve the maximum value of 'time_to_detect' column in the alerts table

alerts
| aggregate max(time_to_detect)
| limit 100

Calculate the average value of 'time_to_detect' column in the alerts table

alerts
| aggregate avg(time_to_detect)
| limit 100

Count unique values of 'source.ip' column in the events table

events
| aggregate count_distinct(source.ip)
| limit 100

Create an array of the set of distinct values of 'source.ip' column in the events table

Note that null values are ignored.

events
| aggregate make_set(source.ip)
| limit 100

Count allowed and denied network events per destination port using countif

events
| where timestamp >= ago(24h) and event.category == 'network'
| aggregate allowed = countif(action.outcome == 'success'), denied = countif(action.outcome == 'failure') by destination.port
| order by denied desc
| limit 100

Render results in chart

Use the render operator to display results in a chart to identify more easily anomalies or outliers. Supported charts are:

  • number
  • piechart
  • columnchart
  • barchart
  • linechart
<table name>
| aggregate <function> by <column name>
| render <chart_type> with (x=<column name>, y=<column name>, breakdown_by=<column name>, mode=<grouped | stacked>)

Count the number of events per asset in the events table and render it in a bar chart

events
| aggregate count() by sekoiaio.any_asset.name
| render barchart with (y=sekoiaio.any_asset.name)
| limit 100

Join tables

Use the join operator to combine data from multiple tables, enriching the data context, filtering more accurately data.

Available join types are:

  • inner join: Returns records that have matching values in both tables (default)
  • left join: Returns all records from the left table, and the matched records from the right table
<left table name>
| inner join <right table name> on <left column name> == <right column name>
| select <model object>.<right column name>, <left column name>

When performing join, the right table is injected into a model object.

This model object (similar to a class Object in code development) contains a set properties. Each property represents a column of the original table.

Info

By convention, we consider that when using join, the left part of the statement represents the column of the left table and the right part of the statement represents the column of the right table.

Join the tables events and intakes

events
| where timestamp > ago(24h)
| limit 100
| inner join intakes on sekoiaio.intake.uuid == uuid   // sekoiaio.intake.uuid belongs to events table and uuid belongs to intakes table
| distinct intake.name

The model object default name is related to the table name it is originating from. In this case, the model name is intake since the join was performed on the intakes table.

Join the tables alerts and entities

alerts
| where created_at > ago(24h)
| limit 100
| inner join entities on entity_uuid == uuid   // entity_uuid belongs to alerts table and uuid belongs to entities table
| distinct entity.name

The model object default name is related to the table name it is originating from. In this case, the model name is entity since the join was performed on the entities table.

Define model object name

In this example, we define a specific name for the model object with the into operator.

alerts
| where created_at > ago(24h)
| inner join entities on entity_uuid == uuid into my_entity
| select my_entity.name

Lookup

Use the lookup operator to extend a table. Extends the current table with values looked-up in another table. Prefer the lookup operator over join when the right table is small enough to fit into memory to improve query performance.

Info

The result doesn't repeat columns from the right table that are the basis for the join operation. The lookup operator only supports left join.

<left table name>
| lookup <right table name> on <left column name> == <right column name>
| aggregate <function> by <column name>
| order by <column name>

Similarly to join operator, lookup will inject the right table into a model object.

Compare

Use the following operators to compare values.

Comparator Description Examples
== Equals 1 == 1
=~ Equals (case insensitive) '.exe' == '.EXE'
!= Not equals 1 != 0
< Less 1 < 10
<= Less or equals 4 <= 5
> Greater 20 > 10
>= Greater or equals 5 >= 4

In

Use the in operator to filter the rows based on a set of case-sensitive strings. Use in~ to filter on a set of case-insensitive strings.

<table name>
| where <column name> in [<value 1>, <value 2>]

Find events where client.ip equals to theses values: 192.168.0.1, 192.168.0.2.

events
| where client.ip in ['192.168.0.1', '192.168.0.2']
| limit 100

Find events where process.name equals to theses values ignoring case-sensitivity: powershell.exe, powershell_ise.exe

events
| where process.name in~ ['powershell.exe', 'powershell_ise.exe']
| limit 100

Contains

Use the contains operator to filter the rows that contains a case-sensitive string. Use contains~ to switch to case-insensitive strings.

<table name>
| where <column name> contains <value 1>

Find events where user.full_name contains the string Admin (case sensitive)

events
| where user.full_name contains 'Admin'
| limit 100

Find events where user.full_name contains the string ADMIN (case insensitive)

events
| where user.full_name contains~ 'ADMIN'
| limit 100

Starts with

Use the startswith operator to filter rows that starts with a case-sensitive string. Use startswith~ to switch to case-insensitive strings.

<table name>
| where <column name> startswith <pattern>

Find events where url.domain starts with the string api.prod

events
| where url.domain startswith 'api.prod'
| limit 100

Find events where process.command_line starts with the string Invoke ignoring case-sensitivity

events
| where process.command_line startswith~ 'Invoke'
| limit 100

Ends with

Use the endswith operator to filter rows that ends with a case-sensitive string. Use endswith~ to switch to case-insensitive strings.

<table name>
| where <column name> endswith <pattern>

Find events where url.path ends with the string /admin

events
| where url.path endswith '/admin'
| limit 100

Find events where process.command_line ends with the string .DLl ignoring case-sensitivity

events
| where process.command_line endswith~ '.DLl'
| limit 100

Not

Use the not operator to negate any comparison.

<table name>
| where not <column name> <comparison operator> <pattern>

Find events where client.ip does not equal to theses values: 192.168.0.1, 192.168.0.2

events
| where not client.ip in ['192.168.0.1', '192.168.0.2']
| limit 100

Find events where user.full_name does not contain the string Admin (case sensitive)

events
| where not user.full_name contains 'Admin'
| limit 100

Find events where process.command_line does not start with the string Invoke ignoring case-sensitivity

events
| where not process.command_line startswith~ 'Invoke'
| limit 100

Find events where process.command_line does not end with the string .DLl ignoring case-sensitivity

events
| where not process.command_line endswith~ '.DLl'
| limit 100

Regex

Use the matches regex operator to filter the rows based on a regex pattern.

<table name>
| where <column name> matches regex <pattern>
Pattern Description Example
. Matches any character ab. matches 'aba', 'abb', 'abz'
? Repeat the preceding character zero or one times abc? matches 'ab' and 'abc'
+ Repeat the preceding character one or more times ab+ matches 'ab', 'abb', 'abbb'
* Repeat the preceding character zero or more times ab* matches 'a', 'ab', 'abb', 'abbb'
{} Minimum and maximum number of times the preceding character can repeat a{2} matches 'aa'
a{2,5} matches 'aa', 'aaa' and 'aaaa'
a{2,} matches 'a' repeated two or more times
| OR operator. The match will succeed if the longest pattern on either the left side OR the right side matches abc|xyz matches 'abc' and 'xyz'
(...) Forms a group. You can use a group to treat part of the expression as a single character abc(def)? matches 'abc' and 'abcdef' but not 'abcd'
[...] Match one of the character in the brackets
Inside the brackets, - indicates a range unless - is the first character or escaped
A ^ before a character in the brackets negates the character or range		 [abc] matches 'a', 'b', 'c'
[-abc] matches '-', 'a', 'b', 'c'
[^abc] matches any character except 'a', 'b', or 'c'

Info

Some characters are reserved as operators: . ? + * | { } [ ] ( ) " \ .
Escape reserved operators with a preceding backslash \ or surround them with double quotes "".
\@ renders as a literal '@'.
\\ renders as a literal '\'.
"john@smith.com" renders as 'john@smith.com'.

Find events where file.name contains '.sh'

let StartTime = ago(1h);
let EndTime = now();

events
| where timestamp between (StartTime .. EndTime)
| where file.name matches regex '.*\.sh'
| limit 100

Variables

Use the let operator to define variables.

let <variable name> = <string | integer>;

<table name>
| where <column name> == <variable name>

Count the number of events in the last 24 hours

let StartTime = ago(24h);
let EndTime = now();

events
| where event.created > StartTime and event.created <= EndTime
| count

Comments

Use // to add comments in the query.

Example

// Comment the filtering condition

<table name>
//| where <column name> = <variable name>
| limit 100

Getting Started & Overview

  • SOL Overview: Sekoia Operating Language overview.
  • SOL Getting Started: This tutorial walks you through writing your first SOL queries. By the end, you'll be able to search events, filter results, and save queries for reuse.
  • SOL Best Practices: Best practices to use SOL effectively.

User Guides

  • Create and Manage Queries: Create and manage queries using SOL.
  • SOL How-to Guides: Learn how to use the main functions of SOL to reach your goals (aggregate data, join tables, use external data, build a query library...).
  • SOL Query Examples: Get inspiration from our examples.
  • SOL Datasets: Discover the CSV import feature that enables SOC analysts to enrich security investigations by importing external data sources directly into the SOL query environment.

Technical Reference