Query Details
Security Alert Find Users Who Signinfrom Malicious I Ps
Query
//Parse the IP information from Security Alerts and find other users who have successfully signed on from the same IP addresses
//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)
//Data connector required for this query - Azure Active Directory - Signin Logs
SecurityAlert
| where ProviderName in ("MCAS", "IPC")
| extend x = todynamic(Entities)
| mv-expand x
| parse-where x with * '"Address":"' MaliciousIP '"' *
//Exclude any corporate or trusted IP addresses
| where MaliciousIP != "10.10.10.10"
| project AlertTime=TimeGenerated, MaliciousIP, CompromisedEntity
| join kind=inner
(
SigninLogs
| where ResultType in ("0","53003","50158")
)
on $left.MaliciousIP == $right.IPAddress
| where CompromisedEntity != UserPrincipalName
| distinct UserPrincipalName, AppDisplayName, IPAddress, UserAgent, ResultType, ResultDescriptionExplanation
This query is used to find other users who have successfully signed on from the same IP addresses as the ones identified in the Security Alerts. It uses the Security Alert and Azure Active Directory - Signin Logs data connectors.
First, it filters the SecurityAlert table to include only alerts from specific providers. Then, it expands the Entities column and parses the IP address information. It excludes any corporate or trusted IP addresses.
Next, it projects the AlertTime, MaliciousIP, and CompromisedEntity columns. It then joins this data with the SigninLogs table, filtering for specific result types. The join is based on matching the MaliciousIP from the SecurityAlert table with the IPAddress from the SigninLogs table.
Finally, it filters out any rows where the CompromisedEntity is the same as the UserPrincipalName and returns distinct values for UserPrincipalName, AppDisplayName, IPAddress, UserAgent, ResultType, and ResultDescription.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators