TI Urlhaus Feed Hit In Email Url Info
Query
//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;
URLHitsExplanation
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
EmailUrlInfodataset, identifying which potentially malicious URLs have appeared in emails. - It further joins this information with
EmailEventsbased on a common identifier (NetworkMessageId), which likely represents unique email messages.
- It matches the URLs from the UrlHaus database with URLs found in the
-
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.
Details
Benjamin Zulliger
Released: November 10, 2024
Tables
EmailUrlInfoEmailEvents
Keywords
UrlhausAbuseChEmailUrlInfoEventsNetworkMessageId
Operators
letexternaldatawhere!startswithextendreplace_stringprojectsplitmv-expandtostringparse_urlHostproject-awayjoinon==