Blog Post

Microsoft Defender for Office 365 Blog

3 MIN READ

Introducing the UrlClickEvents table in advanced hunting with Microsoft Defender for Office 365

Microsoft

Apr 27, 2022

We are excited to announce the public preview for a new data source in Microsoft 365 Defender advanced hunting—the UrlClickEvents table from Microsoft Defender for Office 365, with the changes starting to rollout today.

The UrlClickEvents table is a critical source of information that your security and threat hunting teams can leverage to identify phishing campaigns, potentially malicious clicks, and use this information to respond to threats. The table contains information about Safe Links clicks from email messages, Microsoft Teams, and Office 365 apps (in supported desktop, mobile, and web apps).

As a quick reminder, Safe Links is a feature in Microsoft Defender for Office 365 that provides URL scanning in mail flow, and time-of-click protection from URLs in email messages, Microsoft Teams and Office 365 apps. Safe Links scanning occurs in addition to the regular anti-spam, anti-phish, and anti-malware protection in inbound email messages in Exchange Online Protection (EOP). Safe Links helps protect your users and organization from malicious links that are used in phishing and other attacks.

The UrlClickEvents table schema consists of the following to help security teams hunt and investigate threats targeting their users and organization:

*Column name*	*Description*
Timestamp	The date and time when the user clicked on the link
Url	The full URL that was clicked on by the user
ActionType	Indicates whether the click was allowed or blocked by Safe Links or blocked due to a tenant policy e.g., from Tenant Allow Block list
AccountUpn	User Principal Name of the account that clicked on the link
Workload	The application from which the user clicked on the link, with the values being Email, Office and Teams
NetworkMessageId	The unique identifier for the email that contains the clicked link, generated by Microsoft 365
IPAddress	Public IP address of the device from which the user clicked on the link
ThreatTypes	Verdict at the time of click, which tells whether the URL led to malware, phish or other threats
DetectionMethods	Detection technology which was used to identify the threat at the time of click
IsClickedThrough	Indicates whether the user was able to click through to the original URL or was not allowed
UrlChain	For scenarios involving redirections, it includes URLs present in the redirection chain
ReportId	This is the unique identifier for a click event. Note that for clickthrough scenarios, report ID would have same value, and therefore it should be used to correlate a click event.

Here are a few useful sample queries that can help your security teams get started:

// Search for malicious links where user was allowed to proceed through. 
UrlClickEvents
| where ActionType == "ClickAllowed" or IsClickedThrough !="0"
| where ThreatTypes has "Phish"
| summarize by ReportId, IsClickedThrough, AccountUpn, NetworkMessageId, ThreatTypes, Timestamp

// For email clicks, join URLClickEvents with EmailEvents and EmailPostDeliveryEvents based on NetworkMessageId to determine clickthroughs, potential deliveries through User/Tenant overrides and detection details
UrlClickEvents
| where ThreatTypes has "Phish"
| join EmailEvents on NetworkMessageId,  $left.AccountUpn == $right.RecipientEmailAddress
| project Timestamp, Url, ActionType, AccountUpn, ReportId, NetworkMessageId, ThreatTypes, IsClickedThrough, DeliveryLocation, OrgLevelAction, UserLevelAction

// Determining top clicks by URL and the corresponding Safe Links actions on each click along with user clickthrough 
UrlClickEvents
| where Timestamp > ago(7d)
| extend UrlBlocked = ActionType has_any("ClickBlocked")
| extend UrlAllowed = ActionType has_any('ClickAllowed')
| extend UrlPendingVerdict = ActionType has_any('UrlScanInProgress')
| extend ErrorPage = ActionType has_any('UrlErrorPage')
| summarize Blocked = countif(UrlBlocked), Allowed = countif(UrlAllowed), PendingVerdict = countif(UrlPendingVerdict), 
Error = countif(ErrorPage), ClickedThrough = countif(IsClickedThrough)  by Url

// Merging Defender for Office 365 click data with Endpoint data
UrlClickEvents
| extend Host = tostring(parse_url(Url).Host)
| join (DeviceNetworkEvents) on $left.Host == $right.RemoteUrl and $left.AccountUpn == $right .InitiatingProcessAccountUpn
| where (Timestamp - Timestamp1) between (0min .. 2min)
You could then join this to get the full device timeline

User clickthrough is a configurable setting in the Safe Links policies (see Do not allow users to click through to original URL, and read more over here: Safe Links settings.)

Note that for Teams and Office 365 clicks, NetworkMessageID contains a system generated GUID, and does not map to a Teams/Office 365 entity as of today. Also note that the client IP information is only available for email-based clicks, and is not available for Teams or Office 365 clicks currently. We are looking to update these for Teams by the end of this quarter. The account UPN information is available for email and Teams based clicks and is not available for a subset of Office 365 clicks currently. It should be updated by the end of this quarter.

Do you have questions or feedback about Microsoft Defender for Office 365? Engage with the community and Microsoft experts in the Defender for Office 365 forum.

Updated Apr 27, 2022

Version 2.0

bec

hunting

microsoft 365 defender

phishing

VipulPandey

Microsoft

Joined January 25, 2019

View Profile

Microsoft Defender for Office 365 Blog

Follow this blog board to get notified when there's new activity

8 Comments

Ianskee
Copper Contributor
Sep 05, 2024
Hi, when I ran the 1st sample query - I got this error.

Semantic error
Error message
'where' operator: Failed to resolve table or column expression named 'UrlClickEvents'
How to resolve
Fix semantic errors in your query
MarcinGorski
Copper Contributor
Jul 24, 2024
stromnessian In the situation you described
| where ActionType == "ClickAllowed" and IsClickedThrough == "0"
it means that the user was not prevented and clicked the URL.
stromnessian
Brass Contributor
Jan 11, 2024
In the first query, above, you have
where ActionType == "ClickAllowed" or IsClickedThrough !="0"
but what if ActionType == "ClickAllowed" and IsClickedThrough == "0"? Does that not mean that the user was prevented from reaching the original URL?
gadgetz
Copper Contributor
Feb 04, 2023
GCC Schema

Alerts
------
AlertInfo (replaces DeviceAlertEvents)
- (AlertID, TimeStamp, Severity, Category, Title, AttackTechniques)
AlertEvidence
- (AlertID, TimeStamp, DeviceID, DeviceName, FileName, SHA1, RemoteUrl, RemoteIP, ReportId, Table)

Apps & identities
-----------------
IdentityInfo
IdentityLogonEvents
IdentityQueryEvents
IdentityDirectoryEvents
CloudAppEvents (replaces AppFileEvents)

Email & collaboration
---------------------
EmailEvents
EmailAttachmentInfo
EmailUrlInfo
EmailPostDeliveryEvents

Devices
-------
DeviceInfo
DeviceNetworkInfo
DeviceProcessEvents
DeviceNetworkEvents
DeviceFileEvents
DeviceRegistryEvents
DeviceLogonEvents
DeviceImageLoadEvents
DeviceEvents
DeviceFileCertificateInfo

Threat & Vulnerability Management
---------------------------------
DeviceTvmSoftwareVulnerabilities (replaces DeviceTvmSoftwareInventoryVulnerabilities)
DeviceTvmSoftwareVulnerabilitiesKB
DeviceTvmSecureConfigurationAssessment
DeviceTvmConfigurationAssessmentKB
DeviceTvmSoftwareInventory (replaces DeviceTvmSoftwareInventoryVulnerabilities)
DeviceTvmInfoGathering
DeviceTvmInfoGatheringKB
DeviceTvmSoftwareEvidenceBeta

Missing tables
--------------
UrlClickEvents
AppFileEvents (deprecated)
AADSignInEventsBeta
AADSpnSignInEventsBeta
DeviceAlertEvents (deprecated)
DeviceTvmSoftwareInventoryVulnerabilities (deprecated)
gadgetz
Copper Contributor
Feb 04, 2023
Ringer77260 There is usually a long delay between when tables are available in the commercial cloud vs. government clouds. I've been sitting here tonight analyzing which tables are available in which clouds because I often come across queries that don't work and have to come up with workarounds. For example, I operate in GCC and I don't have the tables for UrlClickEvents, despite using the requisite features in our tenant. I'm going to check with my Microsoft representatives or open a support ticket to find out more. However, if it's unreleased they generally won't provide a release date.
shams02
Copper Contributor
Oct 07, 2022
When using defender API, getting error for UrlClickEvents table.
Here is an example query payload and url
{'Query': 'UrlClickEvents | take 3'}
https://api-us.securitycenter.microsoft.com/api/advancedqueries/run

Error: 400 Client Error: Bad Request for url: https://api-us.securitycenter.microsoft.com/api/advancedqueries/run

However similar query for another table works perfect. Example
{'Query': 'DeviceProcessEvents | take 3'}

What is special about UrlClickEvents table that API gives 400 error?
Ringer77260
Copper Contributor
Jul 08, 2022
This functionality does not appear to be available in GCC. Any timetable on when that will be available?
ScrJeff
Brass Contributor
Jun 06, 2022
Is it possible in advanced hunting to look for content inside a text-based attachment, for example html? We're seeing an increase in phishing using base64 obfuscated code in HTML attachments. The html file includes javascript that decodes the base64 item, turning it back into html code that sends the user to a fake MS365 login page at a malicious domain. It would be helpful it hunting could isolate this particular html based on its contents, and to create a custom detection rule to quarantine them.

// Search for malicious links where user was allowed to proceed through. \nUrlClickEvents\n| where ActionType == \"ClickAllowed\" or IsClickedThrough !=\"0\"\n| where ThreatTypes has \"Phish\"\n| summarize by ReportId, IsClickedThrough, AccountUpn, NetworkMessageId, ThreatTypes, Timestamp

// For email clicks, join URLClickEvents with EmailEvents and EmailPostDeliveryEvents based on NetworkMessageId to determine clickthroughs, potential deliveries through User/Tenant overrides and detection details\nUrlClickEvents\n| where ThreatTypes has \"Phish\"\n| join EmailEvents on NetworkMessageId, $left.AccountUpn == $right.RecipientEmailAddress\n| project Timestamp, Url, ActionType, AccountUpn, ReportId, NetworkMessageId, ThreatTypes, IsClickedThrough, DeliveryLocation, OrgLevelAction, UserLevelAction

// Determining top clicks by URL and the corresponding Safe Links actions on each click along with user clickthrough \nUrlClickEvents\n| where Timestamp > ago(7d)\n| extend UrlBlocked = ActionType has_any(\"ClickBlocked\")\n| extend UrlAllowed = ActionType has_any('ClickAllowed')\n| extend UrlPendingVerdict = ActionType has_any('UrlScanInProgress')\n| extend ErrorPage = ActionType has_any('UrlErrorPage')\n| summarize Blocked = countif(UrlBlocked), Allowed = countif(UrlAllowed), PendingVerdict = countif(UrlPendingVerdict), \nError = countif(ErrorPage), ClickedThrough = countif(IsClickedThrough) by Url

// Merging Defender for Office 365 click data with Endpoint data\nUrlClickEvents\n| extend Host = tostring(parse_url(Url).Host)\n| join (DeviceNetworkEvents) on $left.Host == $right.RemoteUrl and $left.AccountUpn == $right .InitiatingProcessAccountUpn\n| where (Timestamp - Timestamp1) between (0min .. 2min)\nYou could then join this to get the full device timeline

\n *Column name* \n	\n *Description* \n
\n Timestamp \n	\n The date and time when the user clicked on the link \n
\n Url \n	\n The full URL that was clicked on by the user \n
\n ActionType \n	\n Indicates whether the click was allowed or blocked by Safe Links or blocked due to a tenant policy e.g., from Tenant Allow Block list \n
\n AccountUpn \n	\n User Principal Name of the account that clicked on the link \n
\n Workload \n	\n The application from which the user clicked on the link, with the values being Email, Office and Teams \n
\n NetworkMessageId \n	\n The unique identifier for the email that contains the clicked link, generated by Microsoft 365 \n \n
\n IPAddress \n \n	\n Public IP address of the device from which the user clicked on the link \n
\n ThreatTypes \n \n	\n Verdict at the time of click, which tells whether the URL led to malware, phish or other threats \n
\n DetectionMethods \n	\n Detection technology which was used to identify the threat at the time of click \n
\n IsClickedThrough \n	\n Indicates whether the user was able to click through to the original URL or was not allowed \n
\n UrlChain \n	\n For scenarios involving redirections, it includes URLs present in the redirection chain \n
\n ReportId \n	\n This is the unique identifier for a click event. Note that for clickthrough scenarios, report ID would have same value, and therefore it should be used to correlate a click event. \n

\n *Column name* \n	\n *Description* \n
\n Timestamp \n	\n The date and time when the user clicked on the link \n
\n Url \n	\n The full URL that was clicked on by the user \n
\n ActionType \n	\n Indicates whether the click was allowed or blocked by Safe Links or blocked due to a tenant policy e.g., from Tenant Allow Block list \n
\n AccountUpn \n	\n User Principal Name of the account that clicked on the link \n
\n Workload \n	\n The application from which the user clicked on the link, with the values being Email, Office and Teams \n
\n NetworkMessageId \n	\n The unique identifier for the email that contains the clicked link, generated by Microsoft 365 \n \n
\n IPAddress \n \n	\n Public IP address of the device from which the user clicked on the link \n
\n ThreatTypes \n \n	\n Verdict at the time of click, which tells whether the URL led to malware, phish or other threats \n
\n DetectionMethods \n	\n Detection technology which was used to identify the threat at the time of click \n
\n IsClickedThrough \n	\n Indicates whether the user was able to click through to the original URL or was not allowed \n
\n UrlChain \n	\n For scenarios involving redirections, it includes URLs present in the redirection chain \n
\n ReportId \n	\n This is the unique identifier for a click event. Note that for clickthrough scenarios, report ID would have same value, and therefore it should be used to correlate a click event. \n