KQL Search

Query Details

HomeAI ToolsDevice Query

KQL URL Protection Report

Query

// KQL URL Protection Report
// https://www.linkedin.com/posts/activity-7190735644749426691-SQlH/

// For organization subscribed to Defender for Office 365, M365 or security admin would most of time go to DefenderXDR portal "Reports" > "Email & Collaboration" > "URL protection report" to check if a particular user has click on a malicious link and if it was blocked by Office 365 ATP.

// Do you know you can access all these Threat Intelligence URL Click Data in CloudAppEvents table? 😎

// Using DefenderXDR Advanced Hunting and the below KQL will provide information similar to the "URL Protection Report", you can further summarize these set of data to allow you to better understand the threats currently faced by your suite of Office 365 applications that support SafeLinks and possibly tweak your SafeLinks configuration to improve your organization Office 365 application protection.🛡️

CloudAppEvents
| where ActionType == "TIUrlClickData"
| where RawEventData.Workload=="ThreatIntelligence"
| extend AppName = RawEventData.AppName
| extend AccountUpn = RawEventData.UserId
| extend UserIP = RawEventData.UserIp
| extend ClickURL = RawEventData.Url
| extend UrlClickAction = RawEventData.UrlClickAction
| extend TimeOfClick = RawEventData.TimeOfClick
| where ActivityType=="Basic"
| project AppName, AccountUpn, UserIP, ClickURL, UrlClickAction, TimeOfClick
| where UrlClickAction == 2 //User blocked from navigating to the URL

Explanation

This KQL query is designed to generate a report similar to the "URL Protection Report" available in the DefenderXDR portal for organizations subscribed to Defender for Office 365. The query retrieves data from the CloudAppEvents table to identify instances where users have clicked on malicious links that were subsequently blocked by Office 365 Advanced Threat Protection (ATP).

Here's a step-by-step breakdown of what the query does:

  1. Select Data Source: It starts by selecting records from the CloudAppEvents table.
  2. Filter by Action Type: It filters the records to include only those where the ActionType is "TIUrlClickData", which pertains to URL click data related to threat intelligence.
  3. Filter by Workload: It further narrows down the records to those where the Workload is "ThreatIntelligence".
  4. Extract Relevant Fields: It extracts and renames several fields from the raw event data:
    • AppName: The name of the application.
    • AccountUpn: The user ID.
    • UserIP: The IP address of the user.
    • ClickURL: The URL that was clicked.
    • UrlClickAction: The action taken when the URL was clicked.
    • TimeOfClick: The time when the URL was clicked.
  5. Filter by Activity Type: It filters the records to include only those with an ActivityType of "Basic".
  6. Select Specific Fields: It selects only the relevant fields for the final output: AppName, AccountUpn, UserIP, ClickURL, UrlClickAction, and TimeOfClick.
  7. Filter by URL Click Action: It further filters the records to include only those where the UrlClickAction is 2, indicating that the user was blocked from navigating to the URL.

In summary, this query helps security administrators to identify and analyze instances where users attempted to click on malicious links but were blocked by Office 365 ATP, providing insights into potential threats and helping to improve the organization's security posture.

Details

Steven Lim profile picture

Steven Lim

Released: August 2, 2024

Tables

CloudAppEvents

Keywords

ThreatIntelligenceUserOffice365ApplicationsSafeLinksCloudAppEvents

Operators

|where==extendproject

Actions