Microsoft Sentinel medium experimental kql
GreyNoise TI Map IP Entity to CommonSecurityLog
This query maps GreyNoise IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in CommonSecurityLog.
Detection Logic
let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
let dt_lookBack = 1h; // Look back 1 hour for CommonSecurityLog events
let ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators
// Fetch GreyNoise IPv4 indicators from the ThreatIntelIndicators (STIX 2.1) table.
// GreyNoise only publishes ipv4-addr patterns, so a single ObservableKey filter is sufficient.
let GreyNoise_IPs = ThreatIntelIndicators
| where TimeGenerated >= ago(ioc_lookBack)
| where SourceSystem == 'GreyNoise'
| where ObservableKey == 'ipv4-addr:value'
// Take the latest row per indicator first, then evaluate its current state, so an
// indicator that has since been deactivated cannot be resurrected by an older row.
| summarize arg_max(TimeGenerated, *) by Id
| where IsActive == true and IsDeleted == false and ValidUntil > now()
| extend TI_ipEntity = ObservableValue
| where ipv4_is_private(TI_ipEntity) == false and TI_ipEntity !startswith "fe80" and TI_ipEntity !startswith "::" and TI_ipEntity !startswith "127."
// Explicit projection keeps TimeGenerated and Type off the left side of the join, avoiding column collisions.
| project IndicatorId = Id, TI_ipEntity, ThreatType = tostring(Data.indicator_types[0]), IoCDescription = tostring(Data.description), Tags, Confidence, ValidFrom, ValidUntil;
// Perform a join between IP indicators and CommonSecurityLog events
GreyNoise_IPs
// Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation
| join kind=innerunique (
CommonSecurityLog
| where TimeGenerated >= ago(dt_lookBack)
| extend MessageIP = extract(IPRegex, 0, Message)
| extend CS_ipEntity = iff(isnotempty(SourceIP), SourceIP, DestinationIP)
| extend CS_ipEntity = iff(isempty(CS_ipEntity) and isnotempty(MessageIP), MessageIP, CS_ipEntity)
| extend CommonSecurityLog_TimeGenerated = TimeGenerated
)
on $left.TI_ipEntity == $right.CS_ipEntity
// Filter out logs that occurred after the expiration of the corresponding indicator
| where CommonSecurityLog_TimeGenerated < ValidUntil
// Group the results by IndicatorId and CS_ipEntity, and keep the log entry with the latest timestamp
| summarize CommonSecurityLog_TimeGenerated = arg_max(CommonSecurityLog_TimeGenerated, *) by IndicatorId, CS_ipEntity
// Select the desired output fields
| project timestamp = CommonSecurityLog_TimeGenerated, SourceIP, DestinationIP, MessageIP, Message, DeviceVendor, DeviceProduct, IndicatorId, ThreatType, IoCDescription, ValidFrom, ValidUntil, Confidence, Tags, TI_ipEntity, CS_ipEntity, LogSeverity, DeviceAction, Type Field Validations
Loading…
Comments (0)
Loading comments...