Forum Discussion

Copper Contributor

Sep 25, 2023

Solved

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

threat hunting

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

sulaimanncs915

Copper Contributor

Nov 15, 2023

Hi how to create externaldomain indicator

cyb3rmik3

Microsoft

Nov 16, 2023

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

sulaimanncs915
Copper Contributor
Nov 16, 2023
hi can txt file works

https://github.com/tsirolnik/spam-domains-list/blob/master/spamdomains.txt
- cyb3rmik3
  Microsoft
  Nov 16, 2023
  sulaimanncs915 hi,
  
  yes, you can use the following query to leverage the text file you mentioned:
  
  let domainList = externaldata(domain: string) [@"https://raw.githubusercontent.com/tsirolnik/spam-domains-list/master/spamdomains.txt"] with (format="txt"); let Timeframe = 1d; // Choose the best timeframe for your investigation 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 | project Timestamp, SenderMailFromAddress, SenderFromAddress, SenderDisplayName, RecipientEmailAddress, EmailDomain, domain, Subject, LatestDeliveryAction
  
  Beware of the false/positive though
  
  If I have answered your question, please mark your post as Solved
  If you like my response, please consider giving it a like
  - sulaimanncs915
    Copper Contributor
    Nov 16, 2023
    Hi
    
    Thank you.
    
    I want to only see one sender to one recipient. If there are more than one receipient, do not show results. Also i want to exclude some email domains such as 163.com and 126.com. Kindly help.