Filter user type from OU in Active Directory Output

PowerShell Help
1

Team,

I’m using below command to get Service account DistinguishedName.

Script:
Get-ADUser SQLSRVCACCNT | Select DistinguishedName

Output:
CN=SQL Service,OU=Service Accounts,OU=_Special Accounts,DC=corp,DC=cdw,DC=com

I want Just below output which is second part of OU (OU=Service Accounts).
Service Accounts

Can you please let me know script to get second part of OU alone and not entire DistinguishedName?

2

Since active directory does not have an attribute like parent or directory you will have to use either the DistinguishedName or the CanonicalName and cut the desired pieces from it.

Assumed your Common Names do not have commas or slashes in them you could use something like this:

Get-ADUser SQLSRVCACCNT  -Properties CanonicalName | 
ForEach-Object {
    [PSCustomObject]@{
        DistinguishedNameParent = ($_.DistinguishedName -split ',')[1]
        CanonicalNameParent     = ($_.CanonicalName -split '\/')[-2]
    }
}
1 Like
3

that is nice!

lol this is what i was using for work.

[regex]$rx = ".*\/([^\/]+)\/"
Get-ADUser testuser -Properties canonicalname | 
select Name, @{n='OU';e={$rx.Matches($_.canonicalname).groups[1].value}}

will use your method next time :smiley:

4

Thanks.

As long as it does the job it’s ok. :wink:

I always try to code following the KISS principle.

1 Like
5

@Olaf - Thank you so very much. This worked like Charm :slight_smile:

6

@Olaf

When I loop users a list of users and send output to text file and it shows as below.
Can you please help?

Current Incorrect Output
ServerName,SQLSRVCACCNT,@{CanonicalNameParent=Service Accounts}

Anticipated Output
ServerName,SQLSRVCACCNT,Service Accounts

Code:

cls

$ErrorActionPreference = ‘SilentlyContinue’  
$myArray = @() 
$Server_list = Get-Content 'C:\scripts\servers.txt' 

foreach ($Server in $Server_list)
{

# Get the list of individual users (NOT GROUPS) who has access to server
$sqlcmd1 = @" 

SELECT  name
FROM sys.server_principals 
WHERE TYPE IN ('U')
ORDER BY name, type_desc 

"@

Write-host "Collecting Logins for the server: " -f Yellow -NoNewline; write-host  $Server -f Cyan  
$myarray1 = Invoke-Sqlcmd -serverInstance $Server -Database master  -Query $sqlCmd1  # Getting list of individual users to $myarray1

 Foreach ($user in $myarray1.Login)

 {

 $myarray4 = Get-ADUser -Identity $user | where-object {($_.distinguishedname -like "*OU=_People*") -OR ($_.distinguishedname -like "*OU=_Administrative*") -OR ($_.distinguishedname -like "*OU=IT*")  -OR ($_.distinguishedname -like "*OU=_Special Accounts*") }  

 $usertype1 = Get-ADUser $user  -Properties CanonicalName , DistinguishedName | ForEach-Object {[PSCustomObject]@{CanonicalNameParent = ($_.CanonicalName -split '\/')[-2] }} 

 Add-Content -Path C:\login\Access_List_Test.txt -Value  $Server  -NoNewline
 Add-Content -Path C:\login\Access_List_Test.txt -Value   ','   -NoNewline 
 Add-Content -Path C:\login\Access_List_Test.txt -Value $myarray4.name  -NoNewline 
 Add-Content -Path C:\login\Access_List_Test.txt -Value   ',' -NoNewline
 Add-Content -Path C:\login\Access_List_Test.txt -Value  $usertype1

}
}
7

Something like this should work.

$Server_list = Get-Content 'C:\scripts\servers.txt' 
$sqlcmd1 = @'
SELECT  name
FROM sys.server_principals 
WHERE TYPE IN ('U')
ORDER BY name, type_desc 
'@

$Result=
foreach ($Server in $Server_list) {
    $myarray1 = Invoke-Sqlcmd -serverInstance $Server -Database master  -Query $sqlCmd1
    Foreach ($user in $myarray1.Login) {
        $ADUser = Get-ADUser -Identity $user -Properties CanonicalName
        [PSCustomObject]@{
            ComputerName = $Server
            UserName     = $ADUser.Name
            ParentOU     = ($ADUser.CanonicalName -split '\/')[-2]
        }
    }
}
$Result

I’d recommend to output it as CSV or any other kind of structured data. This way you can easily read it again with PowerShell and process it as needed.

8

Great, It’s Workingggg :smiley:
Thank you for all your recommendations, @Olaf.

9

Hi @Olaf

Now I’m trying to get recursive list of users from AD group using same code format but not working.
Not sure where I’m going wrong? Can you please help?

Incorrect Output

Servername System.Object

Expected Output
Servername username ParentOU

Code

$Server_list = Get-Content 'C:\scripts\servers.txt'
$ErrorActionPreference = ‘SilentlyContinue’


$sqlcmd2 =  @"

SELECT  name
FROM sys.server_principals 
WHERE TYPE IN ('G')
ORDER BY name, type_desc

"@

$Result2=
foreach ($Server in $Server_list) {
    $myarray2 = Invoke-Sqlcmd -serverInstance $Server -Database master  -Query $sqlCmd2
    foreach ($group_name in $myarray2.Login) {
        $ADGroup = Get-ADGroupMember -Identity $group_name -Recursive 
        foreach ($name in $ADGroup.name) {
        $ADUser2 = Get-ADUser -Identity $name -Properties CanonicalName
                    
        [PSCustomObject]@{
            ComputerName = $Server
            UserName     = $ADUser2.Name
            ParentOU     = ($ADUser2.CanonicalName -split '\/')[-2]
           }
        }
    }
}
10

You’re lacking the fundamentals. I’d recommend to do a big step back and start with learning the very basics of PowerShell first. It will save you from a lot of wasted time and frustrations.

It is not the purpose of this forum to iteratively and interactively develop your scripts step by step for you.

Since Get-ADGroupMember does not return a CanonicalName like it Get-ADUser does you cannot use the same method like before. But I actually showed 2 ways to get the parent OU of a given account. :wink:

Get-ADGroupMember -Identity 'GroupName' -Recursive | 
ForEach-Object {
    [PSCustomObject]@{
        User     = $_.Name
        ParentOU = ($_.DistinguishedName -split ',')[1]
    }
}

Again … that only works reliably when your CommonNames do not have any commas. :point_up_2:t4:

11

Sure, Thank you for all your recommendations.