KQL Search

let ioc_lookBack = 90d;
let lookback = 90d;
let IncTitle = dynamic(["(Preview) TI map IP entity to SigninLogs","TI map IP entity to SigninLogs"]);
SecurityIncident
| where TimeGenerated > ago(lookback)
| where Title has_any (IncTitle)
| summarize arg_max(TimeGenerated,*) by IncidentNumber
| mv-expand AlertIds
| extend AlertId = tostring(AlertIds)
| join  (SecurityAlert)
on $left. AlertId == $right. SystemAlertId
| mv-expand parse_json(Entities)
| extend EType = tostring((Entities.Type))
| where EType == 'ip'
| extend IPAddress = tostring(Entities.Address)
// Count the # of alerts per IP address
| summarize Alertcount = dcount(SystemAlertId) by IPAddress
| join kind=innerunique  (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)
) on $left. IPAddress ==  $right.TI_ipEntity
| project IPAddress, Alertcount, LatestIndicatorTime, SourceSystem, ConfidenceScore, Description, ThreatType, Tags
// find the successfull sign-ins
| join SigninLogs
on $left. IPAddress == $right. IPAddress
| summarize TotalSignIns = dcount(CorrelationId), Failed = dcountif(CorrelationId, ResultType != 0), Success = dcountif(CorrelationId,ResultType == 0) , TotalUsers = dcount(UserPrincipalName)
by IPAddress, Alertcount, Description, ThreatType, Tags, AutonomousSystemNumber, Location