Matching Url Redirectors From Urlclickevents Table With Openphish External Threat Intel Source

# Matching URL redirectors from UrlClickEvents table with OpenPhish 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 OpenPhish = externaldata(Domain: string)[@"https://raw.githubusercontent.com/openphish/public_feed/refs/heads/main/feed.txt"] with (format="txt", ignoreFirstRecord=True); UrlClickEvents | extend UrlChain = todynamic(UrlChain) | mv-expand UrlChain //| where Url != UrlChain // You can choose to remove the initial URL if you already use another analytic //| where ActionType == "ClickAllowed" // Click has been allowed by SafeLinks | extend ParsedUrl = parse_url(tostring(UrlChain)) | extend ParsedUrl_Domain = parse_json(ParsedUrl)["Host"] | extend DomainParts = split(ParsedUrl_Domain, ".") // Split the domain into parts by "." | extend CleanDomain = strcat_array(array_slice(DomainParts, array_length(DomainParts)-2, 2), ".") | where CleanDomain in~ (OpenPhish) ``` ### 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, OpenPhish. Here's a simplified breakdown of what the query does:

Data Source Setup: It starts by importing a list of suspicious domains from OpenPhish, an external threat intelligence source. This list is fetched from a public URL and is treated as a text file.
Event Analysis: The query examines the UrlClickEvents table, which contains records of URLs that users have clicked on in emails, Teams messages, and Office 365 apps.
URL Chain Expansion: It processes the UrlChain column, which contains the sequence of redirects a user experiences when clicking a URL. Each redirect in the chain is expanded for individual analysis.
Domain Extraction: For each URL in the redirect chain, the query extracts the domain name. It then simplifies the domain to its core components (e.g., "example.com" from "sub.example.com").
Threat Intelligence Matching: The simplified domain is checked against the list of suspicious domains from OpenPhish. If a match is found, it indicates that the URL redirector may be associated with a known phishing threat.

This query helps security analysts identify and investigate potentially harmful URL redirects by leveraging external threat intelligence data.

KQL Search

Matching Url Redirectors From Urlclickevents Table With Openphish External Threat Intel Source

Query

Explanation

Details

Actions