Skip to content

Query examples

Join examples

Join between events and communities tables (for Multi-tenant)

events
| where timestamp > ago(5m)
| limit 100
| lookup communities on sekoiaio.customer.community_uuid == uuid
| select timestamp, sekoiaio.customer.community_uuid, community.name
timestamp sekoiaio.customer.community_uuid community.name
2026-03-25T15:17:20.357Z 904df422-adf9-4be8-b2b6-d027c0d68184 Reynholm Industries
2026-03-25T15:17:57.942Z 904df422-adf9-4be8-b2b6-d027c0d68184 Reynholm Industries
2026-03-25T15:17:15.356Z 904df422-adf9-4be8-b2b6-d027c0d68184 Reynholm Industries

Join between events and entities tables

events
| where timestamp > ago(24h)
| limit 100
| aggregate count=count() by sekoiaio.entity.uuid
| lookup entities on sekoiaio.entity.uuid == uuid
| select entity.name, count
entity.name count
HQ - London Office 100
Cambridge Campus 20

Join between alerts and communities tables (for Multi-tenant)

alerts
| aggregate count=count() by community_uuid
| lookup communities on community_uuid == uuid
| select community.name, community_uuid, count
| limit 100
community.name community_uuid count
Reynholm Industries 904df422-adf9-4be8-b2b6-d027c0d68184 488
MIT - Massachusetts Institute of Technology c0540367-9702-46e2-90c3-7ab65ead9a27 464

Join between alerts and assets tables

alerts
| lookup assets on assets.uuid == uuid
| select short_id, rule_name, asset.name, asset.type
| limit 10
short_id rule_name asset.name asset.type
ALqUHCbZMbnY Mshta Suspicious Child Process desktop-4932-vm host
ALK5mN68LpbB Mshta Command From A Scheduled Task desktop-4932-vm host
ALWzDs9pcVVb CrowdStrike Falcon Intrusion Detection root account
ALQbzwMvo1Aj CrowdStrike Falcon Intrusion Detection Informational Severity root account
ALTwu5cU16LK PsExec Process root account
ALHGJU7tcoGb CrowdStrike Falcon Intrusion Detection root account
ALqPWeuUow4p CrowdStrike Falcon Intrusion Detection High Severity root account

Join between events and intake_formats tables

events
| where timestamp between (?time.start .. ?time.end)
| aggregate count() by sekoiaio.intake.dialect_uuid
| lookup intake_formats on sekoiaio.intake.dialect_uuid == uuid
| select intake_format.name, count
| limit 100
intake_format.name count
Sekoia.io Endpoint Agent 9630384
HarfangLab EDR 9548402
Zscaler Internet Access 19224678

Count events per intake format (event_telemetry)

event_telemetry
| where bucket_start_date between (?time.start .. ?time.end)
| aggregate sum(occurrences) by intake_dialect_uuid
| lookup intake_formats on intake_dialect_uuid == uuid
| select intake_format.name, sum_occurrences
| limit 100
intake_format.name sum_occurrences
HarfangLab EDR 739676
Sekoia.io Endpoint Agent 744873
Zscaler Internet Access 1475940

Data volume per intake format (event_telemetry)

event_telemetry
| where bucket_start_date between (?time.start .. ?time.end)
| aggregate sum(total_event_size) by intake_dialect_uuid
| lookup intake_formats on intake_dialect_uuid == uuid
| select intake_format.name, sum_total_event_size
| limit 100
intake_format.name sum_total_event_size
Zscaler Internet Access 1475940
HarfangLab EDR 739676
Sekoia.io Endpoint Agent 744873

Resolve intake format name from intakes

intakes
| lookup intake_formats on format_uuid == uuid
| select name, intake_format.name
name intake_format.name
Sekoia Agent Sekoia.io Endpoint Agent
Zscaler Zscaler Internet Access
Zscaler ZIA. Zscaler Internet Access

Alerts query examples

Detection rules ranked by number of alerts

alerts
| where created_at > ago(30d)
| aggregate total = sum(occurrences) by rule_name
| top 100 by total
rule_name total
SEKOIA Intelligence Feed 132
Suspicious Mshta Execution 82
HarfangLab EDR Critical Level Rule Detection 58

Assets ranked by number of alerts

alerts
| where created_at > ago(30d)
| aggregate count=count() by assets.uuid
| order by count desc
| limit 100
assets.uuid count
44b54aac-0d06-48aa-bda7-533d73d4e3c4 113
c8946c7e-f502-4736-8bf2-f6025b91583a 50
0d624ec2-8861-4956-9cf2-4fc6962a47ba 37

Threats ranked by number of alerts

alerts
| where created_at > ago(30d)
| aggregate count=count() by threats.name
| order by count desc
| limit 100
threats.name count
User Execution: Malicious File 52
Command and Scripting Interpreter: PowerShell 42
Create or Modify System Process: Windows Service 42

Alerts per detection type

alerts
| where created_at > ago(30d)
| aggregate count() by detection_type
detection_type count
CTI Retrohunt 5
Sigma Correlation 14
Sigma 238

Average time to detect in last 30 days

alerts
| where created_at > ago(30d)
| aggregate avg(time_to_detect)
avg_time_to_detect
1035.6561679790027

Rename columns and convert time_to_detect in minutes

alerts
| where time_to_detect != null
| select entity = entity_name, rule = rule_name, ttd = time_to_detect/60
| limit 10
entity rule ttd
HQ - London Office SEKOIA Intelligence Feed 0.13333333333333333
HQ - London Office OneNote Embedded File 0.13333333333333333
HQ - London Office SEKOIA Intelligence Feed 31.733333333333334

Ranking of communities by alerts

alerts
| aggregate AlertCount = count() by community_uuid
| lookup communities on community_uuid == uuid
| order by AlertCount desc
| select community.name, AlertCount
community.name AlertCount
Reynholm Industries 488
MIT - Massachusetts Institute of Technology 464

Assets query examples

Filter assets with a specific tag

assets
| where tags.tag in ["Admin"]
| limit 100
uuid community_uuid name tags
ddfeab36-f6b5-4f31-831e-5566b882dd00 904df422-adf9-4be8-b2b6-d027c0d68184 Maurice Moss [{"tag":"Admin"}]
b1ee47a2-dae2-4a6a-b4fa-ed8a56382f94 904df422-adf9-4be8-b2b6-d027c0d68184 Roy Trenneman [{"tag":"Admin"}]
b1ee47a2-dae2-4a6a-b4fa-ed8a56382f94 904df422-adf9-4be8-b2b6-d027c0d68184 User network [{"tag":"Admin"}]

Filter assets with multiple tags

assets
| where tags.tag in ["Admin", "Backup"]
| limit 100
uuid community_uuid name tags
ddfeab36-f6b5-4f31-831e-5566b882dd00 904df422-adf9-4be8-b2b6-d027c0d68184 Maurice Moss [{"tag":"Admin"}]
b1ee47a2-dae2-4a6a-b4fa-ed8a56382f94 904df422-adf9-4be8-b2b6-d027c0d68184 Roy Trenneman [{"tag":"Admin"}]
b1ee47a2-dae2-4a6a-b4fa-ed8a56382f94 904df422-adf9-4be8-b2b6-d027c0d68184 User network [{"tag":"Backup"}]

Search the events of assets with a specific tag

Asset tags are not stored in the events. This query reads them from the assets table when it runs, so it uses the current tags, including on the events received before a tag was added.

let tagged_assets = assets
| where tags.tag in ["Admin"]
| select uuid;

events
| where timestamp > ago(7d)
| where sekoiaio.any_asset.uuid in get_column_values("uuid", tagged_assets)
| select timestamp, sekoiaio.any_asset.name, event.action
| limit 100
timestamp sekoiaio.any_asset.name event.action
2026-03-25T15:17:20.357Z ["Maurice Moss","User network"] opened-file
2026-03-25T15:16:52.104Z ["Maurice Moss","User network"] deleted
2026-03-25T15:15:43.981Z ["Roy Trenneman"] opened-file

sekoiaio.any_asset.name lists every asset of the event, not only the tagged ones.

Note

The tag must be assigned to fewer than 10,000 assets. Beyond that, the query returns partial results with a warning.

Find hosts that no asset connector reports

Hosts seen by Sekoia Asset Discovery and by no configured asset connector, such as an EDR or a CMDB, are candidates for Shadow IT.

assets
| where type == "host"
  and source == "automatic" // Discovered, not created by a user
  and not connectors.connector_uuid // No connector type UUID: not seen by any configured Asset Connector
| limit 100

To keep only the hosts that Sekoia Asset Discovery did see:

assets
| where connectors.uuid == "e0437ab0-5e38-4bd3-b922-c500644889c3" // Seen by Sekoia Asset Discovery (built-in configuration UUID)
  and not connectors.connector_uuid // No connector type UUID: not seen by any configured Asset Connector
| limit 100

List all distinct tags

assets
| distinct tags.tag
| limit 100
tags.tag
Admin.
Backup
Berlin

Filter assets where tag starts with a prefix

assets
| where tags.tag startswith "Finance"
| limit 100
uuid community_uuid name tags
ddfeab36-f6b5-4f31-831e-5566b882dd00 904df422-adf9-4be8-b2b6-d027c0d68184 Maurice Moss [{"tag":"Finance"}]
b1ee47a2-dae2-4a6a-b4fa-ed8a56382f94 904df422-adf9-4be8-b2b6-d027c0d68184 Roy Trenneman [{"tag":"Finance"}]
b1ee47a2-dae2-4a6a-b4fa-ed8a56382f94 904df422-adf9-4be8-b2b6-d027c0d68184 User network [{"tag":"VIP"},{"tag":"Finance"}]

Filter assets where tag contains a string (case-insensitive)

assets
| where tags.tag contains~ "prod"
| limit 100
uuid community_uuid name tags
ddfeab36-f6b5-4f31-831e-5566b882dd00 904df422-adf9-4be8-b2b6-d027c0d68184 Maurice Moss [{"tag":"Tier1"},{"tag":"Production"},{"tag":"Berlin"}]
b1ee47a2-dae2-4a6a-b4fa-ed8a56382f94 904df422-adf9-4be8-b2b6-d027c0d68184 Roy Trenneman [{"tag":"Pre-Prod"}]
b1ee47a2-dae2-4a6a-b4fa-ed8a56382f94 904df422-adf9-4be8-b2b6-d027c0d68184 User network [{"tag":"Production"}]

Count assets per tag

assets
| aggregate count() by tags.tag
| order by count desc
| limit 100
tags.tag count
Local accounts 16
MIT - Massachusetts Institute of Technology 11

Exclude assets with a specific tag

assets
| where not tags.tag in ["Backup"]
| limit 100
uuid community_uuid name tags
ddfeab36-f6b5-4f31-831e-5566b882dd00 904df422-adf9-4be8-b2b6-d027c0d68184 Maurice Moss [{"tag":"Tier1"},{"tag":"Production"},{"tag":"Berlin"}]
b1ee47a2-dae2-4a6a-b4fa-ed8a56382f94 904df422-adf9-4be8-b2b6-d027c0d68184 Roy Trenneman [{"tag":"Pre-Prod"}]
b1ee47a2-dae2-4a6a-b4fa-ed8a56382f94 904df422-adf9-4be8-b2b6-d027c0d68184 User network []

Asset risk score query examples

Note

asset_risk_scores and asset_risk_score_history are already scoped to the communities available to the user. To filter results for a specific community, use a lookup on assets and filter on community_uuid. Filter the assets data source before the lookup to avoid the 10,000-row limit on the right side of a lookup.

Top 20 assets with a critical risk score in a specific community

asset_risk_scores
| lookup (assets | where community_uuid == "<community_uuid>") on asset_uuid == uuid
| top 20 by final_score

This query returns the 20 assets with the highest current risk score in the specified community.

Count assets with a critical risk score

asset_risk_scores
| where final_score >= 75
| aggregate count_distinct(asset_uuid)

This query counts the assets in the Critical risk band.

Top 20 assets by risk score

asset_risk_scores
| top 20 by final_score

This query returns the 20 assets with the highest current risk score.

Count assets whose score is mostly driven by recent activity

asset_risk_scores
| where recent_activity_score > exposure_score
| aggregate count_distinct(asset_uuid)

This query counts assets for which recent alerts, cases and points of interest contribute more than persistent exposure factors.

Count assets with a configured criticality and a score above 50

asset_risk_scores
| where criticality_value > 0 and final_score > 50
| aggregate count_distinct(asset_uuid)

This query counts assets with a configured criticality and a risk score above 50.

Count score-affecting events by type

asset_risk_score_history
| where event_date > ago(7d)
| aggregate event_count = count() by type
| order by event_count desc

This query counts the events that affected asset risk scores during the last seven days, grouped by event type.

Top 20 assets by alerts and cases

asset_risk_score_history
| where event_date > ago(30d) and type in ["alert", "case"]
| aggregate event_count = count_distinct(event_uuid) by asset_uuid
| top 20 by event_count

This query returns the 20 assets with the highest number of distinct alerts and cases contributing to their risk history during the last 30 days. The history can contain several entries for the same alert or case as its contribution decays over time, so count_distinct(event_uuid) counts source alerts and cases rather than history entries.

List criticality changes

asset_risk_score_history
| where type == "criticality" and event_date > ago(30d)
| order by event_date desc

This query lists criticality changes recorded during the last 30 days, starting with the most recent event.

Note

asset_risk_scores contains the current score only. Use asset_risk_score_history to analyze the events that affected the score or the risk trajectory.

Note

Rows older than 90 days are deleted daily from asset_risk_score_history. For each asset and event type, the latest hygiene and criticality entry older than 90 days is retained for baseline reconstruction.

Warning

final_score can be empty on newly created history rows until the next score recomputation.

Events query examples

Number of unique command lines per host.name

events
| where timestamp > ago(24h)
| aggregate count=count_distinct(process.command_line) by host.name
| order by count desc
| limit 100
host.name count
laptop-6a1ec62f 16
laptop-chris 525
laptop-b3205bc2 517

Number of unique hostname per month

events
| where timestamp > ago(90d)
| aggregate count=count_distinct(log.hostname) by month(timestamp)
timestamp_month count
2026-03 63

Top 10 visited URL

events
| where timestamp >= ago(24h)
| aggregate count() by url.domain
| top 10 by count
url.domain count
clients2.google.com 2088
www.deloitte.com 2040
www.squarespace.com 2010

Top 10 blocked URL

events
| where timestamp >= ago(24h) and event.action == 'blocked' and user.name != null and url.domain != null
| aggregate count() by url.domain
| top 10 by count
url.domain count
www.amazon.fr 1044
www.myfritz.net 1040
www.uol.com.br 1021

Top 10 login failures on Windows

events
| where timestamp > ago(24h) and event.code == 4625
| aggregate failed_login_count=count() by user.target.name
| top 10 by failed_login_count
user.target.name failed_login_count
barbara_liskov 204
ken_thompson 202
geoffrey_hinton 188

Sekoia.io endpoint agents per version

events
| where timestamp >= ago(24h)
| where sekoiaio.intake.dialect == 'sekoia.io endpoint agent'
| where event.action == 'stats'
| aggregate count_distinct(agent.id) by agent.version
agent.version count_distinct_agent.id
v1.9.0+62292d9c63d80859269661252cfc84db4ff66d81 304

List unique user.name

events
| where timestamp >= ago(24h)
| distinct(user.name)
| limit 100
user.name
SYSTEM
a_borg
ada_lovelace

Number of events per IP address

events
| where timestamp > ago(30d)
| aggregate count=count() by client.ip
| order by count desc
| limit 100
client.ip count
192.168.2.10 204
192.168.2.22 202
192.168.2.100 188

Aggregate events by source.ip and action.outcome

events
| where timestamp >= ago(24h) and event.category == 'authentication'
| aggregate count() by source.ip, action.outcome
| limit 100
source.ip action.outcome count
1.0.0.95 failure 33
1.0.0.95 success 142
1.5.178.82 failure 24
1.5.178.82 success 136

Events where process.name starts with 'chrome'

events
| where timestamp > ago(24h) and process.name startswith('chrome')
| limit 100
timestamp dialect smart description
2026-03-26T15:35:14.738Z Sekoia Text
2026-03-26T15:35:03.740Z Sekoia Text
2026-03-26T15:35:04.539Z Sekoia. Text

Events of specific intake

let intake_uuids = intakes | where name == '<intake name>' | distinct uuid;

events
| where timestamp >= ago(24h)
| where sekoiaio.intake.uuid in intake_uuids
| limit 100
timestamp dialect smart description
2026-03-26T15:35:14.738Z HarfangLab Text
2026-03-26T15:35:03.740Z HarfangLab Text
2026-03-26T15:35:04.539Z HarfangLab Text

Number of defended assets: unique host.name with more than 10 events during 2 weeks in the last 30 days

events
| where timestamp > ago(30d)
| aggregate events_count = count() by host.name, week = week(timestamp)
| where events_count > 10
| aggregate week_count = count(), total_count = sum(events_count) by host.name
| where week_count >= 2
| order by total_count
| project host.name, total_count
| limit 100
host.name total_count
desktop_awk 359626
desktop_neural 358417
laptop_coursera 358319

Note

Please note: the select command can also be used. select and project are aliases and both return the same results.

host.os.type per Sekoia endpoint agent

events
| where timestamp >= ago(24h)
| where sekoiaio.intake.dialect == 'sekoia.io endpoint agent'
| aggregate count() by host.os.type
| limit 100
host.os.type count
windows 590720

Received Kbytes per month per intake

event_telemetry
| where bucket_start_date >= ago(30d)
| aggregate sum_bytes = sum(total_message_size) by intake_uuid
| lookup intakes on intake_uuid == uuid
| select sum_gb = sum_bytes / (1000*1000*1000), intake.name
| order by sum_gb desc
sum_gb intake.name
36.160241918 Sekoia Agent
27.997688794 Zscaler
27.989695219 Zscaler ZIA

Asset applications query examples

Note

Removed applications stay in asset_applications with deleted_at set. Add isnull(deleted_at) to query installed applications only.

Count assets with unsigned applications

asset_applications
| where signed == false and isnull(deleted_at)
| aggregate count_distinct(asset_uuid)

This query counts the assets with at least one unsigned application installed.

Top 20 most widespread applications

asset_applications
| where isnull(deleted_at)
| aggregate asset_count = count_distinct(asset_uuid) by name, version
| top 20 by asset_count

This query returns the 20 applications and versions installed on the most assets.

Versions of an application across assets

asset_applications
| where name == "Google Chrome" and isnull(deleted_at)
| aggregate asset_count = count_distinct(asset_uuid) by version
| order by asset_count desc

This query shows how many assets run each version of an application, to spot outdated versions.

Top 20 publishers

asset_applications
| where isnull(deleted_at)
| aggregate asset_count = count_distinct(asset_uuid) by publisher
| top 20 by asset_count

This query returns the 20 publishers whose applications are installed on the most assets.

Applications recently seen for the first time

asset_applications
| where created_at > ago(7d) and isnull(deleted_at)
| aggregate asset_count = count_distinct(asset_uuid) by name, publisher
| top 20 by asset_count

This query returns the applications first recorded during the last seven days. created_at is the date Sekoia first saw the application, use install_date for the date reported by the asset.

Applications removed during the last 7 days

asset_applications
| where deleted_at > ago(7d)
| lookup assets on asset_uuid == uuid
| select asset.name, name, version, deleted_at
| order by deleted_at desc

This query lists the applications removed from assets during the last seven days, with the asset name.

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