KQL Query Examples

These queries explore, analyze, and correlate ELLIO threat indicators in Microsoft Sentinel.

Browsing indicators

Count all ELLIO indicators

ThreatIntelIndicators
| where Data has "ELLIO"
| where ValidUntil > now()
| summarize ActiveCount = count()

Browse recent indicators

ThreatIntelIndicators
| where Data has "ELLIO"
| where TimeGenerated > ago(24h)
| project TimeGenerated, ObservableValue, Confidence, Tags, ValidUntil
| order by TimeGenerated desc
| take 50

Count by classification

ThreatIntelIndicators
| where Data has "ELLIO"
| extend Classification = extract('"classification":"([^"]+)"', 1, tostring(Data))
| summarize Count = count() by Classification
| order by Count desc

Extension data queries

The ELLIO enrichment extension provides context stored in the Data JSON column. Parse it with parse_json() to access nested fields.

Non-spoofable indicators only

ThreatIntelIndicators
| where Data has "ELLIO"
| extend ParsedData = parse_json(Data)
| extend Spoofable = tobool(
    ParsedData.extensions.["extension-definition--9fcc6545-c018-5dbe-90a8-86fc02af8b34"].spoofable
  )
| where Spoofable == false
| project ObservableValue, Confidence, Tags, ValidUntil
| take 100

Top source countries

ThreatIntelIndicators
| where Data has "ELLIO"
| extend Country = extract('"country_name":"([^"]+)"', 1, tostring(Data))
| where isnotempty(Country)
| summarize Count = count() by Country
| order by Count desc
| take 20

Top ASNs

ThreatIntelIndicators
| where Data has "ELLIO"
| extend ParsedData = parse_json(Data)
| extend ASN = ParsedData.extensions.["extension-definition--9fcc6545-c018-5dbe-90a8-86fc02af8b34"].src_asn
| where isnotempty(ASN)
| summarize Count = count() by
    ASN_Number = toint(ASN.number),
    ASN_Name = tostring(ASN.name)
| order by Count desc
| take 20

Indicators targeting specific ports

ThreatIntelIndicators
| where Data has "ELLIO"
| extend ParsedData = parse_json(Data)
| extend Ports = ParsedData.extensions.["extension-definition--9fcc6545-c018-5dbe-90a8-86fc02af8b34"].ports
| mv-expand Port = Ports
| where toint(Port) == 443
| project ObservableValue, Ports, Tags, Confidence
| take 100

Most persistent IPs (by active days)

ThreatIntelIndicators
| where Data has "ELLIO"
| extend ActiveDays = toint(extract('"active_days":([0-9]+)', 1, tostring(Data)))
| where ActiveDays > 30
| project ObservableValue, ActiveDays, Confidence, Tags
| order by ActiveDays desc
| take 50

Indicators with network fingerprints

ThreatIntelIndicators
| where Data has "ELLIO"
| where Data has "fingerprints"
| extend ParsedData = parse_json(Data)
| extend Fingerprints = ParsedData.extensions.["extension-definition--9fcc6545-c018-5dbe-90a8-86fc02af8b34"].fingerprints
| where isnotempty(Fingerprints)
| project ObservableValue, Fingerprints, Tags
| take 50

Indicators with SSH brute-force attempts

ThreatIntelIndicators
| where Data has "ELLIO"
| where Data has "ssh_credentials"
| extend ParsedData = parse_json(Data)
| extend SSHCreds = ParsedData.extensions.["extension-definition--9fcc6545-c018-5dbe-90a8-86fc02af8b34"].ssh_credentials
| project ObservableValue, SSHCreds, Tags
| take 50

CVE and MITRE ATT&CK

Indicators associated with CVEs

ThreatIntelIndicators
| where Data has "ELLIO"
| where Data has "cve"
| extend ParsedData = parse_json(Data)
| mv-expand Ref = ParsedData.external_references
| where Ref.source_name == "cve"
| summarize IPs = make_set(ObservableValue) by CVE = tostring(Ref.external_id)
| extend IP_Count = array_length(IPs)
| order by IP_Count desc

Kill chain phase distribution (Lockheed Martin)

ThreatIntelIndicators
| where Data has "ELLIO"
| where Data has "kill_chain_phases"
| extend ParsedData = parse_json(Data)
| mv-expand Phase = ParsedData.kill_chain_phases
| where Phase.kill_chain_name == "lockheed-martin-cyber-kill-chain"
| summarize Count = count() by Phase = tostring(Phase.phase_name)
| order by Count desc

MITRE ATT&CK technique distribution

ThreatIntelIndicators
| where Data has "ELLIO"
| extend ParsedData = parse_json(Data)
| mv-expand Ref = ParsedData.external_references
| where Ref.source_name == "mitre-attack"
| summarize Count = count() by Technique = tostring(Ref.external_id)
| order by Count desc

Correlating with your logs

Match against Azure AD sign-in logs

let EllioIPs = ThreatIntelIndicators
| where Data has "ELLIO"
| where ValidUntil > now()
| project ObservableValue;
SigninLogs
| where TimeGenerated > ago(7d)
| where IPAddress in (EllioIPs)
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType, Location, AppDisplayName
| order by TimeGenerated desc

Match against network connection logs

let EllioIPs = ThreatIntelIndicators
| where Data has "ELLIO"
| where ValidUntil > now()
| project ObservableValue;
CommonSecurityLog
| where TimeGenerated > ago(7d)
| where SourceIP in (EllioIPs) or DestinationIP in (EllioIPs)
| project TimeGenerated, SourceIP, DestinationIP, DeviceAction, Activity
| order by TimeGenerated desc

High-confidence matches only

let HighConfidenceIPs = ThreatIntelIndicators
| where Data has "ELLIO"
| where ValidUntil > now()
| where toint(Confidence) >= 80
| project ObservableValue;
SigninLogs
| where TimeGenerated > ago(7d)
| where IPAddress in (HighConfidenceIPs)
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType, Location

Monitoring and statistics

Daily ingestion rate

ThreatIntelIndicators
| where Data has "ELLIO"
| summarize Count = count() by bin(TimeGenerated, 1d)
| order by TimeGenerated desc

Tag distribution

ThreatIntelIndicators
| where Data has "ELLIO"
| extend ParsedData = parse_json(Data)
| mv-expand Tag = ParsedData.labels
| summarize Count = count() by Tag = tostring(Tag)
| order by Count desc
| take 30

Indicators expiring in the next 7 days

ThreatIntelIndicators
| where Data has "ELLIO"
| where ValidUntil between (now() .. now() + 7d)
| summarize ExpiringCount = count()

Destination countries targeted by attackers

ThreatIntelIndicators
| where Data has "ELLIO"
| where Data has "dst_geo"
| extend ParsedData = parse_json(Data)
| extend DstGeo = ParsedData.extensions.["extension-definition--9fcc6545-c018-5dbe-90a8-86fc02af8b34"].dst_geo
| mv-expand Dst = DstGeo
| summarize Count = count() by Country = tostring(Dst.country_name)
| order by Count desc