Bence Weiszmann Microsoft Security notes

Entra IDKQL

Entra ID sign-in gap analysis with KQL

Four KQL queries over the Entra sign-in logs that surface legacy authentication, single-factor sign-ins, sign-ins no Conditional Access policy applied to, and which MFA methods are really in use.

The problem

Conditional Access and MFA policies describe the intended state. The sign-in logs show the actual one: old protocols still in use, accounts signing in with a password only, applications that slip past every policy.

Approach

Send the Entra sign-in logs to a Log Analytics workspace (or Microsoft Sentinel) with diagnostic settings, then query SigninLogs. Each query answers one question and produces a short list you can act on.

Implementation

These queries run against the SigninLogs table in Log Analytics or Microsoft Sentinel. The table only exists once Diagnostic settings in the Entra admin center send SignInLogs to the workspace.

Legacy authentication still in use

Modern clients report as Browser or Mobile Apps and Desktop clients. Everything else is a legacy protocol that cannot do MFA.

SigninLogs
| where TimeGenerated > ago(30d)
| where isnotempty(ClientAppUsed)
| where ClientAppUsed !in ("Browser", "Mobile Apps and Desktop clients")
| summarize SignIns = count(), Apps = make_set(AppDisplayName, 10) by UserPrincipalName, ClientAppUsed
| sort by SignIns desc

Successful single-factor sign-ins

Accounts that signed in with only one factor. Some will be expected (trusted locations, service scenarios); the rest are the ones to look at first.

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == "0" and AuthenticationRequirement == "singleFactorAuthentication"
| extend Country = tostring(LocationDetails.countryOrRegion)
| summarize SignIns = count(), Apps = make_set(AppDisplayName, 10), Countries = make_set(Country, 10) by UserPrincipalName
| sort by SignIns desc

Sign-ins no Conditional Access policy applied to

A successful sign-in with ConditionalAccessStatus of notApplied was not evaluated by any enabled policy.

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == "0" and ConditionalAccessStatus == "notApplied"
| summarize SignIns = count(), Users = dcount(UserPrincipalName) by AppDisplayName
| sort by SignIns desc

Which authentication methods are really used

Useful before moving to phishing-resistant MFA: it shows how many users still rely on push notifications, SMS or voice.

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == "0"
| mv-expand AuthDetail = parse_json(AuthenticationDetails)
| extend Method = tostring(AuthDetail.authenticationMethod)
| where isnotempty(Method) and Method != "Previously satisfied"
| summarize Users = dcount(UserPrincipalName), SignIns = count() by Method
| sort by Users desc

Licensing: sending sign-in logs to Log Analytics requires Microsoft Entra ID P1 or P2, and the workspace is billed by ingested data volume.

Security considerations

Sign-in logs hold personal data such as user names, IP addresses and locations, so access to the workspace should follow least privilege and your retention rules. The queries are read-only. The results show where policies have gaps, so share them only with the people who will close those gaps.