Forum Discussion
sulaimanncs915
Sep 26, 2023Copper Contributor
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
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
- LeonPavesicSilver 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)- sulaimanncs915Copper ContributorHi how to create externaldomain indicator
- cyb3rmik3Iron 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