KQL Search

Query Details

HomeAI ToolsDevice Query

Identity Info Find Duplicate Guest Account

Query

let DuplicateFInderUPN = IdentityInfo
| summarize arg_max(Timestamp,*) by AccountUpn
| summarize countUPN = dcount(AccountUpn) by AccountDisplayName
| where countUPN > 1;
let DuplicateDisplayName = IdentityInfo
| summarize arg_max(Timestamp,*) by AccountUpn
| join kind=inner DuplicateFInderUPN on AccountDisplayName
| order by AccountDisplayName
| project-reorder AccountDisplayName, GivenName, Surname
| project-away *1
| summarize IsAccountEnabledList = make_set(IsAccountEnabled) by AccountDisplayName
| where IsAccountEnabledList !contains "false"
| project AccountDisplayName;
IdentityInfo
| summarize arg_max(Timestamp,*) by AccountUpn
| join kind=leftsemi DuplicateDisplayName on AccountDisplayName
| project-reorder AccountDisplayName, IsAccountEnabled , AccountName
| order by AccountDisplayName,AccountUpn
| where AccountUpn contains "ext."

Explanation

This query is trying to find duplicate user accounts based on their display name and UPN (User Principal Name). It first identifies duplicate UPNs and counts how many times each display name appears with a duplicate UPN. Then, it joins this information with the original IdentityInfo table to get the display names of the duplicate accounts. It filters out any disabled accounts and projects only the display name. Finally, it joins this result with the IdentityInfo table again to get the display name, account status, and account name of the duplicate accounts that contain "ext." in their UPN. The final result is ordered by display name and UPN.

Details

Suryendu Bhattacharyya @CrookedBong (User Submission) profile picture

Suryendu Bhattacharyya @CrookedBong (User Submission)

Released: October 25, 2023

Tables

IdentityInfo

Keywords

IdentityInfo,Timestamp,AccountUpn,AccountDisplayName,countUPN,DuplicateFInderUPN,DuplicateDisplayName,GivenName,Surname,IsAccountEnabledList,IsAccountEnabled,AccountName,ext.

Operators

summarizearg_maxbycountUPNdcountwherejoinkindorder byproject-reorderproject-awaymake_set!containsprojectleftsemicontains

Actions