CODEWITHSUNDEEP
excelpowershellactive-directorydistinguishedname
verfluecht
verfluecht

Reputation: 513

How can I split AD information in Powershell into a excel document?

I am a Powershell starter. I have been trying to create a script, that makes an Excel file with some AD information including the DistinguishedName. My script looks like this:

$dn = Get-ADUser -Filter * -SearchBase "OU=Users,OU=Ch01,OU=EU,DC=corp,DC=ads" | select DistinguishedName,SamAccountName,name |export-csv C:\temp\test1.csv -Delimiter ";"

An example of what I get (Note: | means new cell in Excel):

CN=Testuser\, Verfluecht,OU=Users,OU=Ch01,OU=EU,DC=corp,DC=ads | vtestuser | Testuser, Verfluecht

But in order to group the paths in excel, I need it without the CN (CN=Testuser\, Verfluecht,) So that it would look like this:

OU=Users,OU=Ch01,OU=EU,DC=corp,DC=ads | vtestuser | Testuser, Verfluecht

How can I do this?

I tried many things such as .substring and replace, but I could not get it done.

Upvotes: 1

Views: 1104

Answers (1)

Tim Haintz
Tim Haintz

Reputation: 676

Using this link and a calculated property, it should just drop the first part of the distinguishedname and be left with the parts you need.

Get-ADUser -Filter * -SearchBase "OU=Users,OU=Ch01,OU=EU,DC=corp,DC=ads" | 
Select-Object @{Name="DistinguishedName";Expression={$_.distinguishedname | ForEach-Object {$_ -replace '^.+?(?<!\\),',''}}},samaccountname,name |
Export-Csv C:\temp\test1.csv -Delimiter ";"

On my test environment, I get the output below (without piping it to Export-Csv).

Get-ADUser -Filter * | Select-Object @{Name="DistinguishedName";Expression={$_.distinguishedname | ForEach-Object {$_ -replace '^.+?(?<!\\),',''}}},samaccountname,name

DistinguishedName            samaccountname name          
-----------------            -------------- ----          
CN=Users,DC=timhaintz,DC=com Administrator  Administrator 
CN=Users,DC=timhaintz,DC=com Guest          Guest         
CN=Users,DC=timhaintz,DC=com DefaultAccount DefaultAccount
CN=Users,DC=timhaintz,DC=com krbtgt         krbtgt

Thanks, Tim.

Upvotes: 1

Related Questions