CODEWITHSUNDEEP
sql-serverpowershellactive-directory
Racer SQL
Racer SQL

Reputation: 217

GET-ADUSER into .CSV file, but distinguishedName in a single column

After hours of tries and reading almost all posts here, I could not find the solution to what I need.

I would like to retrieve adusers but only specific fields. Something like this:

Area | Enabled | Name | userPrincipalName(email)

The problem is, the field DistinguishedName has everything I need but it's separated into different columns when I export it to .CSV.

enter image description here

I could only read this line and separate it in the columns I need, but then I have another problem that is there's some users with more than 2,3,4 OU.

is there a way to read, like only one OU ( the first one is the one I need, because it says IT, EXPEDITION , ETC) or at least separate CN as a field, OU as another field, and then name, email ...?

I create this code to run on PowerShell:

Get-ADUser -Filter * -Properties DistinguishedName,SamAccountName,DisplayName,EmailAddress,OfficePhone | Select-Object DistinguishedName,EmailAddress,OfficePhone,DisplayName,SamAccountName | export-csv -path path\to\adusers.csv -NoTypeInformation -Encoding "UTF8"

and then I import it to SQL Server.

Upvotes: 0

Views: 1987

Answers (1)

Jonathan Waring
Jonathan Waring

Reputation: 241

The way I would use this is to create a custom property in the select-object part of your code. eg:

Get-ADUser -Filter * -Properties DistinguishedName,SamAccountName,DisplayName,EmailAddress,OfficePhone | Select-Object DistinguishedName,EmailAddress,OfficePhone,DisplayName,SamAccountName

would become

Get-ADUser -Filter * -Properties DistinguishedName,SamAccountName,DisplayName,EmailAddress,OfficePhone | Select-Object @{Label='DistinguishedName';expression={$_.DistinguishedName.Split(',')[1]}},EmailAddress,OfficePhone,DisplayName,SamAccountName

@{Label = 'DistinguishedName';Expression={$_.Distinguishedname.split(',')[1]}} this is how you create a custom property within the pipeline, so here we are saying I want a custom property and the Label is the name of the property, the expression is what we want the property to be, so in this case we want the current distinguishedname in the pipelin $_.DistinguishedName but then we want to split this on , as that is what separates the parts of the DistinguishedName from each other so we use the .split(',') method which you enter in the character you want to split on. Now based on your question you are only ever interested in the 1st OU in the distinguishedname field so we select the 2nd object in the array created by .split(',') using [1] the first object in the array will be the CN= entry. This will give you something like below. enter image description here

Which you can then export to CSV

Link to further info on custom properties https://4sysops.com/archives/add-a-calculated-property-with-select-object-in-powershell/

Upvotes: 2

Related Questions