KQL Search

Query Details

HomeAI ToolsDevice Query

Unfolding Redirectors Using Urlclickevents Table

Query

# Unfolding redirectors using UrlClickEvents table

## 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.

### Microsoft Defender XDR
```
UrlClickEvents
//| where ActionType == "ClickAllowed" // Uncomment if you need to filter by "ClickAllowed"
| extend UrlChain = todynamic(UrlChain)
| mv-expand UrlChain
| where Url != UrlChain
| extend UrlString = tostring(UrlChain)
| summarize Count = count() by NetworkMessageId
| where Count > 1
| join kind=inner (
    UrlClickEvents
    | extend UrlChain = todynamic(UrlChain)
    | mv-expand UrlChain
//  | where Url != UrlChain
    | extend UrlString = tostring(UrlChain)
) on NetworkMessageId
| sort by TimeGenerated asc
```

### Versioning
| Version       | Date          | Comments                               |
| ------------- |---------------| ---------------------------------------|
| 1.0           | 18/3/2025     | Initial publish                        |

Explanation

This query is designed to analyze and unfold redirector URLs from user clicks in various Microsoft applications like Emails, Teams messages, and Office 365 apps using the UrlClickEvents table. Here's a simplified breakdown of what the query does:

  1. Data Preparation:

    • It starts by accessing the UrlClickEvents table, which logs URL click events.
    • The UrlChain column, which contains a sequence of URLs (redirects), is converted into a dynamic data type for further processing.

  2. Expanding URL Chains:

    • The mv-expand function is used to break down the UrlChain into individual URLs, allowing each URL in the chain to be processed separately.

  3. Filtering and Counting:

    • It filters out cases where the original URL (Url) is different from the URLs in the UrlChain.
    • It counts the number of distinct URLs in the chain for each NetworkMessageId (a unique identifier for network messages) and filters to include only those with more than one URL in the chain.

  4. Joining Data:

    • The query performs an inner join with the original UrlClickEvents table to combine data based on NetworkMessageId, ensuring that only events with multiple redirects are included.

  5. Sorting:

    • Finally, the results are sorted by the TimeGenerated column in ascending order, which organizes the data chronologically.

This query helps identify and analyze redirector URLs that users encounter, providing insights into potential security risks or user behavior patterns related to URL redirections.

Details

Michalis Michalos profile picture

Michalis Michalos

Released: March 18, 2025

Tables

UrlClickEvents

Keywords

UrlClickEventsUrlChainUserEmailsTeamsOfficeAppsNetworkMessageIdTimeGenerated

Operators

UrlClickEventswhereextendtodynamicmv-expandsummarizecountbyjoinkind=inneronsortasctostring

Actions