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"}]

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

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

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