The Situation
Adding new employee user accounts, updating their information when changes occur, and deleting accounts when employment ends are straightforward tasks in Windows Active Directory—if you’re only managing a small number of changes at a time.
However, this simplicity becomes a challenge as the number of employees grows, as we’ve experienced at work. Over the years, our employee count has increased, leading to a corresponding rise in domain users. Additionally, every employee requires access to a domain computer to perform their work. On top of that, we also need to create user accounts for interns, volunteers, and contractors, and managing their access status further complicates things.
To make matters worse, we have a limited number of IT staff and a restricted IT budget, as our organization is a non-profit. Therefore, I need to find a solution to manage this aspect of our work more efficiently.
Here’s What I Did in This Situation
I recognized that finding better ways to manage our user list was a significant project for us, but I was confident that once completed, it would bring substantial long-term benefits to the department. The first step I took was to examine our HRIS (Human Resources Information System) and AD (Active Directory), comparing the information in AD with what we had in our HRIS.
One issue I noticed was that the employee ID number had never been entered into AD. I understood that this would be the first thing I needed to address. I needed a unique key for this project, and the EIDN (employee ID number) served this purpose perfectly.
To begin, I created a local folder on my computer and prepared a CSV file with the following columns:
- SamAccountName
- EmployeeID
- Department
- ManagerSamAccountName
Next, I wrote a PowerShell script to generate a CSV file in this format. I also created a corresponding report in our HRIS with the following columns:
- Username
- EmployeeID
- Department
- Manager
Get-ADUser -Filter {Enabled -eq $true} -Properties EmployeeID, Department, Manager |
Select-Object Name,SamAccountName,EmployeeID,Department,Manager |
Sort-Object Name |
Export-Csv -Path "C:\psLogs\EmployeeList-AD-result.csv" -NoTypeInformation
Using MS Excel, I populated the CSV file with the necessary information. I used the VLOOKUP function in this task.
Then I created this script that will import the CSV file and update the Active Directory.
Import-Csv -Path "C:\psLogs\EmployeeList-AD.csv" |
ForEach-Object {
$managerDN = (Get-ADUser -Identity $_.ManagerSamAccountName).DistinguishedName
Set-ADUser -Identity $_.SamAccountName -EmployeeID $_.EmployeeID -Department $_.Department -Manager $managerDN
}
<h1>Use this if you want to export results into a csv file.</h1>
Get-ADUser -Filter {Enabled -eq $true} -Properties EmployeeID, Department, Manager |
Select-Object Name,SamAccountName,EmployeeID,Department,Manager |
Sort-Object Name |
Export-Csv -Path "C:\psLogs\EmployeeList-AD-result.csv" -NoTypeInformation
Now that I have the EID of employees I can easily compare our current employee list with our active AD user list.
