Forum Discussion
Quantity of bad emails vs legit email query
Hello, I'm trying to create a kql hunting query that will display the total number of bad emails (malware, spam, phishing) but am having some trouble combining the columns of various bad emails toget...
- Ok, I think I understand the ask now. When you get a chance try the below query. If you don't care about clean emails you can comment \ uncomment the lines noted to make it more performant. Let me know if this works for you.
let vip = pack_array("email address removed for privacy reasons");
EmailEvents
| where RecipientEmailAddress in~ (vip)
//| where isnotempty( ThreatTypes) // Uncomment this line if you don't care about clean email count
| summarize
Phish = countif(ThreatTypes has 'Phish')
, Spam = countif(ThreatTypes has 'Spam')
, Malware = countif(ThreatTypes has 'Malware')
, Clean = countif(isempty(ThreatTypes)) // You can comment this line if you don't care about clean email count
by RecipientEmailAddress
MichaelJMelone
Microsoft
MicrosoftOk, I think I understand the ask now. When you get a chance try the below query. If you don't care about clean emails you can comment \ uncomment the lines noted to make it more performant. Let me know if this works for you.
let vip = pack_array("email address removed for privacy reasons");
EmailEvents
| where RecipientEmailAddress in~ (vip)
//| where isnotempty( ThreatTypes) // Uncomment this line if you don't care about clean email count
| summarize
Phish = countif(ThreatTypes has 'Phish')
, Spam = countif(ThreatTypes has 'Spam')
, Malware = countif(ThreatTypes has 'Malware')
, Clean = countif(isempty(ThreatTypes)) // You can comment this line if you don't care about clean email count
by RecipientEmailAddress
let vip = pack_array("email address removed for privacy reasons");
EmailEvents
| where RecipientEmailAddress in~ (vip)
//| where isnotempty( ThreatTypes) // Uncomment this line if you don't care about clean email count
| summarize
Phish = countif(ThreatTypes has 'Phish')
, Spam = countif(ThreatTypes has 'Spam')
, Malware = countif(ThreatTypes has 'Malware')
, Clean = countif(isempty(ThreatTypes)) // You can comment this line if you don't care about clean email count
by RecipientEmailAddress
Yes! That did it. Thanks a lot!
