KQL - Summarize With Nested Counts

Question

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?

Answer 1

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

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"]

Fiddle