Forum Discussion

sulaimanncs915's avatar
sulaimanncs915
Copper Contributor
Sep 26, 2023

365 advance hunting

in Splunk, we have lookup editor. what about 365 advance hunting ?

 

i need to use a csv file to filter out all emails send to external domains.

 The csv file will include the external domains 

  • cyb3rmik3's avatar
    cyb3rmik3
    Nov 17, 2023

    sulaimanncs915 hi,

     

    this should return all results where NetworkMessageId count is equal to 1.

     

    let domainList = externaldata(domain: string) [@"https://raw.githubusercontent.com/tsirolnik/spam-domains-list/master/spamdomains.txt"] with (format="txt");
    let excludedDomains = datatable(excludeddomain :string)  // Add as many domains you would like to exclude
     ["domains",
      "domain2",
      "domain3"];   
    let Timeframe = 2d; // Choose the best timeframe for your investigation
    let SuspiciousEmails = EmailEvents
        | where Timestamp > ago(Timeframe)
        | where EmailDirection == "Outbound" // Assuming you are looking into mails sent by your organization
        | extend EmailDomain = tostring(split(RecipientEmailAddress, '@')[1])
        | join kind=inner (domainList) on $left.EmailDomain == $right.domain
        | where not(EmailDomain in (['excludedDomains']))
        | project Timestamp, NetworkMessageId, SenderMailFromAddress, SenderFromAddress, SenderDisplayName, RecipientEmailAddress, EmailDomain, domain, Subject, LatestDeliveryAction;
    SuspiciousEmails
        | join (EmailEvents
        | summarize count() by NetworkMessageId
        | where count_ == 1
        | project NetworkMessageId
    )on NetworkMessageId
        | sort by Timestamp desc 

     

     

    I hope this helps.

     

    If I have answered your question, please mark your post as Solved

    If you like my response, please consider giving it a like

  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor

    Hi sulaimanncs915,

    Microsoft 365 Defender Advanced Hunting does not have a built-in lookup editor, but you can use the Custom indicators feature to achieve the same functionality.

    Once you have created a custom indicator, you can use it to filter out all emails sent to external domains by using the following hunting query:

    Isječak koda
     

     

    // Get all emails sent to external domains
    Email
    | where SenderAddress in (externalDomainIndicator)​

     

    Replace externalDomainIndicator with the name of the custom indicator you created.

    This query will return a list of all emails sent to the external domains specified in the custom indicator. You can then review the results and take appropriate action.

    Here is an example of a CSV file that you could use to create a custom indicator:

     
    domain.com
    example.com
     

    Once you have uploaded the CSV file and created the custom indicator, you can use the following hunting query to identify all emails sent to the external domains:

     

     

    // Get all emails sent to external domains
    Email
    | where SenderAddress in (externalDomainIndicator)

     

     

    This query will return a list of all emails sent to the domain.com and example.com domains.

    You can also use the externalDomainIndicator custom indicator in other hunting queries, such as queries to identify emails sent from specific external domains or emails that contain specific keywords.


    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic
    (LinkedIn)

      • cyb3rmik3's avatar
        cyb3rmik3
        Iron Contributor

        sulaimanncs915 hi,

         

        unfortunately, there is no straight way to add a CSV in Microsoft 365 Defender which you may work with. However, you may upload the CSV wherever you want (github, a public server etc) and use the externaldata operator to bring these domains to advanced hunting.

        let domains = externaldata(domains: string)[@"https://yourcompanydomain.com/domains.csv"] with (format="csv", ignoreFirstRecord=True);

        (ignoreFirstRecord works if you have a title in the first raw of your .csv)

         

        and then from the EmailEvents table to search for the domains in the .csv file.

        EmailEvents
        | where RecipientEmailAddress has_any (domains)

         

        I hope this helps.

         

        If I have answered your question, please mark your post as Solved

        If you like my response, please consider giving it a like

Resources