Forum Discussion
davinelm
Aug 08, 2022Copper Contributor
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...
- Aug 10, 2022Ok, 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
davinelm
Copper Contributor
Thank you for your reply! I tried out this query and it returned the same result from my first query minus one row. From messing around with it some more it is turning out to be a bit of a mind bender (at least for a kql noob as myself).
so just to clarify if i have columns Red, Blue, Yellow and Red and Blue and Yellow, with 1 count in each column, how to combine them in a new column called "Colors" with a count of all the 1s from each of the individual columns? As of now it works to massage the data in excel and open it in powerbi but eventually i'd like to automate this for reporting.
Thanks again for your reply.
MichaelJMelone
Aug 10, 2022Microsoft
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
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
- davinelmAug 10, 2022Copper Contributor
Yes! That did it. Thanks a lot!