My HR department wants to update AD with some basic information including Manger and Assistant. They are providing a .csv file that contains a 10 digit employee id number to identify employess, their managers and assistants (if they have any). I need to get the users’ names, managers’ names and assistants’ names from AD using the employee id and then set the manager and assistant name for each user.
I’m using activeroles quest active directory management (free) to connect to get users and set information.
I’m able to do it with a test .csv file that contains one user record with the following script code:
But I receive an error that the users in the file cannot bind to the $user identity. so, I believe, it’s due to the fact that I am unsure how to do it for multiple users and the variables. Any help is greatly appreciated!
I realize that isn’t terribly different, but it gets you down to working with one file entry at a time. What you’re doing with the multiple ForEach-Object calls is not, I don’t think, going to get you useful data. It’s entirely possible that $user is null, or that it is an array of multiple objects. Also, I’m not sure that -Manager and Assistant will accept a complete user object as their values - but that’s what you’re passing. I think those may just need a CN, which you could get as a property of the respective user objects.
You didn’t post the specific error message, and I don’t have a sample of your file to look at. For example, in this case, I’d probably put a debug breakpoint after $user gets set. That would let me test the value of $user to make sure it’s what I wanted. If it wasn’t, I could execute Get-QADUser manually to see more detail about what the problem was, perhaps. Youc an set breakpoints in the ISE.
It’s been a while since I’ve worked with the Quest cmdlets for Active Directory so let me first start out by asking what OS your Active Directory domain controllers are running? Just want to make sure that using the Quest AD cmdlets are necessary.
Not sure what field in AD that your putting the assistant information in so I omitted that part, but if you do indeed have to use the Quest cmdlets, something like this should work:
I have to give you credit. This was a lot more complicated than I originally thought. Either one of these should work depending on if you want to use the foreach scripting construct or the ForEach-Object cmdlet.
Both the manager and assistant properties require a Distinguished Name:
Funny thing is the Assistant property doesn’t even show up in PowerShell as an option until you actually set it’s value which is what lead me to the Active Directory schema MMC which is where that image is from (you know you’re getting deep when you start digging around in the AD schema).
The code above is based on a csv with the following format where the numbers are the value of an AD users EmployeeID property.
Thanks for the assistance. I attached a sample of the file. There’s other headers there but I’m not concerned about them.
What I’m trying to do is find the username in AD, the manager and assistant from the employeeid. I then hold those names in a variable and use the set command to apply the discovered names that were once employee ids. It works with one user/record but will not apply with more than one user/record in the file.
So, to recap, I need to take each employee in the row, find their user information based on employeeid, find their manager and assistant baed on employeeid, keed the user information linked to the proper manager name and assistant name that were found and then add the manager name and assistant name to the proper user.
Thanks for the reply. I tried the “foreach-object” code and received the following error:
Set-QADUser : Cannot convert ‘System.Object’ to the type ‘Quest.ActiveRoles.ArsPowerShellSnapIn.Data.IdentityParameter’ required by parameter ‘Manager’. Specified method is not supported.
At line:6 char:26
I'm pretty sure it's due to the fact that $manager.managerid returns the numeric employeeid for the manager rather than the name. That's where $manager = $file | Foreach-Object {Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$_.manager}} in my original attempt helps. The $manager holds the found manager name and then can be used to import that into the -Manager field. When it imports using the $manager data it does import as a DN.
So, I need to do the following for each record:
Find username based on employeeid
Find manager based on employeeid
Find assistant based on employeeid
Set username (found in step one) with the manager name and assistant name found in steps 2 & 3 for each row in our file.
Assuming that more than one person shares the same manager and that an assistant can assist more than one person, it is likely that the same ID will show up in the inputfile more than once.
To minimize the number of queries to Active Directory, I first extract each unique ID in the input-file and store these in $AllUserIDs.
For each unique ID found I query Active Directory for a distinguished name and add that value to a hashtable where EmployeeID is the key.
Now just import the csv again and loop through each row setting the attributes assistant and manager by using references to the hashtable.
I tried to utilize the pipeline as much as possible to lessen the amount of memory used if the csv-file is huge. For smaller files it might be faster to store the imported csv in a variable and using Foreach instead of Foreach-Object.
Thanks, so much! I modified your code to account for the path to my file (see below). When it runs, I get a “path too long” exception seemingly at the @ location (see below):
You are not supposed to add Import-CSV to the command $InputFile = ‘’
Let’s try again, copy this code and only edit line two and three to reflect the Organisational Unit in Active Directory to use as a searchbase and the path to your inputfile.
Thank you soo much! That worked in as far as the manager field updated but not the assistant field! Are you able to describe what is going one line by line or at least let me know how to set debug points in the powershell ISE so I can see it?
Sounds like you have a solution. I tweaked one of the ones I created based on the errors you reported and thought I would post it in case someone else comes across this thread. This version accounts for no entry in the manager and/or assistant fields:
Mikes script above will do the trick, I realize now that my approach might have gotten a bit complex.
But for an educational purpose, I’ve commented the code and added some verbose output, hope it helps.
[Cmdletbinding()]
Param(
# Set OU to the distinguished name of the search root
$OU = "OU=Staff,DC=simonw,DC=se",
# Defines the path to CSV input-file
$InputFile = 'E:\data\powershell\Mockup_HR_EMPLID_EXP2a.csv',
# Defines char used as a delimiter in the CSV
$Delimiter = '|',
# Set EmployeeID to the name of the column containing EmployeeID in CSV
$EmployeeID = 'EmployeeID',
# Set Manager to the name of the column containing Manager reference in CSV
$Manager = 'Manager',
# Set Assistant to the name of the column containing Assistant reference in CSV
$Assistant = 'Assistant'
)
# Sets VerbosePreference to Continue to make sure verbose output is always shown
# Remove this line once satisfied with the script
$VerbosePreference = 'Continue'
########################################
# Do not edit anything below this line #
########################################
Add-PSSnapin -Name Quest.ActiveRoles.ADManagement
# This just gives us a list of any employeeID referenced in the csv-file
$AllUserIDs = Import-Csv -Delimiter $Delimiter -Path $InputFile |
ForEach-Object {
$_.$EmployeeID
$_.$Assistant
$_.$Manager
} | Where-Object {$_ -ne $null} | Sort-Object -Unique
# This creates an empty hashtable (read more on hashtables using Get-Help about_Hash_Tables
$AllUsers = @{}
# Loop through each EmployeeID found in the CSV
Foreach($UserID in $AllUserIDs)
{
# Retrieve the distinguishedName for each user referenced in CSV
$DN = Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$UserID} |
Select-Object -ExpandProperty DN
# Add an entry to the hashtable (EmployeeID is the Key and the DistinguishedName the Value)
$AllUsers.Add($UserID,$DN)
}
# Now we can translate any EmployeeID in the CSV to a distinguished name.
# For example if an emploeeID is 123456 this will return that users distinguished name:
# $AllUsers["123456"]
# Import the CSV-file again and run Set-QADUser once for each row.
Import-Csv -Delimiter $Delimiter -Path $InputFile | Foreach {
# output some verbose messages
Write-Verbose -Message "Processing EmployeeID $($_.EmployeeID):"
Write-Verbose -Message "[$($_.$EmployeeID)] ID translates to DN: $($AllUsers["$($_.$EmployeeID)"])"
Write-Verbose -Message "[$($_.$EmployeeID)] Manager ID is: $($_.$Manager)"
Write-Verbose -Message "[$($_.$EmployeeID)] Manager translates to DN: $($AllUsers["$($_.$Manager)"])"
Write-Verbose -Message "[$($_.$EmployeeID)] Assitant ID is: $($_.$Assistant)"
Write-Verbose -Message "[$($_.$EmployeeID)] Assitant translates to DN: $($AllUsers["$($_.$Assistant)"])"
# Update user in AD and suppress the output
Set-QADUser -Identity $AllUsers["$($_.$EmployeeID)"] -Manager $AllUsers["$($_.$Manager)"] -ObjectAttributes @{Assistant=$AllUsers["$($_.$Assistant)"]} -Verbose:$false |
Out-Null
# Output a Verbose message with two blank lines in the end to make it easier to read
Write-Verbose -Message "Done processing EmployeeID $($_.$EmployeeID)`n`n"
}
What I’m trying to figure out now is how to incorporate basic ad fields that need to be set from the .csv file into this script you created. For example, ipPhone is a column in my .csv file and the information is there. I don’t have to convert it to a DN, obviously, like the manager or assistant field. I’m looking to mimic the paramaters you set for $manager and $assistant to user for ipPhone or sn but it’s not the same procedure.
Also, if you don’t mind, what accounts for this line: $Params.ObjectAttributes = @{assistant=$assistant}? Why is that there and there isn’t a similar line for the manager parameter?
thanks, again! This has been a great learning experience.
More or less, yes. By the time that portion of the code occurs, the CSV has been imported and is converted to objects. It checks to see if that particular user’s manager is populated and only executes the code in the if statement if it is (if it’s true).
[Set-QADUser], ParameterBindingException