KQL Query Pass Hash Sync Status

Hi Wishwahvijaya​ ,

Quick reality check before the KQL: Password Hash Sync is a tenant-level setting in Entra ID, not a per-user attribute. The Copilot-generated queries probably hallucinated tables because there is no table in Sentinel or Defender XDR that exposes a "PHS status per user" column. That data simply does not live in Log Analytics or the Advanced Hunting schema.

What you can derive with KQL is whether a user is cloud-only, hybrid-synced, or on-premises sourced. From there, combined with the tenant-level PHS configuration, you can infer which users have their hash synced (assuming PHS is enabled at the tenant and the user sits in a managed, not federated, domain).

The KQL part (Defender XDR Advanced Hunting / Sentinel):

IdentityInfo
| summarize arg_max(Timestamp, *) by AccountObjectId
| where isnotempty(AccountUpn)
| project 
    AccountUpn,
    AccountDisplayName,
    IdentityEnvironment,      // CloudOnly, Hybrid, On-premises
    SourceProviders,          // ActiveDirectory, EntraID, Okta
    OnPremSid,
    OnPremObjectId,
    IsAccountEnabled
| extend LikelyHashSynced = case(
    IdentityEnvironment == "Hybrid" and isnotempty(OnPremSid), "Likely (verify tenant PHS + domain federation)",
    IdentityEnvironment == "CloudOnly", "No (cloud-only)",
    IdentityEnvironment == "On-premises", "Depends on sync scope",
    "Unknown"
  )
| order by IdentityEnvironment, AccountUpn

This gives you the population that could have a synced hash. To turn "could" into "yes", you need two more pieces from outside KQL:

1. Tenant-level PHS status via Graph: GET https://graph.microsoft.com/v1.0/directory/onPremisesSynchronization and check features.passwordSyncEnabled. PowerShell equivalent: Get-MgDirectoryOnPremiseSynchronization | Select-Object -ExpandProperty Features.
2. Domain authentication type per user UPN suffix (managed vs federated): Get-MgDomain shows AuthenticationType. Federated domains do not get PHS even if the tenant has it enabled, unless you have PHS configured as fallback for staged rollout.

If you actually need this as ongoing data in Sentinel:

Build a small Logic App that runs daily, pulls the Graph endpoints above, and pushes the results into a custom table via the Logs Ingestion API. Then your KQL becomes a real join:

IdentityInfo
| summarize arg_max(Timestamp, *) by AccountObjectId
| join kind=leftouter (DomainSyncStatus_CL) on $left.UpnSuffix == $right.DomainName
| where TenantPHSEnabled == true and AuthenticationType == "Managed" and IdentityEnvironment == "Hybrid"
| project AccountUpn, IdentityEnvironment, AuthenticationType, HashSyncEffective = true

That is the cleanest model I know of. There is no shortcut where Log Analytics magically exposes per-user PHS state, because Microsoft does not emit that as a log signal.

Two things to watch out for if you go this route:

• IdentityInfo requires Defender for Identity or Sentinel UEBA. Without those, the table is empty or missing.
• The IdentityInfo schema was unified in mid-2025, so older queries you find online may use deprecated field names like IsAccountEnabled vs newer ones.

Hope that helps - happy to dig deeper if you have a specific use case (audit, security review, migration planning).