Issue with Sentinel Template Analytic Rule: TI map IP entity to CommonSecurityLog

New Contributor

The template Analytic Rule named "TI map IP entity to CommonSecurityLog" only generates detections when the IOC matches SourceIP. Customers relying on the template rule are not getting detections when the NetworkIP field from ThreatIntelligenceIndicator matches the DestinationIP field from CommonSecurityLog.

 

Problematic KQL from the Analytic Rule:

| extend CS_ipEntity = iff(isnotempty(SourceIP), SourceIP, DestinationIP)

 

Issue: Given that every log in CommonSecurityLog should have both SoureIP and DestinationIP, "isnotempty(SourceIP)" will always be true, and the above KQL will only set CS_ipEntity as the SourceIP. DestinationIP is ignored.

 

Quickly developed solution (for Palo-Alto logs in CommonSecurityLog):

Note: This rule excludes blocked traffic

 

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
//Match IOC NetworkIPs on SourceIP
let TISourceMatch =
ThreatIntelligenceIndicator
| where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
| where Active == true
// Picking up only IOC's that contain the entities we want
| where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
// As there is potentially more than 1 indicator type for matching IP, taking NetworkIP first, then others if that is empty.
// Taking the first non-empty value based on potential IOC match availability
| extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)
// using innerunique to keep perf fast and result set low, we only need one match to indicate potential malicious activity that needs to be investigated
| join kind=innerunique (

CommonSecurityLog
| where TimeGenerated >= ago(dt_lookBack)
| extend CS_ipEntity = SourceIP
| extend CommonSecurityLog_TimeGenerated = TimeGenerated)
on $left.TI_ipEntity == $right.CS_ipEntity
;
//Match IOC NetworkIPs on DestinationIP
let TIDestinationMatch =
ThreatIntelligenceIndicator
| where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
| where Active == true
// Picking up only IOC's that contain the entities we want
| where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
// As there is potentially more than 1 indicator type for matching IP, taking NetworkIP first, then others if that is empty.
// Taking the first non-empty value based on potential IOC match availability
| extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)
// using innerunique to keep perf fast and result set low, we only need one match to indicate potential malicious activity that needs to be investigated
| join kind=innerunique (
CommonSecurityLog
| where TimeGenerated >= ago(dt_lookBack)
| extend CS_ipEntity = DestinationIP
| extend CommonSecurityLog_TimeGenerated = TimeGenerated
)
on $left.TI_ipEntity == $right.CS_ipEntity
;
TISourceMatch
| union TIDestinationMatch
| where CommonSecurityLog_TimeGenerated < ExpirationDateTime
| summarize CommonSecurityLog_TimeGenerated = arg_max(CommonSecurityLog_TimeGenerated, *) by IndicatorId, CS_ipEntity
| project CommonSecurityLog_TimeGenerated, TI_ipEntity, SourceIP, DestinationIP, DeviceAction, DeviceVendor, DeviceProduct, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, CS_ipEntity, LogSeverity, FlexString2Label, FlexString2
//Exclude traffic blocked by firewall
| where DeviceAction !in ('reset-both', 'block-url', 'deny', 'drop')
| extend timestamp = CommonSecurityLog_TimeGenerated, IPCustomEntity = CS_ipEntity

1 Reply
Quick solution maintaining general structure of the existing template rule:

let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
let dt_lookBack = 1h;
let ioc_lookBack = 14d;
ThreatIntelligenceIndicator
| where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
| where Active == true
// Picking up only IOC's that contain the entities we want
| where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
// As there is potentially more than 1 indicator type for matching IP, taking NetworkIP first, then others if that is empty.
// Taking the first non-empty value based on potential IOC match availability
| extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)
// using innerunique to keep perf fast and result set low, we only need one match to indicate potential malicious activity that needs to be investigated
| join kind=innerunique (
CommonSecurityLog
| where TimeGenerated >= ago(dt_lookBack)
| extend MessageIP = extract(IPRegex, 0, Message)
| extend CS_ipEntity = SourceIP
| extend CS_ipEntity = iff(isempty(CS_ipEntity) and isnotempty(MessageIP), MessageIP, CS_ipEntity)
| extend CommonSecurityLog_TimeGenerated = TimeGenerated
| union CommonSecurityLog
| where TimeGenerated >= ago(dt_lookBack)
| extend MessageIP = extract(IPRegex, 0, Message)
| extend CS_ipEntity = 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
| where CommonSecurityLog_TimeGenerated < ExpirationDateTime
| summarize CommonSecurityLog_TimeGenerated = arg_max(CommonSecurityLog_TimeGenerated, *) by IndicatorId, CS_ipEntity
| project CommonSecurityLog_TimeGenerated, SourceIP, DestinationIP, MessageIP, Message, DeviceVendor, DeviceProduct, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, TI_ipEntity, CS_ipEntity, LogSeverity, DeviceAction
| extend timestamp = CommonSecurityLog_TimeGenerated, IPCustomEntity = CS_ipEntity