KQL to extract IP addresses from SecurityAlerts

[rpargman]

I'm not sure if there is a simpler way to do this, but I wanted to get a list of all the IP addresses in both Entities and ExtendedProperties of SecurityAlerts. This is helpful to join on DeviceNetworkEvents or other tables that contain IP addresses to see if any IP from a SecurityAlert had other activity in your environment.

Here is the KQL query that I came up with and saved as a custom function. Suggestions for improvement are welcome!

SecurityAlert 
// First get lists of IP addresses from ExtendedProperties
| extend properties = parse_json(ExtendedProperties)
| extend IP_list = split(tostring(properties["IP Addresses"]), ",")
| project IP_list
| where isnotempty(IP_list) 
| summarize make_set(IP_list)
| mv-expand set_IP_list // get each IP on its own row
| where isnotempty(set_IP_list)
| project IP = tostring(set_IP_list)
// Now get every IP address from Entities that are type "ip"
| union (SecurityAlert 
| extend Entities = parse_json(Entities)
| project Entities
| mv-expand Entities
| extend EType = tostring(Entities.Type)
| where EType == "ip"
| extend IP = tostring(Entities.Address)
| project IP)
| order by IP

[#2 · Accepted solution]

Good stuff ! I modified the query a bit. I think it gets the same results. Also I use distinct just to grab unique IPs. I think that what you were trying to achieve with the summarize make set.

If you run a "| count" you can see the difference. 

SecurityAlert
// First get lists of unique IP addresses from the Extended Properties
| project IPs = tostring(parse_json(ExtendedProperties)["IP Addresses"])
| extend IPs = split(IPs,",") | mv-expand IPs
| where isnotempty(IPs) | distinct tostring(IPs) // get only unique IPs
| union (SecurityAlert // join to Entities IP pool
| mv-expand parse_json(Entities)
| project IPs = Entities["Address"]
| where isnotempty(IPs) | distinct tostring(IPs)) // get only unique IPs
| order by IPs
| count

[#3]

Thank you! That's a nice improvement!