CDav117
0
I'm pretty new to KQL, and running into a problem trying to format my data in Azure Sentinel.
I have a query with these columns I'm interested in: Email and IP.
If I run something like summarize count() by Email, IP I get almost what I want, however in some cases, the email value will be the same, but could be coming from a different IP.
Is there a way to have the output formatted so it will show the email value, then under that, list all the IP count values associated with the email?
You can easily create a set (unique values) of IPs per Email
// Data sample generation. Not part of the solution.
let t = range i from 1 to 30 step 1 | extend Email = strcat("email_", tostring(toint(rand(3))), "@", dynamic(["gmail", "outlook", "hotmail"])[toint(rand(3))], ".com"), IP = strcat_delim(".", tostring(toint(rand(256))), tostring(toint(rand(256))), tostring(toint(rand(256))), tostring(toint(rand(256))));
// Solution starts here
t
| summarize make_set(IP) by Email
| set_IP | |
|---|---|
| email_0@outlook.com | ["22.0.72.237","32.17.234.224","84.232.201.220","181.161.231.252","121.190.204.101"] |
| email_1@gmail.com | ["187.58.44.239","95.117.156.141","16.245.100.138"] |
| email_2@outlook.com | ["154.46.54.212","178.139.208.204","204.197.11.160","160.96.246.141","173.141.14.145","100.35.29.216"] |
| email_0@gmail.com | ["230.16.241.147","173.164.214.236","95.194.124.236","186.101.39.234"] |
| email_1@hotmail.com | ["19.214.101.122","168.72.148.236"] |
| email_2@hotmail.com | ["136.190.117.24","113.147.42.218","224.220.103.201"] |
| email_0@hotmail.com | ["126.176.108.237","201.222.155.151"] |
| email_2@gmail.com | ["132.67.147.234","2.101.57.210"] |
| email_1@outlook.com | ["6.173.214.26","18.169.68.195","87.141.157.8"] |