TI Urlhaus Feed Hit In Email Url Info

//UrlHaus Abuse.ch Hits in EmailUrlInfo let URLHausOnlineRAW = externaldata (UHFeed:string) ["https://urlhaus.abuse.ch/downloads/csv_online/"] with(format="txt") | where UHFeed !startswith "#" | extend UHRAW=replace_string(UHFeed, '"', '') | project splitted=split(UHRAW, ',') | mv-expand id=splitted[0], dateadded=splitted[1], UHUrl=splitted[2], UHurl_status=splitted[3], UHlast_onlin=splitted[4], UHthreat=splitted[5], UHtags=splitted[6], UHLink=splitted[7], UHReporter=splitted[8] | extend UHUrl = tostring(UHUrl) | extend UHUrlDomain = tostring(parse_url(UHUrl).Host) | project-away splitted; let URLHits = EmailUrlInfo | join kind=inner URLHausOnlineRAW on $left.Url == $right.UHUrl | join EmailEvents on NetworkMessageId; URLHits

Explanation

This query is designed to identify and analyze URLs from the UrlHaus Abuse.ch database that have been detected in email data. Here's a simplified breakdown of what the query does:

Fetch Data from UrlHaus: It retrieves a list of potentially malicious URLs from the UrlHaus Abuse.ch online database in CSV format.
Data Cleaning and Preparation:
- It removes any lines starting with "#" (likely comments or headers).
- It removes any quotation marks from the data.
- It splits each line of the CSV into separate fields (like ID, date added, URL, status, threat type, tags, etc.).
Extract Domain Information: It extracts the domain part of each URL for further analysis.
Join with Email Data:
- It matches the URLs from the UrlHaus database with URLs found in the EmailUrlInfo dataset, identifying which potentially malicious URLs have appeared in emails.
- It further joins this information with EmailEvents based on a common identifier (NetworkMessageId), which likely represents unique email messages.
Result: The final output (URLHits) contains information about URLs from the UrlHaus database that have been found in email data, providing insights into potential threats present in email communications.

KQL Search

TI Urlhaus Feed Hit In Email Url Info

Query

Explanation

Details

Actions