Query Details
Email Bad Reputation ASN Activities
Query
**Email Bad Reputation ASN activities** **Description**: This new query generates additional information using ASN/CIDR info from 'Firewall IP Lists @ Gyp the Cat dot Com' site (which takes data provided by other services and formats them) combined with a 'Bad ASN Rate/Reputation' source from the well-known source SpamHaus. As a result, it will show if some email was sent from a SenderIP address related to the mentioned ASN's and if the message is delivered into Inbox Folder: ``` let CIDRASN = (externaldata (CIDR:string, CIDRASN:int, CIDRASNName:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-asn.csv.zip'] with (ignoreFirstRecord=true)); let Malicious_ASN= (externaldata (asn:string)['https://www.spamhaus.org/drop/asndrop.json']with(format="multijson")); EmailEvents | evaluate ipv4_lookup(CIDRASN, SenderIPv4, CIDR, return_unmatched=true) | extend GeoIPData = geo_info_from_ip_address(SenderIPv4) | where isnotempty( CIDR) | extend asn_info = tostring(CIDRASN) | where DeliveryLocation has "Inbox" | join kind=inner (Malicious_ASN) on $left.asn_info == $right.asn | project Timestamp, SenderFromAddress,SenderMailFromAddress, SenderDisplayName, SenderMailFromDomain, SenderIPv4, RecipientEmailAddress, Subject, DeliveryAction,DeliveryLocation, ThreatTypes, CIDR, CIDRASNName, asn_info, asn ```
Explanation
This query is designed to identify emails that were sent from IP addresses associated with Autonomous System Numbers (ASNs) that have a bad reputation, and to check if these emails were delivered to the inbox folder. Here's a simplified breakdown of what the query does:
-
Fetch ASN/CIDR Information: It retrieves a list of CIDR (Classless Inter-Domain Routing) blocks and their associated ASNs from an external source ('Firewall IP Lists @ Gyp the Cat dot Com').
-
Fetch Malicious ASN List: It retrieves a list of ASNs known to have a bad reputation from SpamHaus.
-
Match Emails with ASN/CIDR: It looks up the sender's IP address in the email events against the CIDR list to find matching ASNs.
-
Geolocation Data: It adds geolocation information based on the sender's IP address.
-
Filter for Inbox Delivery: It filters the results to only include emails that were delivered to the inbox.
-
Join with Malicious ASN List: It joins the email data with the malicious ASN list to find emails sent from bad ASNs.
-
Select Relevant Information: It projects (selects) specific fields to display in the final output, such as the timestamp, sender and recipient information, subject, delivery action, threat types, and ASN details.
In summary, this query identifies emails sent from IP addresses associated with bad ASNs and checks if they were delivered to the inbox, providing detailed information about these emails.
Details
Sergio Albea
Released: August 22, 2024
Tables
Keywords
Operators