How to return properties from a list in Powershell for AD Users

Question

Apologies for the basic question, I like to dabble in Powershell but as I don't use it frequently enough I have forgotten a lot!

I have a csv list of users' employee numbers (employee.csv), which are in AD under "extensionAttribute15"

I would like, for each employee number in the list, to return each accounts SamAccountName and what their AD group membership is, into an Excel sheet

So far I have

$employee = Import-csv .\Downloads\employee.csv

$employee | ForEach-Object {
Get-ADUser -Credential $Credentials -Filter "extensionAttribute15 -like '*$($_.extensionAttribute15)*'" -Properties * | Select-Object extensionAttribute15, Name, emailAddress, samAccountName, Description, MemberOf  | Export-Csv .\Downloads\new_employee.csv
}

which works, but it doesn't expand on the AD Group membership...

Thanks in advance!

Answer 1

Consider how you want the results to appear. Should all the groups be in the same cell, separated by a new line? Separated by a delimiter? Each group is displayed in a new row/column?

Here's a basic way to display each group in the same cell, separated by a new line

$employee | ForEach-Object {
    Get-ADUser -Credential $Credentials -Filter "extensionAttribute15 -like '*$($_.extensionAttribute15)*'" -Properties * | 
        Select-Object extensionAttribute15, Name, emailAddress, samAccountName, Description, @{name="MemberOf";expression={$_.MemberOf | Out-String}}} | 
        Export-Csv .\Downloads\new_employee.csv

Here's a link

Answer 2

As commented, Get-ADUser already returns objects with these properties:
DistinguishedName, Enabled, GivenName, Name, ObjectClass, ObjectGUID, SamAccountName, SID, Surname, UserPrincipalName.

In your case you also want these properties to be returned: extensionAttribute15, EmailAddress, Description and MemberOf

A flaw in your code is that you Export-Csv inside the loop, so on each iteration it will be overwritten. You can avoid that by either add switch -Append there, but then you are creating a lot of disk-write actions.

The best way is to pipe everything to Export-Csv at the end of the ForEach-Object loop, so the file needs to be written only once.

$employee = Import-csv -Path '.\Downloads\employee.csv'

# an array to hold the extra properties Get-ADUser should include in the results
$propertiesToInclude = 'extensionAttribute15', 'EmailAddress', 'Description', 'MemberOf'
# now loop over the items of the CSV, and pipe the complete collection at the end to Export-Csv
$employee | ForEach-Object {
    Get-ADUser -Credential $Credentials -Filter "extensionAttribute15 -like '*$($_.extensionAttribute15)*'" -Properties $propertiesToInclude |
    Select-Object extensionAttribute15, Name, emailAddress, samAccountName, Description, MemberOf
} | Export-Csv -Path '.\Downloads\new_employee.csv' -NoTypeInformation

---

You can combine the values of the MemberOf property into one column if you like by changing the Select-Object line into

Select-Object extensionAttribute15, Name, emailAddress, samAccountName, Description, 
              @{Name = 'MemberOf'; Expression = { $_.MemberOf -join '; ' }} -ExcludeProperty MemberOf

Answer 3

Json supports arrays and works well for this. Convertfrom-json will convert memberof back into an array.

get-aduser js -property memberof,extensionAttribute15,emailAddress,Description |
select name,samaccountname,memberof,extensionAttribute15,emailAddress,Description |
Convertto-json | set-content new_employee.json

{
    "extensionAttribute15":  null,
    "Name":  "js",
    "emailAddress":  "[email protected]",
    "samAccountName":  "js",
    "Description":  "SYSTEM ADMINISTRATOR",
    "MemberOf":  [
                     "CN=group1,OU=admins,DC=stackoverflow,DC=com",
                     "CN=group2,OU=admins,DC=stackoverflow,DC=com",
                     "CN=group3,OU=admins,DC=stackoverflow,DC=com"
                 ]
}