Applied KQL

Helping others get things done using Kusto Query Language (KQL).

KQL Query: Logon Failure Reason analysis

by

Artistic interpretation of a KQL query which returns a grouping of Logon Failure reasons by Application - Copilot Designer
Artistic interpretation of a KQL query which returns a grouping of Logon Failure reasons by Application – Copilot Designer

Investigate which Microsoft applications are experiencing the most logon failures and the reasons behind those failures.

Prerequisites for using this KQL query

The Microsoft Defender for Identity Domain controller Sensor should be installed on your domain controllers if you want Active Directory logon failures. Perform query using Microsoft Defender > Hunting > Advanced hunting.

KQL query to get logon failure reason counts, grouped by Application and FailureReason

IdentityLogonEvents
| where ActionType == "LogonFailed"
| summarize count() by  Application, FailureReason
| project Application, FailureReason, count_
| sort by count_ desc

Deconstructing the KQL query

Read on to understand HOW the query above works.

The goal of this query is to start analysis of which applications are experiencing the most logon failures and the reasons behind those failures. By sorting the results, we’ll identify the interesting areas for further investigation.

IdentityLogonEvents

This is the Log Analytics table we’re querying. It contains information about authentication activities related to both on-premises Active Directory (captured by Microsoft Defender for Identity) and Microsoft online services (captured by Microsoft Defender for Cloud Apps).

| where ActionType == "LogonFailed"

Since we’re only interested in failed logon events, we start by filtering the events to include only those where the ActionType is “LogonFailed”.

| summarize count() by Application, FailureReason

Next, we summarize the data. We group the events by two fields:

  • Application (which application triggered the logon attempt) and
  • FailureReason (the reason for the logon failure).

The count() function calculates the number of occurrences for each combination of application and failure reason.

| project Application, FailureReason, count_

After summarizing, we use project to select specific columns to display in the final result. We choose to show the Application, FailureReason, and a custom column called count_ (which represents the count of logon failures for the given Application and FailureReason combination).

| sort by count_ desc

Finally, we sort the results in descending order based on the count_ column. This way, we’ll see the applications with the highest number of failed logon attempts at the top.

FailureReason: some possible values

We couldn’t find a definitive list of possible values for FailureReason, but running the query above for a duration of log activity (e.g., last 30 days) should give you a good idea of what potential values you see in your environment. Here are some examples:

Active Directory

  • AccountDisabled
  • AccountExpired
  • AccountLocked
  • Generic
  • InvalidCredentials
  • InvalidLogonHours
  • OldPassword
  • PasswordExpired
  • PasswordMustChange
  • UnknownUser
  • WrongPassword

Microsoft 365

  • Error validating credentials due to invalid username or password.
  • General failure
  • Strong Authentication is required.
  • The account is locked, you’ve tried to sign in too many times with an incorrect user ID or password.
  • The password is expired.
  • The reply URL specified in the request does not match the reply URLs configured for the application.
  • The session is not valid due to password expiration or recent password change.
  • The user or administrator has not consented to use the application. Send an interactive authorization request for this user and resource.
  • This occurred due to Keep me signed in interrupt when the user was signing in.