Detect risky sign-ins, MFA weaknesses, legacy authentication, privilege escalation, and unusual service principal activity using Kusto Query Language.
Your Azure environment generates thousands of security events every day. But how many are actually investigated?
For many organizations, the problem isn't a lack of security data. It's knowing how to turn that data into actionable security intelligence. Microsoft Entra ID, Azure Log Analytics, and Microsoft Sentinel provide valuable visibility into authentication activity, administrative changes, and identity threats. Yet collecting logs alone doesn't secure an environment.
That's where Kusto Query Language (KQL) becomes invaluable. Here are five essential Azure security KQL queries to help teams detect and investigate common identity security risks.
Why KQL Matters for Azure Security Monitoring
KQL lets security teams search large volumes of telemetry in Azure Monitor, Log Analytics, and Microsoft Sentinel. It can help answer questions such as:
- Are risky users signing in from unexpected locations?
- Are successful sign-ins recorded with only a single-factor authentication requirement?
- Are legacy authentication clients still in use?
- Who has changed privileged Microsoft Entra roles?
- Are application identities authenticating from unfamiliar IP addresses?
Before you begin: Send the relevant Microsoft Entra diagnostic logs to a Log Analytics workspace. Availability of detailed identity risk signals depends on your Microsoft Entra licensing. Test and tailor these examples to your tenant before using them for production alerts.
1. Detect Risky Sign-ins in Microsoft Entra ID
Security objective: Investigate potentially compromised user accounts and suspicious authentication events.
Microsoft Entra ID Protection identifies potentially risky sign-in behavior. This query surfaces recent medium- or high-risk events and adds user, application, IP address, and risk context.
SigninLogs
| where TimeGenerated >= ago(7d)
| where RiskLevelDuringSignIn in~ ("high", "medium")
or RiskLevelAggregated in~ ("high", "medium")
| project
TimeGenerated,
UserPrincipalName,
IPAddress,
Location,
AppDisplayName,
RiskLevelDuringSignIn,
RiskLevelAggregated,
RiskState,
ResultType
| order by TimeGenerated desc
What to look for
Review the user, source IP address, location, application, sign-in outcome, and risk state. A high-risk sign-in from a new location may merit urgent investigation, especially if it coincides with unexpected administrative changes or endpoint alerts.
Investigation tip: Correlate results with Conditional Access outcomes, authentication details, device information, and other alerts. A risky sign-in is an indicator to investigate, not definitive proof of compromise. Depending on licensing, risk levels may be hidden.
2. Find Successful Sign-ins Without an MFA Requirement
Security objective: Investigate potentially unexpected single-factor authentication events and policy gaps.
Organizations can mistakenly equate having an MFA policy with complete MFA coverage. This query lists successful sign-ins for which Microsoft Entra recorded a single-factor authentication requirement.
SigninLogs
| where TimeGenerated >= ago(7d)
| where ResultType == "0"
| where AuthenticationRequirement =~ "singleFactorAuthentication"
| project
TimeGenerated,
UserPrincipalName,
AppDisplayName,
IPAddress,
Location,
AuthenticationRequirement,
ConditionalAccessStatus,
ClientAppUsed
| order by TimeGenerated desc
What to look for
Investigate applications or users with unexpected single-factor sign-ins. Review Conditional Access policies and exclusions alongside the sign-in's detailed authentication context.
Important limitation: This does not list everyone who is unregistered for MFA or prove an account is unprotected. MFA coverage requires separate analysis of authentication method registration and applicable policy controls.
3. Identify Legacy Authentication Activity
Security objective: Detect older authentication paths that may not support modern security safeguards.
Legacy authentication protocols and clients remain security concerns, especially when they cannot satisfy modern MFA requirements. Use the following query to group activity by account, client type, and IP address.
SigninLogs
| where TimeGenerated >= ago(14d)
| where ClientAppUsed in~ (
"Exchange ActiveSync",
"IMAP",
"POP",
"SMTP",
"MAPI",
"Other clients"
)
| summarize
Attempts = count(),
SuccessfulAttempts = countif(ResultType == "0"),
FailedAttempts = countif(ResultType != "0"),
LastSeen = max(TimeGenerated)
by UserPrincipalName, ClientAppUsed, IPAddress
| order by Attempts desc
What to look for
Prioritize successful legacy-client authentications, repeated failures, and unexpected source IP addresses. Confirm whether a legitimate application or device depends on the observed protocol before changing access policies.
Investigation tip: Client classifications vary across authentication flows. This query is not a complete protocol inventory; check non-interactive user sign-in logs as well.
4. Monitor Privileged Role Changes
Security objective: Investigate unexpected privilege escalation and sensitive directory administration.
Privileged directory roles, such as Global Administrator and Privileged Role Administrator, can grant powerful access. Monitoring assignments, removals, and eligibility changes can expose unauthorized modifications.
AuditLogs
| where TimeGenerated >= ago(7d)
| where ActivityDisplayName has_any (
"Add member to role",
"Remove member from role",
"Add eligible member to role",
"Remove eligible member from role"
)
| extend InitiatedByUser =
tostring(InitiatedBy.user.userPrincipalName)
| extend InitiatedByApp =
tostring(InitiatedBy.app.displayName)
| extend Target =
tostring(TargetResources[0].displayName)
| project
TimeGenerated,
ActivityDisplayName,
Result,
InitiatedByUser,
InitiatedByApp,
Target,
TargetResources,
CorrelationId
| order by TimeGenerated desc
What to look for
Check who initiated the action, the target account, the role affected, the result, and whether the action matches an approved change or Privileged Identity Management activity.
Investigation tip: Activity names may differ by role-change workflow and PIM operation. Validate the event names in your environment and extend the filter to include activations and other relevant administrative actions.
5. Investigate Service Principal Sign-ins From New IP Addresses
Security objective: Find potentially unusual authentication activity involving application identities.
Service principals often authenticate without a human present and may hold access to sensitive resources. This query compares successful service principal sign-ins in the last 24 hours against a previous 13-day IP baseline.
let Baseline =
AADServicePrincipalSignInLogs
| where TimeGenerated between (ago(14d) .. ago(1d))
| where ResultType == "0"
| summarize by ServicePrincipalId, IPAddress;
AADServicePrincipalSignInLogs
| where TimeGenerated >= ago(1d)
| where ResultType == "0"
| join kind=leftanti Baseline
on ServicePrincipalId, IPAddress
| project
TimeGenerated,
ServicePrincipalName,
ServicePrincipalId,
IPAddress,
ResourceDisplayName,
ResultType
| order by TimeGenerated desc
What to look for
Review unfamiliar source IP addresses, particularly when they coincide with newly created secrets, unexpected application permissions, or unusual resource access.
Important limitation: Dynamic cloud egress IP addresses can generate legitimate results. Historical retention must cover the baseline period. A new source IP is an anomaly, not proof of compromise.
Turn KQL Queries Into a Repeatable Security Process
These five queries are useful starting points, but consistent monitoring is what makes them valuable.
- Collect the right logs. Verify diagnostic settings and retention for sign-in, audit, and service principal data.
- Review results regularly. Assign owners and review frequency based on the risks involved.
- Tune meaningful alerts. Turn high-value patterns into Microsoft Sentinel analytics rules where appropriate.
- Investigate in context. Correlate events with Conditional Access, authentication details, change approvals, and endpoint signals.
- Track improvement. Use findings to harden authentication, remove unnecessary privileges, and reduce outdated dependencies.
The goal is not to generate more alerts. It is to reduce the time between an important security event and a meaningful response.
Queries Alone Don't Show Your Full Identity Security Posture
KQL is excellent for event-level investigations. But security leaders also need to know whether MFA coverage is improving, how many risky sign-ins occurred, whether legacy authentication remains, and whether privileged identity changes are being monitored consistently.
These broader questions require a repeatable assessment process, often spanning several datasets and security controls.
Simplify Identity Security Reviews With AzureWise
The AzureWise Identity Security Assessment combines an identity security risk scorecard with practical KQL-based checks to help Azure administrators, security engineers, and consultants assess important Microsoft Entra ID risks.
It covers areas such as MFA coverage, risky sign-ins, legacy authentication, privileged role activity, service principal sign-ins, OAuth consent, and guest account visibility.
Rather than rebuilding an assessment from scratch each time, use a structured workflow to identify weaknesses, explain findings, and prioritize remediation.
Explore the AzureWise Identity Security Assessment
Final Thoughts
Collecting security logs is essential, but knowing what to look for makes those logs useful. These five Azure security KQL queries provide a foundation for investigating risky sign-ins, potential MFA policy gaps, legacy authentication, privileged role changes, and application identity anomalies.
Use them as starting points, tailor them to your environment, and integrate them into a consistent identity monitoring program.
Frequently Asked Questions
What is KQL in Azure security?
Kusto Query Language (KQL) is Microsoft's query language for analyzing data in Azure Monitor, Log Analytics, and Microsoft Sentinel. Security teams use it for security investigations, threat hunting, and alerting.
Can KQL detect compromised Microsoft Entra ID accounts?
KQL can surface indicators such as risky sign-ins and unexpected administrative actions. Investigation and corroborating evidence are needed to establish whether an account is compromised.
Which Microsoft Entra logs should security teams monitor?
Important sources include SigninLogs, AuditLogs, AADNonInteractiveUserSignInLogs, and AADServicePrincipalSignInLogs, depending on the diagnostic data enabled.
Can I use these KQL queries in Microsoft Sentinel?
Yes, if the underlying Microsoft Entra logs are available in the connected Log Analytics workspace. Production analytics rules require additional tuning and testing.
Do I need Microsoft Sentinel to run these queries?
No. You can run them in Azure Log Analytics with the appropriate data collection configured.
AzureWise helps organizations turn Microsoft Entra ID security data into practical, repeatable security assessments.
Want the Azure identity assessment already built?
AzureWise Identity Security Assessment turns Microsoft Entra ID telemetry into a weighted Security Rating, detailed findings, and prioritized remediation guidance inside Azure Monitor.
Get AzureWise — $99