KQL QR Code Phishing

[sulaimanncs915]

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?

[#2]

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.

[#3]

TimeGenerated has error

[#4]

The name 'TimeGenerated' does not refer to any known column, table, variable or function

[#5]

@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?

[#6]

Hi I only have
Timestamp

[#7]

i need to add a line with regex to exclude files starting image001, image002,image003,image004.

[#8]

Sorry I assumed this was in Microsoft Sentinel, if you are in Defender XDR then yes, use TimeStamp instead

[#9]

You could use !startswith:

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