Group Membership Report

This topic contains 8 replies, has 2 voices, and was last updated by  Neville D’Souza 3 weeks ago.

  • Author
    Posts
  • #82826

    Neville D’Souza
    Participant

    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.

  • #82829

    Willy Moselhy
    Participant

    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...

  • #82951

    Neville D’Souza
    Participant

    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.

  • #82966

    Willy Moselhy
    Participant

    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

  • #83066

    Neville D’Souza
    Participant

    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.

  • #83200

    Willy Moselhy
    Participant

    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!

  • #83225

    Neville D’Souza
    Participant

    Thanks Willey,

    Got it to work.

    • #83227

      Willy Moselhy
      Participant

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

  • #83230

    Neville D’Souza
    Participant

    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"
    

You must be logged in to reply to this topic.