Forum Discussion

sulaimanncs915's avatar
sulaimanncs915
Copper Contributor
Nov 16, 2023

KQL QR Code Phishing

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?
  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    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.
    • sulaimanncs915's avatar
      sulaimanncs915
      Copper Contributor
      The name 'TimeGenerated' does not refer to any known column, table, variable or function
      • Clive_Watson's avatar
        Clive_Watson
        Bronze Contributor

        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?

         



Resources