I had to change how we update employeeID’s from a CSV sheet that is provided to me by Oracle. The script we used to use did comparisons between the sheet by email address, and now oracle isn’t supplying those so i need to write something to match employee id’s and update them. Here is what i have so far.
Import latest adp file, set as variable, only selecting objects from file that have email field populated.
$adplist = import-csv "C:\Temp\Test2.csv" # | Where-Object {$_.'EMPLID'}
#write-host $adplist
#Loop through each selected object in the csv.
ForEach ($employee in ($adplist | Where {$_.Company -Match "CompanyA"})) {
Write-Host $employee.'First_Name' $employee.'Last_Name' $employee.'EMPLID' $employee.Company
#Locate AD user based on employeeID match in specific OU
if ($employee.'EMPLID' -ne ""){
#Write-Host "Employee ID Found as"$employee.'EMPLID'
$EmployeeID = $employee.'EMPLID'
$adUserByEmployeeID = Get-ADUser -SearchBase "OU=Employees,OU=User Accounts,DC=CompanyA,DC=com" -Properties Emailaddress, employeeid -filter 'EmployeeID -eq $EmployeeID' #$employee.'Email Address'"
#Write-Host "adUserByEmployeeID: " $adUserByEmployeeID
}
#If AD user resolves by EmployeeID
if ($adUserByEmployeeID) {
#Sets the format of EmployeeID to Oracle CSV
$id = [int]$employee.emplid
Write-Host "Employee ID: " $id
#Gets the EmployeeID of the user from AD
$existingEmployeeID = get-aduser $adUserByEmployeeID -properties employeeid | select $_.employeeid
write-host "existing employee id"$existingEmployeeID
#Check if the employee ID is already set and set to ID given by Oracle
if ($existingEmployeeID.employeeid -eq "" -or $existingEmployeeID.employeeid -ne "") {
#Set Employee ID of user in AD - only if it was previously unset
Set-ADUser $adUserByEmployeeID -EmployeeID $id
write-host "TRIED TO WRITE NEW ID"
}
write-host "Did not write new ID"
}
}
It gets through most of it but doesn’t seem to update the employee ID’s from the CSV sheet. There is something in the end that is stopping it and i’m not sure what.
It’s hard to tell from a quick look at the script especially without the input/output. I would run the script in ISE and set breakpoints and/or step through the code line by line. Examine the contents of the variables before each lines executes, check the results immediately after, etc.
Some observations though:
if ($existingEmployeeID.employeeid -eq “” -or $existingEmployeeID.employeeid -ne “”) {
You may have done this on purpose thinking it would execute regardless of the value, but “” and null are 2 different things. I always use [string]::IsNullOrEmpty($var) as my test when I don’t know or don’t care if the value can be null.
Also, are you positive there will never be a duplicate employeeID? If there is one, $existingEmployeeID will be an array.
I’m having trouble following your logic and understanding the goal of your code.
- Loop through Oracle data
- Find a user with a matching employeeID
- Get the user again (the user is already found with your first Get-ADUser)
- if this user is or is not empty (this will always be true, because it's one or the other)
- Set the employee ID on the user (you just searched for the user on this criteria, so the employee ID is already set to this value)
Can you explain what you are trying to accomplish? The only change in your code is casting the employeeID to a int, which would remove leading zeros. You don’t need a CSV to fix that, you could just find any employee with a ID set and reset the employee ID to a non-padded value.
Get-ADUser -Filter "EmployeeID -like '*'" | Set-ADUser -EmployeeID $([int]$_.EmployeeID) -WhatIf
Ron,
There will never be an identical employee number. If there ever is then HR made a huge mistake!
Rob,
The end goal is that I need to change every employee’s ID’s to match what oracle gives on the csv. After that I will need to then have a script to automate the matching of employees from the CSV and update any information in Active Directory and add a field as well.
I think his problem is that he lost the only way to link the Oracle data to AD. He was using the email address, which creates a positive match between the 2 sets of data. Now, he’s lacking a method to cross reference them. You could resort to name matching, but that would be unreliable. Matching by employeeid, in order to update employeeid, won’t work.
Ron…
Crap! I am trying to get them to sent work emails so the original script would work, but for some reason they’re sending personal emails and I cannot match those to anything in active directory. It’s very stupid and lazy i think. Getting Oracle to do something outside what they are doing is like running up a hill made of ice!
I have changed this a little bit so that what I need it to do now is to match the Active Directory Email address to the email address on the CSV, then move forward. The part that it’s getting stuck at is the variable to make it equal to the User’s email address.
My variable $adEmail is adding too much data such as @{EmailAddress=UserA@emailaddress.com} when I need $adEmail = UserA@emailaddress.com so i can match what is in AD to the CSV.
How can I get only the results without all the other junk like the headers?
My guess would be that you pulled ADEmail with a select-object, and you need to add -expand.
I’m using basically…
$ademail = get-Aduser -SearchBase “OU=” -properties Emailaddress | select Emailaddress
The output is good just has too much extra header junk in the variable to match what would be on the CSV sheet
So simply adding -expandproperty after select worked like a charm…thus.
$ademail = get-Aduser -SearchBase “OU=” -properties Emailaddress | select -ExpandProperty Emailaddress
Alrighty, moving onwards… 
select -expand Emailaddress
That will convert the object to a string.
Right now, if you were to ouput $ademail, you would get someething like this:
[PS] $ademail
Emailaddress
Blah@blah.com
If you add -expand, you just get the data.
[PS] $ademail
Blah@blah.com
You could also have just used: $ademail.Emailaddress
Select-OBJECT just pulls out the the selected columns, the result is just a sub-setted object.
$user = get-aduser blah -Properties mail
$user.gettype()
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True False ADUser Microsoft.ActiveDirectory.Management.ADAccount
($user | select mail).gettype()
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True False PSCustomObject System.Object
($user | select -expand mail).gettype()
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True String System.Object