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 17, 2023

also i need 1 sender to 1 recipient only
if there are more than 1 recipient , do not show the result

cyb3rmik3

Microsoft

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 17, 2023
how to group by sender to recipient ?
for example, if sender sends 10 emails to email address removed for privacy reasons, it should group it together instead of showing separately.