In May I had the pleasure to be invited to the KQL Café which is hosted by Gianni Castaldi & Alex Verboon. Within this format they empower people to work with KQL and share various tips and tricks. So this is not a usual blogpost but rather a summary and resource hub for the things I presented within the KQL Café.
Recording
You can find the full recording of the KQL Café on YoutTube.
Summary and Resources
To make the content of my talk more accessible, you can find a summary of the individual topics, including the leveraged KQL queries and further resources as part of this post. The KQL queries were mostly consuming the Entra ID Sign-In and Audit Logs. You can forward them to your Microsoft Sentinel or Log Analytics workspace.
What the heck is ITDR?!
Identity Threat Detection and Response (ITDR) is currently one of my favourite topics. It’s basically a combination of the disciplines Identity and Access Management (IAM) and the cyber security disciplines detection and response. Similar to other cybersecurity topics there’s a rule of thumb: The more you invest on the preventive side to increase your identity security posture — the less effort you (hopefully) have on the detection and response side 🤞🤞. Within my talk for the KQL Café I addressed various of those ITDR topics that help you on the preventive side.
Cross Tenant Access
Cross Tenant Access is basically when users in your Entra ID tenant access other tenants as guests or a guest user in your tenant accesses your tenant. Historically, organisations have restricted their inbound access and introduces guest user processes and maybe had a list of organisations that we’re allowed to invite guests from. Thanks to cross tenant access settings in Entra ID we can configure both the in- and outbound access on a granular basis.
The Entra ID Sign-In logs deliver us helpful telemetry for cross tenant access. Unfortunately you’ll get only the Tenant ID and not the organisation or domain name. For this purpose you can combine KQL with PowerShell and parse the acquired Tenant IDs from the logs with the Microsoft Graph API.
- Query and PowerShell code: ITDR/Queries/Entra-Cross-Tenant-Access.md at main · nicolonsky/ITDR (github.com)
- Microsoft Graph API Ref: tenantRelationship: findTenantInformationByDomainName — Microsoft Graph v1.0 | Microsoft Learn
- Cross Tenant Access: Cross-tenant access overview — Microsoft Entra External ID | Microsoft Learn
Conditional Access Policy Gaps
Within the Entra ID Sign-In logs we have the ConditionalAccessStatus field, indicating the Conditional Access (CA) policy status. notApplied means no policies applied or that you might have some gaps as part of your CA deployment. Due to service dependencies and bootstrap applications some of those gaps are ‘by design’ but often this is due to lack of ‘all cloud apps’ selection or excluded accounts.
To get meaningful results, we can leverage the Sentinel User and Entity Behavior Analytics (UEBA) table and filter out break glass and other accounts.
- KQL Query to identify CA gaps: ITDR/Queries/Entra-Conditional-Access-Not-Applied.md at main · nicolonsky/ITDR (github.com)
- Excellent Microsoft 425 show explaining bootstrap apps: https://www.youtube.com/watch?v=HylR3JLUtMs
- More KQL and CA: Conditional Access insights and reporting workbook — Microsoft Entra ID | Microsoft Learn
Conditional Access Report Only Telemetry
Personally, I’d never approach a Conditional Access deployment without Log Analytics to first collect telemetry before enabling Conditional Access policies or making bigger changes to policies in production. With KQL we can leverage the CA policy processing result also for policies in report only mode to predict the impact.
The Sign-In logs contain the CA policy status for each policy in scope and allow us to predict the impact. From a KQL perspective this is also an interesting scenario as the ConditionalAccessPolicies column is a dynamic property holding an array of processed CA policies. mv-expand maps each CA policy into a new entry, allowing us to easily filter for all failed policies.
- KQL Query: ITDR/Queries/Entra-Conditional-Access-Failure-By-Policy.md at main · nicolonsky/ITDR (github.com)
Conditional Access Policies Log Ingestion
Although the Conditional Access status can aid with troubleshooting and operations of CA, this can be an unexpected driver for ingestion costs, especially if you deploy a high amount of conditional access policies in your environment.
The following KQL query visualises the data amount for the CA status in MB or GB:
let lookback = 360d;
union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(lookback)
| extend size = estimate_data_size(ConditionalAccessPolicies_string)
| extend ingestedKB = toreal(size)/1024.
| extend ingestedMB = toreal(ingestedKB)/1024
| extend ingestedGB = toreal(ingestedMB)/1024
| summarize CASize=sum(ingestedMB) by bin(TimeGenerated, 1d)
| render columnchart
To save some buck$ you can setup a transformation rule to project-away this information or even create a dedicated table or only retain specific policies.
Additional information: Custom data ingestion and transformation in Microsoft Sentinel | Microsoft Learn
Entra ID Device Attribute Tracking
Let’s leave the beast of Conditional Access behind us and focus on a challenge where some Entra ID device attributes were missing from the Entra ID audit logs. To circumvent this gap, I leveraged a dynamic restricted management administrative unit for the particular enrollmentProfile attribute and monitored the administrative unit for changes with KQL.
- KQL Query: ITDR/Queries/Entra-Restricted-Administrative-Unit-Device-Added.md at main · nicolonsky/ITDR (github.com)
- Administrative Units: Restricted management administrative units in Microsoft Entra ID (Preview) — Microsoft Entra ID | Microsoft Learn
Entra ID & Passkeys
Last but not least let’s tackle one of the latest and greates innovations of Entra ID — the public preview of passkeys. The FIDO alliance publishes a metadata service blob containing information about FIDO2 certified keys including their attestation guid and model information. This makes it a perfect scenario for the externaldata operator.
- FIDO alliance MDS blob: https://fidoalliance.org/metadata
- KQL query for enrichment: ITDR/Queries/Entra-Passkey-Registration.md at main · nicolonsky/ITDR (github.com)
- Dedicated blog post to this topic: Have you heard about passkeys and AAGuids? | by Nicola | Medium
Ciao and enjoy your Coffee
Thanks Gianni and Alex for having me and all the positive responses I got for my session. I’ll definitely enjoy the next occurrences of the KQL Café and maybe come back with a bag full of beans or new KQL skills ☕️😉.
// Nicola
P.S.: I’m adding new KQL queries and ITDR resources to my GitHub repository so don’t forget to leave a ⭐️.
