-
Notifications
You must be signed in to change notification settings - Fork 1
/
TI map IP entity to AADServicePrincipalSignInLogs
24 lines (24 loc) · 1.87 KB
/
TI map IP entity to AADServicePrincipalSignInLogs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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 (
AADServicePrincipalSignInLogs // | where TimeGenerated >= ago(dt_lookBack)
| extend AADServicePrincipalSignIn_TimeGenerated = TimeGenerated
)
on $left.TI_ipEntity == $right.IPAddress
| where AADServicePrincipalSignIn_TimeGenerated < ExpirationDateTime
| summarize AADServicePrincipalSignIn_TimeGenerated = arg_max(AADServicePrincipalSignIn_TimeGenerated, *) by IndicatorId, IPAddress
| project AADServicePrincipalSignIn_TimeGenerated, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, TI_ipEntity, IPAddress, ServicePrincipalName,
NetworkIP, NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress
| extend timestamp = AADServicePrincipalSignIn_TimeGenerated, IPCustomEntity = IPAddress, AccountCustomEntity = ServicePrincipalName