Query Details
Email Events From Email Providers
Query
//A list of all email provider domains (free, paid, blacklist etc). Some of these are probably not around anymore. I've combined a dozen lists from around the web. Current "major providers" should all be in here as of the date this is created. let EmailProviders = externaldata (EmailProvider: string) [@'https://gist.githubusercontent.com/ammarshah/f5c2624d767f91a7cbdc4e54db8dd0bf/raw/660fd949eba09c0b86574d9d3aa0f2137161fc7c/all_email_provider_domains.txt'] with (format=csv, ignoreFirstRecord=False); EmailEvents | where SenderFromDomain has_any (EmailProviders) or RecipientEmailAddress has_any(EmailProviders) //| join kind=leftouter EmailAttachmentInfo on NetworkMessageId //left outer as we may not have any attachments //| summarize by SenderFromDomain, RecipientEmailAddress
Explanation
This KQL query is designed to analyze email events by identifying whether the sender or recipient email addresses belong to a known list of email provider domains. Here's a simplified breakdown:
-
Email Providers List:
- The query starts by loading a list of email provider domains from an external source, which is a CSV file hosted online. This list includes various types of email providers, such as free, paid, and blacklisted ones. The list is comprehensive and might include some providers that are no longer active.
-
Filtering Email Events:
- The query then examines a dataset called
EmailEvents. It filters this dataset to find records where either the sender's domain (SenderFromDomain) or the recipient's email address (RecipientEmailAddress) matches any of the domains in the loaded list of email providers.
- The query then examines a dataset called
-
Commented Out Code:
- There is a commented-out section that suggests a potential extension of the query. It indicates that the query could be expanded to include information about email attachments by joining with another dataset called
EmailAttachmentInfo. However, this part is not active in the current query.
- There is a commented-out section that suggests a potential extension of the query. It indicates that the query could be expanded to include information about email attachments by joining with another dataset called
In summary, the query is used to identify email communications involving known email provider domains, which can be useful for analyzing email traffic patterns or detecting potentially suspicious activity.
Details
Jay Kerai
Released: December 6, 2024
Tables
EmailEvents
Keywords
EmailProvidersDomainsEmailEventsSenderRecipientEmailAddressNetworkMessageIdAttachments
Operators
letexternaldatawithformatwherehas_anyor