KQL Queries and Workbook Analysis for Identity Governance (SC-300)
KQL Queries and Workbook Analysis in Microsoft Entra Identity Governance
Why Is This Important?
Kusto Query Language (KQL) queries and Azure Workbooks are essential tools for planning and automating identity governance in Microsoft Entra ID (formerly Azure AD). Organizations need to monitor, analyze, and report on identity-related activities such as sign-in patterns, access reviews, entitlement management, and privileged identity usage. KQL enables security teams to write powerful queries against log data, while Workbooks provide interactive, visual dashboards that consolidate this data into actionable insights. For the SC-300 exam, understanding how KQL and Workbooks work together is critical because Microsoft tests your ability to plan and implement identity governance strategies using these tools.
What Are KQL Queries?
KQL (Kusto Query Language) is the query language used across Microsoft services including Azure Monitor, Microsoft Sentinel, Log Analytics, and Microsoft Entra ID logs. It allows administrators to:
- Query Sign-in logs and Audit logs stored in Log Analytics workspaces
- Filter, summarize, and aggregate identity-related data
- Identify anomalies, trends, and compliance gaps in identity governance
- Create custom alerts based on specific identity events
Key tables relevant to identity governance include:
- SigninLogs: Contains interactive sign-in data including user, application, location, conditional access results, and risk levels
- AADNonInteractiveUserSignInLogs: Contains non-interactive (service/app-based) sign-in data
- AuditLogs: Contains directory changes such as role assignments, group modifications, access package assignments, and policy changes
- AADProvisioningLogs: Contains provisioning activity data for automated user lifecycle management
- IdentityInfo: Contains identity metadata used in Microsoft Sentinel
What Are Azure Workbooks?
Azure Workbooks are interactive reporting tools available in the Azure portal that combine text, KQL queries, metrics, and parameters into rich visual reports. In the context of Microsoft Entra ID, Workbooks are found under Microsoft Entra ID > Monitoring > Workbooks. They provide pre-built and customizable templates for identity governance scenarios including:
- Sign-in analysis: Understanding authentication patterns and failures
- Conditional Access insights: Evaluating policy impact and gaps
- Sensitive operations: Tracking privileged identity activities
- Access package activity: Monitoring entitlement management usage
- Cross-tenant access: Reviewing external collaboration patterns
How It Works
Step 1: Prerequisites
To use KQL queries and Workbooks for identity governance, you must first:
- Configure Diagnostic Settings in Microsoft Entra ID to send logs (SigninLogs, AuditLogs, etc.) to a Log Analytics workspace
- Ensure the appropriate Microsoft Entra ID license (P1 or P2) is in place for advanced log data
- Assign the user at least Reports Reader, Security Reader, or Global Reader role to access Workbooks
Step 2: Writing KQL Queries
KQL queries follow a tabular data flow model. A basic query structure looks like this:
TableName
| where TimeGenerated > ago(7d)
| where ColumnName == "value"
| summarize Count = count() by GroupingColumn
| order by Count desc
Example: Find all failed sign-ins in the last 7 days grouped by user:
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName
| order by FailedCount desc
Example: Find all role assignment changes in audit logs:
AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName == "Add member to role"
| project TimeGenerated, InitiatedBy.user.userPrincipalName, TargetResources[0].displayName
Step 3: Using Workbooks
Navigate to Microsoft Entra ID > Monitoring > Workbooks. You can:
- Select from pre-built templates such as "Sign-ins," "Conditional Access Insights and Reporting," or "Sensitive Operations Report"
- Customize templates by editing KQL queries within them
- Add parameters (such as time range selectors and user filters) for interactive exploration
- Pin Workbooks to Azure Dashboards for ongoing monitoring
- Share Workbooks with team members by saving them as shared resources
Step 4: Automating Identity Governance with KQL
KQL queries can feed into automated workflows:
- Create Log Analytics alert rules that trigger when specific identity events occur (e.g., a user is assigned a privileged role outside of PIM)
- Use Azure Logic Apps to respond to alerts automatically (e.g., send a notification, revoke access, trigger an access review)
- Schedule recurring queries to generate compliance reports for auditors
- Integrate with Microsoft Sentinel for advanced identity threat detection using KQL-based analytics rules
Key KQL Operators for the SC-300 Exam
- where: Filters rows based on a condition
- summarize: Aggregates data (count, sum, avg, dcount)
- project: Selects specific columns to display
- extend: Creates calculated columns
- join: Combines data from two tables
- render: Visualizes results as charts (timechart, barchart, piechart)
- ago(): Specifies relative time (e.g., ago(7d) for 7 days ago)
- distinct: Returns unique values
- top: Returns the top N rows
- parse: Extracts values from string fields
Common Workbook Templates to Know for SC-300
- Conditional Access Insights and Reporting: Analyzes the impact of CA policies, shows which policies are being triggered, and identifies gaps
- Sign-in Failure Analysis: Breaks down authentication failures by error code, user, and application
- Authentication Methods Activity: Tracks MFA registration and usage across the organization
- Sensitive Operations Report: Monitors high-impact administrative actions
- Cross-tenant Access Activity: Reviews B2B collaboration and cross-tenant sign-in behavior
Exam Tips: Answering Questions on KQL Queries and Workbook Analysis
1. Know the prerequisites: Exam questions often test whether you know that diagnostic settings must be configured to route logs to a Log Analytics workspace before KQL queries or Workbooks can be used. Without this step, no log data is available to query.
2. Understand which table to query: Be clear on the differences between SigninLogs (interactive sign-ins), AADNonInteractiveUserSignInLogs (non-interactive), AuditLogs (directory changes), and AADProvisioningLogs (provisioning). Exam scenarios will describe a situation and expect you to pick the correct table.
3. Read KQL query snippets carefully: The exam may present a KQL query and ask what it returns. Focus on the where clause (filtering), summarize clause (aggregation), and project clause (output columns) to determine the result.
4. Know the role requirements: To view Workbooks, users need at minimum Reports Reader role. To edit and save shared Workbooks, they typically need Workbook Contributor permissions on the resource group. Questions may test role-based access to monitoring data.
5. Differentiate Workbooks from other tools: Understand that Workbooks are for interactive visualization and analysis, while Log Analytics alerts are for automated detection and response. If a question asks about ongoing monitoring and alerting, the answer is likely alert rules, not Workbooks.
6. Focus on governance scenarios: The exam ties KQL and Workbooks to identity governance use cases. Be ready for scenarios involving: monitoring access review completion rates, tracking entitlement management access package assignments, auditing PIM role activations, and reviewing Conditional Access policy effectiveness.
7. Remember the ResultType field: In SigninLogs, ResultType == 0 means a successful sign-in. Any other value indicates a failure or interruption. This is commonly tested.
8. Know the time functions: ago(1h), ago(7d), ago(30d) are frequently used in exam scenarios. Understand that TimeGenerated is the timestamp column used for time-based filtering.
9. Practice interpreting Workbook visuals: The exam may show a screenshot of a Workbook and ask you to interpret the data or recommend an action based on the results. Look at axis labels, legends, and data trends carefully.
10. Connect KQL to automation: For questions about automating identity governance tasks, remember that KQL queries can be used in Azure Monitor alert rules which can trigger Logic Apps or Azure Functions to take automated remediation actions such as disabling accounts, revoking sessions, or notifying administrators.
11. Licensing awareness: Microsoft Entra ID P1 provides basic sign-in and audit logs. Microsoft Entra ID P2 adds risk-based logs and more detailed PIM audit data. Be aware that some Workbook templates require P2 licensing to display complete data.
12. Workbook sharing and saving: Workbooks can be saved as private (only visible to the creator) or shared (visible to others with access to the resource group). Exam questions may test your understanding of this distinction when asking about collaboration and reporting scenarios.
