Group Membership Report

nevilleonline · October 25, 2017, 2:32am

Hi All,

I have been in the middle of creating a group membership report for the management. I managed to get the report using powershell but spent hours formatting it the way I wanted. I am very new to powershell so here goes nothing.

I am looking to get report based on a few conditions and the csv has to be in a certain format. These users have to be active and from a certain OU which has space like OU=My Test OU,DC=Subdom,DC=dom,DC=tld. All group names are different but start with lets say “GPA_”

First Condition : There are 10 odd groups. If the user belongs to any one of these groups. Then the CSV has to be in below format

username AccessA AccessB AccessC
userA Disabled Disabled Disabled

Second Condition: There are 3 groups. If the user belongs to one or more than one group than accordingly it will be

username AccessA AccessB AccessC
userB Enabled Disabled Disabled

or

username AccessA AccessB AccessC
userB Enabled Enabled Disabled

depending one which group and how many groups it is a member of.

Third Condition: If the any user is not a member of any group that start with GPA_* then the CSV will be formatted as

username AccessA AccessB AccessC
userC Enabled Enabled Enabled

If everything can be in one CSV then it would be great.

I used the below powershell scripts to get the data i needed,

Powershell one for condition one and two

 
$CustomResult=@() 
$groups = Get-Content "C:\\groups.txt"           
$Groups | ForEach-Object {
$group = $_
Get-ADGroupMember $group | ForEach-Object {
$CustomResult += [PSCustomObject] @{ 
                GroupName = $group
				Username = $_.samAccountName
                Member = $_.Name				
            }           
  }
}
$CustomResult | Export-CSV "C:\\ad-group-members.csv" -NoTypeInformation -Encoding UTF8

Powershell 2 for third condition

Get-ADUser -SearchBase "OU=My Test OU,DC=Subdom,DC=dom,DC=tld" -Filter * -properties memberof | Where-Object {!($_.memberof -like "*GPA_*")} |  Select-Object SamAccountName | Export-CSV "C:\\fullaccess-members.csv"

Any help will be great.Thanks in advance.

willymoselhy · October 25, 2017, 2:54am

Lets play!
I usually like to begin with the end in mind, in your case that’s the perfect CSV, right?
in this case, would this format be perfect?
SamAccountName, AccessA, AccessB, AccessC
User1,Enabled,Disabled,Disabled
USer2,Disabled,Disabled,Disabled
etc…

You are off to a good start…

nevilleonline · October 26, 2017, 7:59am

Ok I am very close. I have resolved first and third condition, but struggling with second condition. Can anyone help please?

Get-ADUser -SearchBase "OU=My Test OU,DC=Subdom,DC=dom,DC=tld" -Filter * -properties memberof | 
			Where-Object {(($_.memberof -match 'GPA_CD') -or `
                                       ($_.memberof -match 'GPA_SD') -or `
                                       ($_.memberof -match 'GPA_US')) -and ( `
						 ($_.SamAccountName -like "co-*") -or `
						 ($_.SamAccountName -like "ea-*") -or `
						 ($_.SamAccountName -like "em-*") -or `
						 ($_.SamAccountName -like "gl-*") -or `
						 ($_.SamAccountName -like "hi-*") -or `
						 ($_.SamAccountName -like "lo-*") -or `
						 ($_.SamAccountName -like "ne-*") -or `
						 ($_.SamAccountName -like "ni-*") -or `
						 ($_.SamAccountName -like "nw-*") -or `
						 ($_.SamAccountName -like "se-*") -or `
						 ($_.SamAccountName -like "wa-*") -or `
						 ($_.SamAccountName -like "wm-*") -or `
						 ($_.SamAccountName -like "wx-*") -or `
						 ($_.SamAccountName -like "yh-*"))} |  
			Select-Object @{ expression={$_.SamAccountName}; label='Username' }, `
			ForEach-Object{	if ($_.memberof -match 'GPA_CD') `
						  {@{ expression={"Enabled"}; label='US' }, `
						  @{ expression={"Disabled"}; label='CD' }, `
						  @{ expression={"Enabled"}; label='SD' }} `
						  if ($_.memberof -match 'GPA_SD') `
						  {@{ expression={"Enabled"}; label='US' }, `
						  @{ expression={"Enabled"}; label='CD' }, `
						  @{ expression={"Disabled"}; label='SD' }}
						  if ($_.memberof -match 'GPA_US') `
						  {@{ expression={"Disabled"}; label='US' }, `
						  @{ expression={"Enabled"}; label='CD' }, `
						  @{ expression={"Enabled"}; label='SD' }} `
						  if (($_.memberof -match 'GPA_CD') -and ($_.memberof -match 'GPA_SD')) `
						  {@{ expression={"Enabled"}; label='US' }, `
						  @{ expression={"Disabled"}; label='CD' }, `
						  @{ expression={"Disabled"}; label='SD' }} `
						  if (($_.memberof -match 'GPA_SD') -and ($_.memberof -match 'GPA_US')) `
						  {@{ expression={"Disabled"}; label='US' }, `
						  @{ expression={"Enabled"}; label='CD' }, `
						  @{ expression={"Disabled"}; label='SD' }} `					  
						  if (($_.memberof -match 'GPA_US') -and ($_.memberof -match 'GPA_CD')) `
						  {@{ expression={"Disabled"}; label='US' }, `
						  @{ expression={"Disabled"}; label='CD' }, `
						  @{ expression={"Enabled"}; label='SD'}} `
						  }| Export-CSV "C:\\members.csv"

I get a syntax error “Select-Object : A positional parameter cannot be found that accepts argument ‘ForEach-Object’.” But not sure how to resolve this. Any pointers or help would be great on this.

willymoselhy · October 26, 2017, 8:57am

Change the line of Select-Object as follows,

Select-Object @{expression={$_.SamAccountName}; label='Username' }} |

And there are some methods to make your script easier,

Put label before expression to make it easier to read.
Use -in instead of -like for the where-object, it should look like this

($_.SamAccountName.Substring(0,3) -in @("co-","ea-")

and of course you can define an array with all the prefixes and use it.

If I have time I’ll try to write a better example

nevilleonline · October 27, 2017, 12:45am

Thanks Willy. Pretty cool. I got the top part to work well and that error is gone but i don’t get any output if I pipe it to format-table.
Can you help me get the second part right I think my logic is wrong and this whole thing can be much shorter.

ForEach-Object{	if ($_.memberof -match 'GPA_CD') `
		 {@{ expression={"Enabled"}; label='US' }, `
		  @{ expression={"Disabled"}; label='CD' }, `
		  @{ expression={"Enabled"}; label='SD' }} `
		if ($_.memberof -match 'GPA_SD') `
		 {@{ expression={"Enabled"}; label='US' }, `
		  @{ expression={"Enabled"}; label='CD' }, `
		  @{ expression={"Disabled"}; label='SD' }}
		if ($_.memberof -match 'GPA_US') `
		 {@{ expression={"Disabled"}; label='US' }, `
		  @{ expression={"Enabled"}; label='CD' }, `
		  @{ expression={"Enabled"}; label='SD' }} `
		if (($_.memberof -match 'GPA_CD') -and ($_.memberof -match 'GPA_SD')) `
		 {@{ expression={"Enabled"}; label='US' }, `
		  @{ expression={"Disabled"}; label='CD' }, `
		  @{ expression={"Disabled"}; label='SD' }} `
		if (($_.memberof -match 'GPA_SD') -and ($_.memberof -match 'GPA_US')) `
		 {@{ expression={"Disabled"}; label='US' }, `
		  @{ expression={"Enabled"}; label='CD' }, `
		  @{ expression={"Disabled"}; label='SD' }} `					  
		if (($_.memberof -match 'GPA_US') -and ($_.memberof -match 'GPA_CD')) `
		 {@{ expression={"Disabled"}; label='US' }, `
		  @{ expression={"Disabled"}; label='CD' }, `
		  @{ expression={"Enabled"}; label='SD'}} `
		}| Export-CSV "C:\\members.csv"

Pretty sure even if it works each column will give double/triple entries in each column. An example I can use would be great.

willymoselhy · October 29, 2017, 12:39am

First, you do not need to use Select-Object as it will create a new object with just the selected properties (Username in your code). This new object does NOT have the property ‘memberof’ which we are trying to match in the next step.

In your place I would get all the data from Get-ADUser into a variable, then use an array of custom objects to collect the data in Foreach.

An array of objects can then be easily exported to a CSV or viewed using Out-GridView.

Email me at W “AT” mslhy “DOT” com. maybe we can get in contact and do this one together!

nevilleonline · October 30, 2017, 8:20am

Thanks Willey,

Got it to work.

willymoselhy · October 30, 2017, 8:22am

How about posting the final script so everyone gets to share the knowledge?

nevilleonline · October 30, 2017, 8:26am

Here you go…

Get-ADUser -SearchBase "OU=My Test OU,DC=Subdom,DC=dom,DC=tld" -Filter * -properties memberof | 
			Where-Object {(($_.memberof -match 'GPA_CD') -or `
                           ($_.memberof -match 'GPA_SD') -or `
                           ($_.memberof -match 'GPA_US')) -and `
						  (($_.SamAccountName.Substring(0,3)) -in @("co-","ea-","em-","gl-","hi-","lo-","ne-","ni-","nw-","se-","wa-","wm-","wx-","yh-"))}|  
			Select-Object @{ expression={$_.SamAccountName}; label='Username' },`
            @{ expression={if ($_.memberof -match 'GPA_US'){"Disabled"} else {"Enabled"} }; label='US' }, `
            @{ expression={if ($_.memberof -match 'GPA_CD'){"Disabled"} else {"Enabled"}}; label='CD' },`
            @{ expression={if ($_.memberof -match 'DLP_SD'){"Disabled"} else{"Enabled"} }; label='SD' }| Export-CSV "C:\\restrictedaccess-members.csv"

Topic		Replies	Views
Get AD User and Group Membership and export to CSV PowerShell Help	2	205	September 26, 2019
Active Directory CSV of groups user is member of PowerShell Help	1	226	February 15, 2022
Script to display information in CSV PowerShell Help	6	203	March 3, 2019
Get-ADPrincipalGroupMembership and save it to .csv report PowerShell Help	13	1391	December 5, 2022
Query for specific group membership, export to csv PowerShell Help	2	122	February 27, 2014

Group Membership Report

Related Topics