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

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!

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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"
                 ]
}