KQL Search

Query Details

HomeAI ToolsDevice Query

MDI Identity Password Security Posture Assessment

Query

# Active Directory - Password Security Posture Assessment

![KQL](https://img.shields.io/badge/language-KQL-blue.svg)
![Status: Testing](https://img.shields.io/badge/status-testing-blue.svg)

## Query Information

### Description

iThis query leverages the `IdentityAccountInfo` and `IdentityInfo` tables in Microsoft Defender XDR to identify Active Directory accounts with outdated passwords and assess their security risk. By combining key attributes such as `Tags`, `LastPasswordChangeTime`, `AccountStatus`, and `UserAccountControl`, the query helps security teams:

- **Identify stale passwords**: Detect accounts that haven't changed their password in an extended period, calculated in both days and years
- **Assess sensitivity**: Determine whether accounts are tagged as sensitive or have elevated privileges
- **Review password policies**: Identify accounts with `PasswordNeverExpires` or `PasswordNotRequired` flags set
- **Analyze account status**: Focus on enabled accounts that may pose active security risks

This query is particularly useful for addressing **Microsoft Defender for Identity security posture assessments**, including:

- Built-in Active Directory Guest account is enabled
- Change password for krbtgt account
- Change password of built-in domain Administrator account
- Rotate password for Microsoft Entra Connect AD DS Connector account
- Remove unsafe permissions on sensitive Microsoft Entra Connect accounts

The second query variant provides focused results by filtering specifically for high-risk built-in accounts (Administrator, Guest, krbtgt) and Microsoft Entra Connect synchronization accounts (MSOL_, AAD_, ADSync).

#### References

- [IdentityAccountInfo](https://learn.microsoft.com/en-us/defender-xdr/advanced-hunting-identityaccountinfo-table)
- [IdentityInfo](https://learn.microsoft.com/en-us/defender-xdr/advanced-hunting-identityinfo-table)
- [Hybrid security posture assessments](https://learn.microsoft.com/en-us/defender-for-identity/security-posture-assessments/hybrid-security)
- [Identity infrastructure security assessments](https://learn.microsoft.com/en-us/defender-for-identity/security-posture-assessments/identity-infrastructure)
- [Accounts security posture assessments](https://learn.microsoft.com/en-us/defender-for-identity/security-posture-assessments/accounts)

### Author

- **Alex Verboon**

## Defender XDR

```kql
let accountinfo = IdentityAccountInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated,*) by IdentityId
| extend DaysSinceLastPasswordChange =
    iff(isnull(LastPasswordChangeTime), int(null),
        datetime_diff('day', now(), LastPasswordChangeTime))
| extend YearsSinceLastPasswordChange =
    iff(isnull(LastPasswordChangeTime), int(null),
        datetime_diff('year', now(), LastPasswordChangeTime))   
| extend Sensitive = array_index_of(Tags, "Sensitive")   != -1
| extend SensitiveLabel = iff(Sensitive == 1, "🟥 Sensitive", "⬜ Not Sensitive")
| project IdentityId,AccountUpn, AccountStatus, LastPasswordChangeTime,DaysSinceLastPasswordChange,YearsSinceLastPasswordChange, Sid, Sensitive, SensitiveLabel;
let IdInfo = IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated,*) by IdentityId
| extend PasswordNeverExpires = array_index_of(UserAccountControl, "PasswordNeverExpires")   != -1,
         PasswordNotRequired = array_index_of(UserAccountControl, "PasswordNotRequired")   != -1
| extend OUPath = extract(@"CN=[^,]+,(.*)", 1, DistinguishedName)
| project IdentityId,AccountName, AccountDomain, AccountDisplayName, OnPremSid, OnPremObjectId, AccountUpn, PasswordNeverExpires, PasswordNotRequired,Type, OUPath;
IdInfo
| join kind=leftouter (accountinfo)
on $left. IdentityId == $right. IdentityId
| project IdentityId, AccountName, AccountStatus,AccountDomain, AccountDisplayName,AccountUpn,Sensitive,SensitiveLabel,LastPasswordChangeTime, DaysSinceLastPasswordChange, YearsSinceLastPasswordChange, PasswordNeverExpires, PasswordNotRequired,Type, OUPath
| sort by DaysSinceLastPasswordChange desc 
| where AccountStatus != @"Disabled"
```

Visualize the result in Year buckets (add the kql below to the above query)

```kql
| where isnotnull(LastPasswordChangeTime)
| extend PasswordAgeBucket =
    case(
        DaysSinceLastPasswordChange <= 365, "0-1 years",
        DaysSinceLastPasswordChange <= 2*365, "1-2 years",
        DaysSinceLastPasswordChange <= 5*365, "2-5 years",
        DaysSinceLastPasswordChange <= 10*365, "5-10 years",
        "10+ years")
| summarize Accounts = dcount(IdentityId) by PasswordAgeBucket
| sort by Accounts desc
| render columnchart  
```
Filter the results by Built-in Administrator, Guest accounts, krbgt and Entra ID Synch accounts.

```kql
let accountinfo = IdentityAccountInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated,*) by IdentityId
| extend DaysSinceLastPasswordChange =
    iff(isnull(LastPasswordChangeTime), int(null),
        datetime_diff('day', now(), LastPasswordChangeTime))
| extend YearsSinceLastPasswordChange =
    iff(isnull(LastPasswordChangeTime), int(null),
        datetime_diff('year', now(), LastPasswordChangeTime))   
| extend Sensitive = array_index_of(Tags, "Sensitive")   != -1
| extend SensitiveLabel = iff(Sensitive == 1, "🟥 Sensitive", "⬜ Not Sensitive")
| project IdentityId,AccountUpn, AccountStatus, LastPasswordChangeTime,DaysSinceLastPasswordChange,YearsSinceLastPasswordChange, Sid, Sensitive, SensitiveLabel;
let IdInfo = IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated,*) by IdentityId
| where isnotempty( AccountName)
| extend PasswordNeverExpires = array_index_of(UserAccountControl, "PasswordNeverExpires")   != -1,
         PasswordNotRequired = array_index_of(UserAccountControl, "PasswordNotRequired")   != -1
| extend OUPath = extract(@"CN=[^,]+,(.*)", 1, DistinguishedName)
| project IdentityId,AccountName, AccountDomain, AccountDisplayName, OnPremSid, OnPremObjectId, AccountUpn, PasswordNeverExpires, PasswordNotRequired,Type, OUPath;
IdInfo
| join kind=leftouter (accountinfo)
on $left. IdentityId == $right. IdentityId
| project IdentityId, AccountName, AccountStatus,AccountDomain, AccountDisplayName,AccountUpn,Sensitive,SensitiveLabel,LastPasswordChangeTime, DaysSinceLastPasswordChange, YearsSinceLastPasswordChange, PasswordNeverExpires, PasswordNotRequired,Type, OUPath
| sort by DaysSinceLastPasswordChange desc 
| where tolower(AccountName) in ("krbtgt", "administrator","guest","admin") or tolower(AccountName) startswith "msol_"
                                                                    or tolower(AccountName) startswith "AAD_"
                                                                    or tolower(AccountName) startswith "ADSync"
```

Explanation

This KQL query is designed to assess the security posture of Active Directory accounts by examining password-related attributes. Here's a simplified breakdown of what the query does:

  1. Data Sources: The query uses two tables from Microsoft Defender XDR: IdentityAccountInfo and IdentityInfo.

  2. Objective: It aims to identify accounts with outdated passwords and assess their security risks by looking at various attributes.

  3. Key Features:

    • Stale Passwords: It calculates how long it has been since each account's password was last changed, both in days and years.
    • Account Sensitivity: It checks if accounts are marked as sensitive or have elevated privileges.
    • Password Policies: It identifies accounts with settings like PasswordNeverExpires or PasswordNotRequired.
    • Account Status: It focuses on enabled accounts that might pose security risks.

  4. Use Cases: This query is useful for security assessments in Microsoft Defender for Identity, such as ensuring critical accounts like the built-in Administrator, Guest, and krbtgt accounts have secure password practices.

  5. Visualization: The query can be extended to visualize accounts based on how long ago their passwords were changed, categorizing them into different age buckets (e.g., 0-1 years, 1-2 years, etc.).

  6. Filtering: A variant of the query specifically targets high-risk accounts, such as built-in accounts and Microsoft Entra Connect synchronization accounts, to provide focused results.

Overall, this query helps security teams identify and mitigate potential risks associated with outdated passwords and improper account configurations in Active Directory.

Details

Alex Verboon profile picture

Alex Verboon

Released: December 6, 2025

Tables

IdentityAccountInfoIdentityInfo

Keywords

ActiveDirectoryAccountsPasswordSecurityRiskMicrosoftDefenderIdentitySensitivePrivilegesPoliciesStatusAdministratorGuestKrbtgtEntraConnectSynchronization

Operators

letwhereagosummarizearg_maxbyextendiffisnulldatetime_diffnowarray_index_ofprojectjoinkindleftouteron==sortdesc!=isnotnullcase<=dcountrendercolumnchartisnotemptytolowerinstartswith

Actions