Query Details
Matching Ip Redirectors From Urlclickevents Table With Urlhaus External Threat Intel Source
Query
# Matching IP redirectors from UrlClickEvents table with URLHaus external threat intel source
## Description
The following query leverages UrlClickEvents and more specifically the UrlChain column to unfold redirectors identified from user's clicks at Emails, Teams messages and Office 365 apps, and also matches these redirector URLs to OpenPhish theat intelligence source.
### Microsoft Defender XDR
```
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 UHExtractedIP = extract(@'(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})', 0, UHUrl)
| where isnotempty(UHExtractedIP);
UrlClickEvents
| where ActionType == "ClickAllowed" // Click has been allowed by SafeLinks
| extend UrlChain = todynamic(UrlChain)
| mv-expand UrlChain
| extend UrlString = tostring(UrlChain)
| extend ExtractedIP = extract(@'(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})', 0, UrlString)
| where isnotempty(ExtractedIP)
| join kind=inner URLHausOnlineRAW on $left.ExtractedIP == $right.UHExtractedIP
```
### Versioning
| Version | Date | Comments |
| ------------- |---------------| ---------------------------------------|
| 1.0 | 18/3/2025 | Initial publish |
Explanation
This query is designed to identify potentially malicious URL redirectors by analyzing user click events and comparing them against a known threat intelligence source, URLHaus. Here's a simplified breakdown of what the query does:
-
Fetch External Threat Data:
- The query retrieves data from URLHaus, a threat intelligence source that lists malicious URLs. This data is fetched in a raw format and processed to extract relevant fields such as the URL, threat type, and associated IP address.
-
Process URLHaus Data:
- The raw data from URLHaus is split into individual components, and the IP addresses are extracted from the URLs listed in the threat feed.
-
Analyze User Click Events:
- The query examines user click events recorded in the
UrlClickEventstable, specifically focusing on events where clicks were allowed by SafeLinks (a security feature).
- The query examines user click events recorded in the
-
Extract and Match IP Addresses:
- From the
UrlChaincolumn in theUrlClickEventstable, which records the sequence of URLs a user was redirected through, the query extracts IP addresses. - It then compares these extracted IP addresses with those from the URLHaus data to identify matches.
- From the
-
Identify Malicious Redirectors:
- If a match is found between the IP addresses from user click events and those in the URLHaus threat feed, it indicates that the user was redirected through a potentially malicious URL.
Overall, this query helps in identifying and analyzing potentially harmful URL redirectors that users encounter through emails, Teams messages, and Office 365 apps by leveraging external threat intelligence data.
Details
Michalis Michalos
Released: March 18, 2025
Tables
UrlClickEvents
Keywords
UrlClickEventsUrlChainEmailsTeamsOfficeAppsOpenPhishMicrosoftDefenderXDR
Operators
letexternaldatawhereextendreplace_stringprojectsplitmv-expandtostringextractisnotemptytodynamicjoin