Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community

KQL QR Code Phishing

Copper Contributor
let trustedDomains = dynamic(["microsoft.com"]);
let imageFileTypes = dynamic(["png", "jpeg", "svg"]);
EmailEvents
| where EmailDirection == "Inbound"
| where AttachmentCount > 0
| where not(SenderFromDomain has_any (trustedDomains))
| join EmailAttachmentInfo on NetworkMessageId
| where FileType has_any (imageFileTypes)
| summarize max(RecipientEmailAddress) by Subject,FileName,SenderDisplayName,SenderFromAddress
 
 
how to group by unique sender and how many count, can someone help with the query?
8 Replies
Would arg_max work for you? e.g. Use this as your last line

| summarize count(), arg_max(TimeGenerated,Subject,FileName,SenderDisplayName, RecipientEmailAddress) by SenderFromAddress

If we swap to TimeGenerated, we can get a count of each time we see the SenderfromAddress and show with arg_max the last entry for the named columns.
TimeGenerated has error
The name 'TimeGenerated' does not refer to any known column, table, variable or function

@sulaimanncs915 

Hi, The Table EmailEvents has a TimeGenerated Column, if you are not seeing it that is strange.

 

This is the full code, I'm using (which is the same as you supplied apart from the last line):

 

let trustedDomains = dynamic(["microsoft.com"]);
let imageFileTypes = dynamic(["png", "jpeg", "svg"]);
EmailEvents
| where EmailDirection == "Inbound"
| where AttachmentCount > 0
| where not(SenderFromDomain has_any (trustedDomains))
| join EmailAttachmentInfo on NetworkMessageId
| where FileType has_any (imageFileTypes)
| summarize count(), arg_max(TimeGenerated,Subject,FileName,SenderDisplayName, RecipientEmailAddress) by SenderFromAddress


If you check the schema, do you see the Column?

Clive_Watson_0-1700173752344.png

 



Hi I only have
Timestamp
i need to add a line with regex to exclude files starting image001, image002,image003,image004.
Sorry I assumed this was in Microsoft Sentinel, if you are in Defender XDR then yes, use TimeStamp instead
You could use !startswith:

| join EmailAttachmentInfo on NetworkMessageId
| where FileType has_any (imageFileTypes)
| where FileName !startswith "image"