CSV of AD Memberships per Department

PowerShell Help
1

OK, I thought this would be an easy one, but my PSfu is weak and I need some assistance.
All I am trying to do is get a csv output of all our AD user’s departments and their corresponding group memberships.
I know how to query users for all the departments, and I know how to query for each users group memberships. Im just getting stuck putting it all together.

Here is what I have so far.

’
$OUpath = ‘OU=people,DC=domain,DC=com’
$Departments = Get-ADUser -Filter * -SearchBase $OUpath -Properties * | Select-object UserPrincipleName,Department
Get-adprincipalgroupmembership | Foreach-Object ($UserPrincipleName in $Departments)
Export-csv c:\scripts\department_memberships.csv
’

The result I am trying to get is a table like this:

Department1 Department2 Department3
Domain Users Domain Users Domain Users
Group 1 Group 3 Group 2
Group 2 Group 4 Group 4
Group 3 Group 5 Group 6
etc etc etc

Thanks in advance for the help!

2

Michael,
Welcome to the forum. :wave:t3:

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org 1 <---- Click :point_up_2:t4: :wink:

That won’t work. You cannot use the values of one single property as table headers.

What for do you actually want to use the data you’re after?

Did you write this code? Did you try to run this code?

And just to mention it at least once: Using -Properties *, although you only need the property Department besides the default return set, is not a good idea. This puts much more stress to your AD than necessary.

3

Hi Olaf,
Thanks for the tip on code formatting, but I actually do not see a <> button in the message editor.

The business case is that we don’t have a baseline membership list per department for when we create new users. Various people in the same department will have different group memberships. Its a mess. So, I am working on evaluating all the different departments to see what memberships they have and from that build a baseline group membership template for each department. Probably TMI, but you did ask.

The cmdlets for the variables work, but I dont think I have the foreach correct.
Also, I cant remove -Properties * because then the cmdlet will not be able to see the Department attribute

4

image

That’s OK. At least it tells me that you’re not bound to this particular format. :wink:

hmmm … no comment on that … :smirk:

Instead of -Properties * what queries ALL available attributes you should use -Properties Department. What’s way less stressful for your DC. :point_up_2:t3:

Here’s how I’d approach it. First we need the list of users with their departments and since we already query the users we can query their group memberships as well.

$SearchBase = 'OU=people,DC=domain,DC=com'
$UserList = Get-ADUser -Filter * -SearchBase $SearchBase -Properties MemberOF, Department

Since the MemberOf attribute contains a list of DistinguishedNames what’s ugly and hard to read I’d like to query the names of the groups. That’s easier on the eyes I think. And to be able to use this as a lookup table we save the output in a hashtable.

$ADGroupLookupTable = @{}
$UserList.MemberOf | 
Select-Object -Unique |
ForEach-Object {
   $ADGroupLookupTable.add( $_ , (Get-ADGroup -Identity $_).Name )
}

Now we use both lists and build our result list

$ResultList =
$UserList | 
ForEach-Object {
    foreach ($MemberShip in $_.MemberOf) {
        [PSCustomObject]@{
            UserName   = $_.Name
            Department = $_.Department
            Group      = $ADGroupLookupTable[$MemberShip]
        }
    }
}

Now you can group this list for the department and you have the info you wanted, right? :wink:

$ResultList |
Group-Object -Property Department | 
ForEach-Object {
    [PSCustomObject]@{
        Name      = $_.Name
        GroupList = $_.Group.Group -join ','
    }
}
5

Thanks Olaf! This is great.
Yeah I do not know why I cant see that button under the message editor. Permissions perhaps?

image
image746×239 5.19 KB

Thank you for all of the code! Incredibly helpful!
When I run the entire script, it works, but displays the output in the PS window. I added an Export-CSV (with the path) to the end, but PS came back and reported this:

cmdlet Export-Csv at command pipeline position 1
Supply values for the following parameters:
InputObject:

I just pushed Enter, and it looks like it worked, however when I opened the CSV it says this:
#TYPE System.String
Length
0

Is there a way I can export the results to csv so I can work with the data in Excel?

6

Please do not describe the code - share it. If you get error messages you should share them completely - formatted as code as well.

You can format code manually by adding 3 consecutive backticks on two empty lines and the code in between … like this:

image

7

OK sure thing.

Here is what I ran:

PS C:\> $SearchBase = 'OU=People,DC=domain,DC=com'
$UserList = Get-ADUser -Filter * -SearchBase $SearchBase -Properties MemberOF, Department
$ExportPath = 'C:\Temp\department_memberships1.csv'

$ADGroupLookupTable = @{}
$UserList.MemberOf | 
Select-Object -Unique |
ForEach-Object {
   $ADGroupLookupTable.add( $_ , (Get-ADGroup -Identity $_).Name )
}

$ResultList =
$UserList | 
ForEach-Object {
    foreach ($MemberShip in $_.MemberOf) {
        [PSCustomObject]@{
            UserName   = $_.Name
            Department = $_.Department
            Group      = $ADGroupLookupTable[$MemberShip]
        }
    }
}

$ResultList |
Group-Object -Property Department | 
ForEach-Object {
    [PSCustomObject]@{
        Name      = $_.Name
        GroupList = $_.Group.Group -join ','
    }
}
Export-Csv $ExportPath

Here is the message I received:

cmdlet Export-Csv at command pipeline position 1
Supply values for the following parameters:
InputObject: (I pushed Enter here)

This is the result:

Name GroupList

55,717,Sales Japan MS_CMS,License-Networld,JPN_Sales_Assistant,JPN_JMC,SP_Legal_Contracts,SP_Legal_Forms,All_Tokyo_Users…
57,253,Customer Engineering - Japan PTKK-HitachiOmika,Lenovo_Spec_Acc,PTKK-TOSHIBA,PTKK-Panasonic,PTKK-NEC,PPMs_Update_Request,Lenovo_Glo…
99,911,Finance PTL-All,Accounting,Admin,Tokyo_Users,allsubscribers6dfc3549,Legal,Americas_Operations,SP_Legal_Contra…
55,773,FAE Japan ServerTiger Team,ServerTigerTeam,Lenovo_Global,JPSupport,JPN_Managers,JPN_JMC,Transition,SP_Legal_Con…
PTL-All,NuBIOS,PTL-All,PTL-All,PTL-All,NuBIOS,PTL-All,allsubscribers6dfc3549,PTL Email Control Access…
57,251,Customer Engineering - Korea Korea_Users,All_Korea_Users,SCT4,SCT,PTL-All,KOR_Users,Visual Studio Licensed Users,KOR_Engs,Korea_CE…
55,774,FAE APAC Korea_Users,NuBIOS-Integration,QA_Korea,BP_Group,SP_Legal_Contracts,SP_Legal_Forms,All_Korea_Users,SC…
55,709,Sales APAC PTL-All,KOR_Users,allsubscribers6dfc3549,SP_Legal_Contracts,SP_Legal_Forms,

(this is a concatenated list as the actual list is very long)

PS C:>

Here is also a screenshot of the results in ISE.

image
image1091×166 98.3 KB

This is what I got when I opened the exported csv file:

image

Is this the format you were looking for? I think now I just need to be able to export the results to a CSV correctly.

8

You have to provide something for Export-Csv to work with. Did you get the system of piping the results of one cmdlet to the next the PowerShell pipeline is based on? :face_with_raised_eyebrow:

Run it hits way

$ResultList |
Group-Object -Property Department | 
ForEach-Object {
    [PSCustomObject]@{
        Name      = $_.Name
        GroupList = $_.Group.Group -join ','
    }
} |
Export-Csv $ExportPath

Your output looks weird though … the name column should have on one department per row.

9

oh Jesus…I forgot to add the export path variable (slapping head).

So, like, it worked and I think I am good to go.

image
image942×148 7.54 KB

Thanks a ton for this. This was a tough one for me. I have learned a lot!