Bulk ADGroup Import Performance

PowerShell Help
1

Hello all,

I’m having an issue that I understand but I’m not really sure how to make things bigger. I need to import a large group of users into an AD group that they may already be a part of. The problem is that even with 500ish computer objects, this script is so slow! I’ve heard of ways to import something like group membership into a temporary table but I’m not 100% sure how to build that out. Possible work flow?..

  1. Import CSV for list of computers
  2. Import ADGroupMembership and store as a temporary table
  3. If $_.computer exists in temp table, write to log “:)”
  4. If $_.computer doesn’t exist in temp table, add to ad group and write to log unless computer exists in exception list (had to through that one in ;))

Here is what I have so far…

# Connect Quest
Add-PSSnapin quest.active*

#var declaration

$date = (Get-Date -f MMddyy)
$transcript = "./$date.log"
$csvpath = "./computers.csv"

Start-Transcript -Path $transcript

If ((Test-Path -Path $csvpath) -eq $false)
{
	
	Write-Verbose -Verbose "FILE NOT FOUND!! $csvpath"
}
Elseif ((Test-Path -Path $csvpath) -eq $true)
{
	
	$Computers = Import-Csv -Path $csvpath
	$Exceptions = @("Junk1","Junk2")
	
	
	# Loop through computers
	$Computers | ForEach-Object {
		
		If ($Exceptions -contains $_.ComputerName)
		{
			
			#give warning
			Write-Warning "Skipping $($_.ComputerName) due to manually specified exception"
			
		}
		else
		{
			# Add computer as member to the group with verbose console output and nulling output object
			Add-QADGroupMember -Identity 'SomeGroup' -Member $_.ComputerName -Verbose | Out-Null
		} # if/else
		
		
	} # End computers Loop
	
	#Disconnect Quest
	Disconnect-QADService -Service vwoaahvp199.vwoa.na.vwg -proxy
	Remove-PSSnapin quest.active*

}

Stop-Transcript
2

Let me maybe re-state the problem to make sure I have it.

You have List A, and you want to make AD look like List A.

This involves enumerating List A, checking each item in it, and fixing the items that aren’t right (e.g., computer isn’t a member of a group).

Your slow bits are going to be querying AD for each item in List A, and then performing the actual add-to-group operation.

Your proposed alternative is simply dumping all the group memberships from AD in one operation, and putting them in (say) SQL Server Express, which cane queried much more quickly than AD.

That’s probably a valid approach to at least try. You can look at one of our ebooks, “Creating Historical and Trend Reports,” for some code examples on how to use PowerShell to query SQL Server. A local Express install should work quite well for this. You’ll have to query the group memberships, enumerate them, and fire off a SQL INSERT statement for each one. That’ll populate your database. Then, your script can check against SQL Server rather than AD directly. When you find a missing item, you add it to AD appropriately.

I can’t say how MUCH this will speed things up - it’s something you kind of have to test to see, because there are a lot of environmental specifics.

3

Don,

Yes you have the idea. I actually found what I was trying remember also…https://powershell.org/forums/topic/csv-to-csv/

Dave provided a method for “building” a search base when I was attempting to generate an AD report that sped things up 80% at least…just have to remember how to use it…:slight_smile:

Dave Wyatt · October 20, 2014 at 9:54 am #19867 Reply | Quote

Dave Wyatt
Moderator
Topics: 14
Replies: 1491

You can make a single call to Get-Qaduser which stores the information you need in memory, then use that information to perform the CSV transformations. Depending on how many users are in your domain, though, you may run into memory problems here. (Memory usage and execution time are often competing with each other in these situations.)

You can try something like this:

$csv= ‘c:\somefolder\master.csv’

$userTable = @{}

filter UpdateUserTable
{
$userTable[$.SamAccountName] = [pscustomobject] @{
FirstName = $.FirstName
LastName = $.LastName
Email = $.Email
CostCenter = $_.PhysicalDeliveryOfficeName
}
}

Get-QADUser | UpdateUserTable

filter UpdateObject
{
$csvEntry = $_
$userEntry = $userTable[$csvEntry.UserID]

[pscustomobject] @{
    Computer      = $csvEntry.Computer
    UserID        = $csvEntry.UserID
    FirstName     = $userEntry.FirstName
    LastName      = $userEntry.LastName
    Email         = $userEntry.Email
    'Cost Center' = $userEntry.CostCenter
    Date          = $csvEntry.Date
    DeviceID      = $csvEntry.DeviceID
    ProviderName  = $csvEntry.ProviderName
    DriveType     = $csvEntry.DriveType
}

}

Import-Csv $csv |
UpdateObject |
Export-Csv -Path ‘C:\somefolder\master_final.csv’ -N