KQL Search

Query Details

HomeAI ToolsDevice Query

AD Extract User OU

Query

# Active Directory - Extract Account OU

## Query Information

### Description

Use the below query example to extract the Accounts AD organizational unit from the OnPremisesDistinguishedName

First we define the regex pattern

```kql
let OUPattern = @"^(CN=[^,]+,)?(.+)$";
```

and then use this regex to extract the OU

```kql
| extend OU = extract(OUPattern, 2, OnPremisesDistinguishedName)
```

#### References

### Microsoft Sentinel

```kql
let ADGroups = dynamic(['LAPS_Global_Workplace_Reset', 'LAPS_Servers_Reset']);
let OUPattern = @"^(CN=[^,]+,)?(.+)$";
IdentityInfo
| where TimeGenerated > ago(90d)
| summarize arg_max(TimeGenerated, *) by AccountName
| where GroupMembership has_any (ADGroups)
| extend OU = extract(OUPattern, 2, OnPremisesDistinguishedName)
| project AccountUPN, AccountName, OnPremisesDistinguishedName, OU, GroupMembership 
//| summarize count() by OU

```

Explanation

The query is extracting the organizational unit (OU) of user accounts from the Active Directory. It uses a regular expression pattern to extract the OU from the OnPremisesDistinguishedName attribute. The query also filters the results based on certain group memberships and includes additional information such as the account name, UPN, and group membership.

Details

Alex Verboon profile picture

Alex Verboon

Released: June 4, 2023

Tables

IdentityInfo

Keywords

ActiveDirectory,Extract,Account,OU,OnPremisesDistinguishedName,regex,extend,extract,OUPattern,IdentityInfo,TimeGenerated,AccountName,GroupMembership,ADGroups,AccountUPN

Operators

extendextractwheresummarizebyhas_anyproject

Actions