How To Sync Active Directory Users With A Spreadsheet
Think you can do better? Show us!
It’s not uncommon for Human Resources to pass on lists of new staff, or changes to existing staff, in the form of a spreadsheet. You could go through these spreadsheets line by line and manually update Active Directory. This may work for small domain, but even then you are human and humans make mistakes. When mistakes do happen, was it because of the data from HR or because you misread it? Get ready to operate at scale, and reduce the risk of human error, by learning how to synchronize Active Directory user with a spreadsheet using PowerShell. In this video, Josh will walk you through how to create, and update, users based on information from a spreadsheet. We’ll be using Doug Finke’s ImportExcel PowerShell module to read our spreadsheet and the New-ADUser and Set-ADUser cmdlets from the Active Directory module. Prerequisites include: The Active Directory Module from the Remote Server Administration Tools (RSAT). Doug Finke’s ImportExcel PowerShell Module.
takes naps the Human Resources Department seems out a weekly staff changes spreadsheet and we need to Automate. The Eds Moves and changes that are indicated within it. Will do this by creating our own advanced function? Whoever let's 1st make sure that these changes haven't already been actioned of pulled out a selection of the changes from within that spreadsheet and will quickly run through these tests to ensure that they haven't already been done so. First, let's check that Dell our new user isn't already present and Active Directory, which the error indicates that he is not next. Let's look at Mabel. She's come off of a fixed term contract and should no longer have an account expiry date. However, when we look at her account. She does and Conversely candy to his resigned so she should have an account expiry date. Which she doesn't? Britain has been promoted and his title should now be senior cost accountant, however, his title is still currently cost accountant. And finally pyre has changed reporting lines her Department should now be engineering and who manager should be Austina. However. She's currently in the Legal Department and reporting to Ed. Salute start building our advanced function will start by declaring the function and naming it in this case sync staff changes. And I've also noted at the top this function requires the import Excel module. This module is what will be using to import the Excel spreadsheet as usable data and powershell. Next we'll make this in actual advanced function using the commandlet binding keyword and clearing 3 parameters path is simply the location of the Excel Spreadsheet Bay. So U is the OU under which all of our users will be created and the company is of course, takes naps. These are being parameterized so that they can be changed. Easily, however, I've given based so un company a default value as they won't change for my use case. Next I'll explicitly import the import Excel Module and then use the import excel function to import the data from my excel spreadsheet. The data in my spreadsheet starts at row two. So have indicated that and sometimes empty rows are left at the bottom of the spreadsheet. So the data only switch says that if a row is empty to exclude it. Next oh start looping through each staff member in that excel daughter and old to find some common variables for each as we run through them. So I want to know their employee code as a string rather than the value that it would be naturally, which is a double and I'll use this employee code to find out if that user already exists in Active Directory. I also want to know the users manager and I want to get their AD user object. I'll do this using the managers user ID column. From the Excel spreadsheet. Now I want to know if that user does exist in Active Directory. And if it does, I'm going to update it so if the user does exist alright of verbose message indicating that it's been found, and that I'm proceeding to update it or define the desired. OU for that user which is actually the name of the Department under our base. So you and then I'll sit the description and title to match the staff members indicated position. V Department in their manager will both be updated here as well. If the spreadsheet indicates that the staff member has an. Indict IE there on a fixed term contract or they have resigned. I'm going to sit the account expiration date. And if the spreadsheet doesn't indicate an in note. I'm going to set it to null this will cancel any existing account expiration dates that might already be in Active Directory. Now all update the user using these set ad user commandlet. And supplying that existing user object, and if the user isn't in the OU for their Department will move the ad object into the correct location. We do this by using the existing users distinguished name, and the desired OU, that we calculated above know if the user doesn't already exist. We're going to workout. Some more variables for that user once again will determine the OU but now we're also going to create the users. Email address UPN. And full name and will also create a password in this example. I'm using a static password for all users in the real world. You would want this to be a randomized password now create a hash table of all of my parameters that we used to create this new user and again if the Excel spreadsheet specifies an in date. I'll add that to the Hashtable. Finally, I'll pass that Hashtable 2 knew ad user and this will create my user as specified. By load this function and memory and then execute the changes. It works way for a moment and then returns. Let's circle back to our tests that we started with and see that the changes have been applied so Adele our new user should exist in Active Directory, which he does. Mabel should not have an expiry date. Which he does not kendeda should have an expiry date? What she does Britains title should now reflect his promotion and he is now a senior cost accountant and finally. Pie is being moved to another Department. Endo you SH should reflect it in new managers should be updated and as we can see her Department has been updated her distinguished name reflects the new OU and who managers also changed with now successfully synched our changes from a excel spreadsheet and have repped our script up into a function which we can use as often as we need.