CMMC Entra ID KQL for Level 2 Evidence

An assessor won’t accept “we monitor Entra ID” on faith. I need records that show who signed in, what changed, when it happened, and whether the control worked.

That is where CMMC Entra ID KQL helps. When I prepare Level 2 evidence, I use KQL to pull repeatable samples from Entra sign-in and audit data, then pair those results with policy, retention, and review records. The query is support for the evidence, not the whole case.

What CMMC Level 2 expects from Entra ID logging

For CMMC Level 2, the audit requirement is plain: logs must help me detect, investigate, and report unauthorized activity. In practice, that means I need timestamps, user identity, source details such as IP address, event results, and enough context to explain what happened.

Microsoft’s CMMC Level 2 guidance for Entra ID is useful because it ties identity settings to the control set. I also keep ND-ISAC guidance on privileged functions close at hand when I package admin-role evidence, because privileged activity usually gets extra attention in an assessment.

Microsoft now uses the name Entra ID, although older notes, saved searches, and portal screenshots may still say Azure AD. That naming change matters less than the underlying point: I need signin logs, audit logs, and retention that covers the review period.

KQL helps because it gives me a repeatable method. A screenshot from the portal can show that logging exists. A saved query can show that I know how to search for failed sign-ins, role changes, policy edits, and user lifecycle events across a defined time window.

Queries support evidence collection. They do not replace policy, procedure, configuration screenshots, and documented review.

Set up the data before the KQL matters

Before any query is useful, I need the right Entra logs in a Log Analytics workspace. Most of the examples below assume the data lands in tables such as SigninLogs and AuditLogs, either through Microsoft Sentinel or direct diagnostic settings. If I only rely on the default portal view, my evidence window may be too short.

This is the quick map I use when I prepare evidence:

Evidence question	Main table	What I want to show
Who attempted access?	SigninLogs	User, app, IP, time, success or failure
Did MFA or Conditional Access apply?	SigninLogs	Authentication requirement and policy result
Who changed identity settings?	AuditLogs	Operation, actor, target, result
Who changed admin access or accounts?	AuditLogs	Role, user, app, and lifecycle events

Retention is part of the story. If I keep only a few days of logs, I may not have enough data for an assessor to sample. I want evidence that logging is on, kept long enough to support review, and actually reviewed by staff.

If logs were never exported, or the retention already expired, KQL can’t recreate the missing history. That gap shows up fast in an assessment.

KQL queries I use for Entra ID evidence

I build queries to answer assessor questions, not to fill a dashboard. I start broad, test the fields in my tenant, then save the final search with a clear name and date range.

Failed sign-ins and weak client paths

This query is one of my first checks:

SigninLogs | where TimeGenerated >= ago(30d) | where ResultType != 0 | summarize FailedAttempts=count(), SourceIPs=make_set(IPAddress, 5) by UserPrincipalName, AppDisplayName | order by FailedAttempts desc

The data source is SigninLogs. It shows repeated failures by user and app, with sample IP addresses. For a CMMC review, this helps me prove I can trace unauthorized or unsuccessful access attempts and identify accounts that deserve follow-up.

I also check for older client patterns that may bypass stronger controls:

SigninLogs | where TimeGenerated >= ago(30d) | where ClientAppUsed in ("IMAP", "POP", "SMTP", "MAPI over HTTP", "Other clients") | summarize Attempts=count(), Success=countif(ResultType == 0), Failure=countif(ResultType != 0) by ClientAppUsed, AppDisplayName

This also uses SigninLogs. It matters because legacy or weak client paths often create risk that policy alone doesn’t explain. If I claim modern auth enforcement, this query helps me back that up with tenant activity.

MFA, Conditional Access, and policy activity

For Level 2 evidence, I want more than a screenshot of a policy. I want proof that sign-ins interact with those controls.

SigninLogs | where TimeGenerated >= ago(30d) | summarize Attempts=count(), Success=countif(ResultType == 0), Failure=countif(ResultType != 0) by ConditionalAccessStatus, AuthenticationRequirement

This query comes from SigninLogs. It gives me a simple rollup of how many sign-ins hit Conditional Access and what authentication level applied. By itself, it doesn’t prove the policy is correct. Paired with the policy export and an approval record, it shows that the control is active and generating usable records.

I also want a change trail for the policies themselves:

AuditLogs | where TimeGenerated >= ago(90d) | where Category == "Policy" | project TimeGenerated, OperationName, Result, InitiatedBy=tostring(InitiatedBy.user.userPrincipalName), TargetResources | order by TimeGenerated desc

The data source here is AuditLogs. An assessor cares because security settings should not change without traceability. If a Conditional Access rule was edited last month, I need to show who changed it and what object was touched.

Privileged roles, account changes, and app consent

Privileged access deserves tighter attention, so I keep a focused role-change query ready:

AuditLogs | where TimeGenerated >= ago(90d) | where OperationName has_any ("Add member to role", "Add eligible member to role", "Add member to directory role", "Remove member from role") | project TimeGenerated, OperationName, Result, InitiatedBy=tostring(InitiatedBy.user.userPrincipalName), TargetResources

This query uses AuditLogs. It helps me show who granted or removed elevated access, when that happened, and whether the action succeeded. That is useful for privileged function reviews and for proving I can trace admin changes.

For user lifecycle activity, I start with a broad search:

AuditLogs | where TimeGenerated >= ago(90d) | where Category == "UserManagement" | project TimeGenerated, OperationName, Result, InitiatedBy=tostring(InitiatedBy.user.userPrincipalName), TargetResources | order by TimeGenerated desc

This query also uses AuditLogs. I use it to sample account creation, updates, disables, and deletions. If my written process says offboarding disables access quickly, this query gives me events I can cross-check against tickets and HR triggers.

Application consent and service principal creation can also open risk paths, so I include an app-focused search:

AuditLogs | where TimeGenerated >= ago(90d) | where OperationName has_any ("Add service principal", "Add application", "Consent to application") | project TimeGenerated, OperationName, Result, InitiatedBy=tostring(InitiatedBy.user.userPrincipalName), TargetResources

This matters when third-party apps touch Microsoft 365 data. It lets me show who introduced a new trust path into the tenant. Operation names can vary a bit by tenant and event type, so I always test and tune these filters before I freeze the evidence set.

How I package KQL output for an assessor

A CSV export by itself makes assessors work harder. I package each saved search with the query text, the date range, a screenshot of the result set, the related retention setting, and a short note that explains why I ran it. I also link each query to the written policy or procedure that the activity supports.

That method works well for Small Business IT teams because the evidence story rarely stays inside identity alone. In real environments, Entra records connect to Cloud Infrastructure, Office 365 Migration history, and Data Center Technology boundaries. For hospitality clients, the same identity trail can touch Restaurant POS Support and Kitchen Technology Solutions when staff accounts reach reporting or vendor systems.

My Cybersecurity Services work gets stronger when Entra evidence lines up with Endpoint Security, Device Hardening, and Cloud Management records. Some clients look for Innovative IT Solutions or Tailored Technology Services. I translate that into clean evidence, practical Technology Consulting, and the habits a strong Business Technology Partner should bring to every review. That includes Infrastructure Optimization, Digital Transformation, IT Strategy for SMBs, Secure Cloud Architecture, Managed IT for Small Business, and Business Continuity & Security.

Conclusion

Good KQL turns Entra ID activity into evidence that an assessor can follow. The strongest package shows the query, the source table, the retention setting, the policy behind the control, and proof that someone reviews the output.

When I build Level 2 evidence this way, the conversation changes. I am no longer saying “we watch identity.” I am showing how, where, and over what period I can prove it.