Add Members to Group – a different approach

‘Sup PSHomies,

My Project Manager is slowly becoming a  true PowerShell believer! Of course he doesn’t have to do the actual scripting, that’s where I come in… 😉

So in walks the PM…

PM: “Say Urv, if I gave you an excel worksheet with the user/group relationship, think you’d be able to script it?”

Me:”Gee, this is so sudden, let me think about it and get back to you asap… (Grinning)”

Here’s an impression of the worksheet:

I’ve done this in past by just going through each user adding each group. I remember reading a post by Mike F. Robbins on how this could be done more efficiently! I very much like this approach. I’ve added a lil’ extra to the mix. Let’s dig in!

Well for starters I’ll just get the source directly from the excel file using D. Finke’s ImportExcel Module. No need to convert to CSV first.

$xlsxADGroupMembers = Import-Excel .\source\xlsx\$xlsxFile -WorkSheetname $WorkSheet

The other thing was retrieving the GroupNames from PSCustomObject by selecting MemberType ‘NoteProperty’, I got this as a tip on my own blog by Dirk. This way you’re not depending on the position of where the group names start in the header.

#Select Group names from Object
$Header = $xlsxADGroupMembers |
   Get-Member -MemberType NoteProperty |
   Where-Object{$_.Name -ne  'UserID'} |
   Select-Object -ExpandProperty Name

This makes it just a bit resilient.

I also decided to use hashtable to store the results first before processing the group membership. This way I can also export the results for future reference (Always keep a log)

#Create empty hashtables
$addADGroupMembers = @{}
$delADGroupMembers = @{}

#Get Group membership
$Header |
ForEach-Object{
   $Group = $_
   $addADGroupMembers.$Group  = $xlsxADGroupMembers.Where{$_.$Group -eq '1'} | Select-Object -ExpandProperty 'UserID'
   $delADGroupMembers.$Group = $xlsxADGroupMembers.Where{$_.$Group -ne '1'} | Select-Object -ExpandProperty 'UserID'
}

Now it’s time to add the members to specified groups

$Header |
ForEach-Object{
   if($addADGroupMembers.$_){
      try{
         Add-ADGroupMember -Identity $_ -Members $addADGroupMembers.$_
      }
      catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
         Write-Warning "AD Object $($Error[0].CategoryInfo.TargetName) not found"
      }
   }

   if($delADGroupMembers.$_){
      try{
         Remove-ADGroupMember -Identity $_ -Members $delADGroupMembers.$_ -Confirm:$false
      }
      catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
         Write-Warning "AD Object $($Error[0].CategoryInfo.TargetName) not found"
      }
   }
}

I’m using Try/Catch to catch any errors on AD Objects not existing. Without it you’d get quite a few errors if the AD objects don’t exists.

Here’s the full script:

	<#
	Author: I. Strachan
	Version:
	Version History:
	Purpose: Add group member from excel worksheet. Scripts is based on Mike Robin's work
	http://tinyurl.com/jfmw4o7
	#>
	[CmdletBinding()]
	param(
	$xlsxFile = 'Demo-Kruisjes.xlsx',
	$WorkSheet = 'Demo'
	)
	#region
	$exportDate = Get-Date -Format ddMMyyyy
	#endregion
	#Import Worksheet Using D. Finke's ImportExcel module http://tinyurl.com/lbhkhbd
	$xlsxADGroupMembers = Import-Excel .\source\xlsx\$xlsxFile -WorkSheetname $WorkSheet
	#Select Group names from Object
	$Header = $xlsxADGroupMembers |
	Get-Member -MemberType NoteProperty |
	Where-Object{$_.Name -ne 'UserID'} |
	Select-Object -ExpandProperty Name
	#Create empty hashtables
	$addADGroupMembers = @{}
	$delADGroupMembers = @{}
	#Get Group membership
	$Header |
	ForEach-Object{
	$Group = $_
	$addADGroupMembers.$Group = $xlsxADGroupMembers.Where{$_.$Group -eq '1'} | Select-Object -ExpandProperty 'UserID'
	$delADGroupMembers.$Group = $xlsxADGroupMembers.Where{$_.$Group -ne '1'} | Select-Object -ExpandProperty 'UserID'
	}
	#region Main. Add and remove users to/from groups
	$Header |
	ForEach-Object{
	if($addADGroupMembers.$_){
	try{
	Add-ADGroupMember -Identity $_ -Members $addADGroupMembers.$_
	}
	catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
	Write-Warning "AD Object $($Error[0].CategoryInfo.TargetName) not found"
	}
	}
	if($delADGroupMembers.$_){
	try{
	Remove-ADGroupMember -Identity $_ -Members $delADGroupMembers.$_ -Confirm:$false
	}
	catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
	Write-Warning "AD Object $($Error[0].CategoryInfo.TargetName) not found"
	}
	}
	}
	#endregion
	#region Export for futher processing
	$GroupMembers =@{
	Groups = $Header | Get-ADGroup -Properties WhenCreated
	Added = $addADGroupMembers
	Revoked = $delADGroupMembers
	}
	$GroupMembers |
	Export-Clixml .\export\dsa\ADGroupMembers-$exportDate.xml -Encoding UTF8
	#endregion

view raw Add-MemberToADGroup.ps1 hosted with ❤ by GitHub

I like the fact that you can use the list to add, but also delete if it isn’t necessary. Of course if you fill it in wrong, then there is no fixing that. So be warned!

Another thing is that members can be added manually or another process, so don’t be surprised, when you’re evaulating the group membership….

Tip: Save the  GroupMembership just to be sure…

#Get current Group memberships
$SnapshotADGroupMembers = @{}

$Header |
ForEach-Object{
   $SnapshotADGroupMembers.$($_) = Get-ADGroupMember -Identity $_ | Select-Object -ExpandProperty SamAccountName
}

Next time I’ll blog about how we could to this in reverse… Stay tuned!

Hope it’s worth something to you,

Ttyl,

Urv