KQL Patterns for Querying Purview Events in CloudAppEvents: Key Insights and Common Pitfalls

Microsoft Purview helps organizations classify, protect, and govern sensitive data across Microsoft 365. Security teams often use KQL (Kusto Query Language) in Microsoft Defender XDR Advanced Hunting to investigate Microsoft Purview events, monitor sensitivity labels, and analyze DLP activity stored in CloudAppEvents. However, many administrators encounter incomplete results or slow-running queries because critical Purview data is stored within nested JSON fields rather than standard columns. 

This guide explains the most effective CloudAppEvents KQL patterns, highlights common mistakes that impact query performance, and demonstrates how to reliably retrieve Purview activity logs, sensitivity label information, DLP indicators, and other event metadata. 

RawEventData is where the real data hides 

One of the biggest misconceptions when writing CloudAppEvents KQL queries is assuming that all Microsoft Purview information is directly available through the ActionType field. In reality, important attributes—including Sensitivity Label IDs, DLP policy names, Sensitive Information Type (SIT) matches, and justification text—are stored inside the RawEventData JSON object. 

To retrieve these values efficiently, parse the JSON only once and reuse the parsed object throughout the query. Repeatedly calling parse_json(tostring(RawEventData)) forces the query engine to process the same JSON multiple times, which can significantly increase execution time, especially when querying large volumes of Microsoft Defender XDR telemetry. 

let RawParsed = CloudAppEvents
| where Timestamp > ago(7d)
| where ActionType contains “Sensitivity”
| extend raw = parse_json(tostring(RawEventData));

RawParsed
| extend LabelId = tostring(raw.SensitivityLabelEventData.SensitivityLabelId)
| project Timestamp, AccountObjectId, ActionType, ObjectName, LabelId
 

This approach improves readability, reduces processing overhead, and provides a scalable foundation for building more advanced Microsoft Purview KQL queries. 

Two column traps to know before writing anything else 

Several fields in CloudAppEvents behave differently than many administrators expect, often resulting in empty query results or unexpected errors. 

The first issue involves AccountId. Although it may appear suitable for filtering users, it actually stores the Microsoft Entra object GUID rather than a user’s email address or UPN. Filtering AccountId with an email address will return no matching results. When searching for user activity, use AccountObjectId or the appropriate identity field instead. 

The second issue involves ApplicationId. This field stores an integer value rather than a string GUID. Comparing it with string values generates a SEM0025 type mismatch error instead of silently returning no data. Understanding these data types before building Advanced Hunting KQL queries helps eliminate unnecessary troubleshooting and improves query reliability. 

Four patterns worth building once 

Many Microsoft Purview investigations require the same query logic repeatedly. Creating reusable KQL query patterns saves time, improves consistency, and makes ongoing investigations more efficient. 

Common reusable patterns include: 

• Retrieving Sensitivity Label IDs from RawEventData. 

• Extracting Sensitivity Label Names for reporting. 

• Identifying DLP policy matches and enforcement actions. 

• Capturing justification text when users override sensitivity labels. 

• Parsing Sensitive Information Type (SIT) matches. 

• Extracting metadata for compliance investigations and audit reporting. 

Reusable CloudAppEvents KQL examples also make it easier to standardize reporting across security operations teams while reducing maintenance effort. 

The prerequisite that fails silently 

Even perfectly written Microsoft Purview KQL queries may return no results if the required services are not configured correctly. 

To collect Purview events successfully: 

• Deploy Microsoft Defender for Cloud Apps. 

• Enable Microsoft 365 Activities under Settings → Cloud Apps → App Connectors. 

• Verify that activity collection is enabled instead of relying solely on the connector status. 

• Confirm that CloudAppEvents is receiving new telemetry before troubleshooting the query itself. 

A connector may display as Connected while still collecting no usable data. Before assuming the query is incorrect, validate the environment configuration and ensure the required data sources are actively sending telemetry. 

Conclusion 

Writing efficient Microsoft Purview KQL queries requires more than understanding Kusto syntax. Success depends on knowing where Purview stores its data, parsing RawEventData correctly, avoiding common column mismatches, and verifying that Microsoft Defender for Cloud Apps is configured to collect Microsoft 365 activity. 

By following these CloudAppEvents KQL best practices, security analysts can build faster, more reliable Advanced Hunting queries, simplify compliance investigations, improve Purview activity log analysis, and extract valuable insights from Microsoft Defender XDR without unnecessary troubleshooting.