// tools/kql

KQL library

Every Log Analytics query from this site in one place. Copy one, paste it into your workspace and adjust the table or time range.

Key Vault: move from access policies to Azure RBAC

from the post · 12 Aug 2026

Finding vaults still on access policies

kql

// Azure Resource Graph
resources
| where type == "microsoft.keyvault/vaults"
| extend rbac = tobool(properties.enableRbacAuthorization)
| where rbac != true
| project name, resourceGroup, subscriptionId

Six KQL queries for Entra sign-in logs every admin should keep

from the post · 11 Mar 2026

1. Top sign-in failures

kql

SigninLogs
| where TimeGenerated > ago(7d) and ResultType != "0"
| summarize Count = count() by ResultType, ResultDescription
| top 20 by Count

2. One user's recent sign-ins

kql

SigninLogs
| where TimeGenerated > ago(3d)
| where UserPrincipalName =~ "jane.doe@contoso.com"
| project TimeGenerated, AppDisplayName, ResultType, ResultDescription,
          IPAddress, Location = tostring(LocationDetails.countryOrRegion),
          ConditionalAccessStatus
| order by TimeGenerated desc

3. Sign-ins blocked by Conditional Access

kql

SigninLogs
| where TimeGenerated > ago(1d) and ConditionalAccessStatus == "failure"
| summarize Count = count() by UserPrincipalName, AppDisplayName
| order by Count desc

4. Successful sign-ins from new countries

kql

let known = SigninLogs
| where TimeGenerated between (ago(30d) .. ago(1d)) and ResultType == "0"
| distinct UserPrincipalName, Country = tostring(LocationDetails.countryOrRegion);
SigninLogs
| where TimeGenerated > ago(1d) and ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| join kind=leftanti known on UserPrincipalName, Country
| project TimeGenerated, UserPrincipalName, Country, IPAddress, AppDisplayName

5. Password spray pattern

kql

SigninLogs
| where TimeGenerated > ago(1h) and ResultType == "50126"
| summarize Users = dcount(UserPrincipalName) by IPAddress
| where Users > 10
| order by Users desc

6. Legacy authentication still in use

kql

SigninLogs
| where TimeGenerated > ago(14d)
| where ClientAppUsed !in ("Browser", "Mobile Apps and Desktop clients")
| summarize Count = count() by ClientAppUsed, UserPrincipalName, AppDisplayName
| order by Count desc

7. Non-interactive sign-ins by app

kql

AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(1d)
| summarize Count = count(), Failures = countif(ResultType != "0") by AppDisplayName
| order by Count desc

8. Service principal sign-ins from unexpected IPs

kql

AADServicePrincipalSignInLogs
| where TimeGenerated > ago(7d) and ResultType == "0"
| summarize IPs = make_set(IPAddress, 20), Count = count() by ServicePrincipalName
| where array_length(IPs) > 3
| order by Count desc

Named locations: getting IP ranges and countries right in Conditional Access

from the post · 17 Dec 2025

Building a country block safely

kql

SigninLogs
| where TimeGenerated > ago(30d) and ResultType == "0"
| summarize Users = dcount(UserPrincipalName), SignIns = count() by Country = tostring(LocationDetails.countryOrRegion)
| order by SignIns desc

Turning on Defender CSPM across a landing zone

from the post · 3 Dec 2025

Checking coverage

kql

// Azure Resource Graph: Defender CSPM status per subscription
securityresources
| where type == "microsoft.security/pricings" and name == "CloudPosture"
| project subscriptionId, tier = tostring(properties.pricingTier)
| order by tier asc

Test Conditional Access safely with report-only mode and What If

from the post · 26 Nov 2025

Reviewing the impact

kql

SigninLogs
| where TimeGenerated > ago(7d)
| mv-expand ConditionalAccessPolicies
| where ConditionalAccessPolicies.displayName == "Require compliant device"
| where ConditionalAccessPolicies.result == "reportOnlyFailure"
| summarize Failures = count() by UserPrincipalName, AppDisplayName
| order by Failures desc

Break-glass accounts done right

from the post · 12 Nov 2025

Monitoring

kql

SigninLogs
| where UserPrincipalName in~ ("bg-admin1@contoso.onmicrosoft.com", "bg-admin2@contoso.onmicrosoft.com")
| project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName, ResultType