Let’s play powershell part 1: Updating Active Directory users from a CSV file

** This has turned into something of an epic, if you just want the script it’s here or at the bottom of this post! **
Fairly reasonable request from a client today – they’d like to tidy up and add to the data stored about users in Active Directory by supplying a CSV file containing the users and the properties that they should have. The CSV file looked something like this:

First name:,Last name:,Email:,Office Landline:,Extension:,Telephone number:,Job Title:,Division:
Joe,Bloggs,Joe.Bloggs@example.co.uk,01234 567 890,123,09876 543 210,,Big Top
Dave,Bloggs,Dave.Bloggs@example.co.uk,01234 567 891,124,09876 543 211,Head Lion Tamer,Mangaerie
Pete,Bloggs,Pete.Bloggs@example.co.uk,01234 567 892,125,09876 543 212,Clown Administrator,Custard Pie Workshop
Rob,Bloggs,Rob.Bloggs@example.co.uk,01234 567 893,126,09876 543 213,Tightrope Calibration Technician,Big Top
Jim,Bloggs,Jim.Bloggs@example.co.uk,01234 567 894,127,09876 543 214,Stilt Walker,Big Top

There’s a few guides around to doing this, however I didn’t find any of them particularly pleasing because they all rely on manually mapping the CSV file colums to Active Directory User Objects on a case-by-case basis. If they CSV file is correctly formatted this shouldn’t be necessary. The requirements, as I see them, are that:

  • There should be at least one field in the CSV file that uniquely identifies a user in active directory. The usual property used for this purpose is UserPrincipleName but it should also be possible in small environments to use the user’s full name, firstname and surname or email address.
  • All the fields in the CSV must have names that match exactly the Active Directory User property that they relate to.  There’s a list of these here.

I also used these criteria while creating the script:

  • If the field for the user in question is empty in the CSV file, don’t update that property.
  • If an active directory user can’t be found for a given record in the CSV file, write a line to the console to say so and move on to the next record.
  • If a field in the CSV file doesn’t map to an Active Directory User property write a line to the console and move on.

With that in mind, I initially tweaked the CSV file to meet my criteria by renaming some colums:

GivenName,Surname,EmailAddress,OfficePhone,Extension,MobilePhone,Title,Department
Joe,Bloggs,Joe.Bloggs@example.co.uk,01234 567 890,123,09876 543 210,,Big Top
Dave,Bloggs,Dave.Bloggs@example.co.uk,01234 567 891,124,09876 543 211,Head Lion Tamer,Mangaerie
Pete,Bloggs,Pete.Bloggs@example.co.uk,01234 567 892,125,09876 543 212,Clown Administrator,Custard Pie Workshop
Rob,Bloggs,Rob.Bloggs@example.co.uk,01234 567 893,126,09876 543 213,Tightrope Calibration Technician,Big Top
Jim,Bloggs,Jim.Bloggs@example.co.uk,01234 567 894,127,09876 543 214,Stilt Walker,Big Top

Active Directory doesn’t have a user attribute for telephone extension, however a few different people are suggesting that if the extension is stored with the landline number, separated by a comma (like this: “01234 567 890, 123”), many phones will automatically dial the number, pause for 3 seconds, then dial the extension. This seems like the best approach but does involve storing a value containing a comma in the CSV file so it will need to be quoted. So the again-modified CSV becomes:

GivenName,Surname,EmailAddress,OfficePhone,MobilePhone,Title,Department
Joe,Bloggs,Joe.Bloggs@example.co.uk,"01234 567 890, 123",09876 543 210,,Big Top

That looks like a suitable CSV file to import now, so let’s start writing some PowerShell.

The first stage is to get the data from the CSV. This is made trivial by the PowerShell cmdlet Import-CSV. This parses a well-formed CSV and creates from it what Microsoft refer to as a “table-like object”. In real terms, it gives us a collection of custom objects that can be iterated over, such that each object has properties named after the columns and containing the data from the CSV file.

Let’s run that a few times and see how it actually works.

First we’ll just call Import-CSV with our CSV file:

PS C:\Users\Administrator> Import-Csv C:\Users\Administrator\AD-user-update.csv

GivenName    : Joe
Surname      : Bloggs
EmailAddress : Joe.Bloggs@example.co.uk
OfficePhone  : 01234 567 890,123
MobilePhone  : 09876 543 210
Title        :
Department   : Big Top

GivenName    : Dave
Surname      : Bloggs
EmailAddress : Dave.Bloggs@example.co.uk
OfficePhone  : 01234 567 891,124
MobilePhone  : 09876 543 211
Title        : Head Lion Tamer
Department   : Mangaerie

GivenName    : Pete
Surname      : Bloggs
EmailAddress : Pete.Bloggs@example.co.uk
OfficePhone  : 01234 567 892,125
MobilePhone  : 09876 543 212
Title        : Clown Administrator
Department   : Custard Pie Workshop

GivenName    : Rob
Surname      : Bloggs
EmailAddress : Rob.Bloggs@example.co.uk
OfficePhone  : 01234 567 893,126
MobilePhone  : 09876 543 213
Title        : Tightrope Calibration Technician
Department   : Big Top

GivenName    : Jim
Surname      : Bloggs
EmailAddress : Jim.Bloggs@example.co.uk
OfficePhone  : 01234 567 894,127
MobilePhone  : 09876 543 214
Title        : Stilt Walker
Department   : Big Top

That shows that PowerShell has correctly understood the CSV file, and created a collection of objects which certainly appear to have the right properties.

Let’s have a closer look at the actual objects that get created in this process. We’ll run the Import-CSV again, but push the results into a variable called $users:

PS C:\Users\Administrator> $users = Import-Csv C:\Users\Administrator\AD-user-update.csv

What’s been created here is actually an array of objects, and we reference individual items in an array using [] square brackets and the zero-based index of the element we want:

PS C:\Users\Administrator> $users = Import-Csv C:\Users\Administrator\AD-user-update.csv
PS C:\Users\Administrator> $users[0]
GivenName    : Joe
Surname      : Bloggs
EmailAddress : Joe.Bloggs@example.co.uk
OfficePhone  : 01234 567 890,123
MobilePhone  : 09876 543 210
Title        :
Department   : Big Top

PS C:\Users\Administrator> $users[5]
PS C:\Users\Administrator> $users[3]
GivenName    : Rob
Surname      : Bloggs
EmailAddress : Rob.Bloggs@example.co.uk
OfficePhone  : 01234 567 893,126
MobilePhone  : 09876 543 213
Title        : Tightrope Calibration Technician
Department   : Big Top

Notice that as we only have 5 records in our CSV the highest valid index is 4, and calling $users[5] returns nothing.
So now we can reference a particular user entry from our CSV individually. The next thing to do is see what properties that object has, using the Get-Member cmdlet.

PS C:\Users\Administrator> $users[0] | Get-Member
   TypeName: System.Management.Automation.PSCustomObject
Name         MemberType   Definition
----         ----------   ----------
Equals       Method       bool Equals(System.Object obj)
GetHashCode  Method       int GetHashCode()
GetType      Method       type GetType()
ToString     Method       string ToString()
Department   NoteProperty System.String Department=Big Top
EmailAddress NoteProperty System.String EmailAddress=Joe.Bloggs@example.co.uk
GivenName    NoteProperty System.String GivenName=Joe
MobilePhone  NoteProperty System.String MobilePhone=09876 543 210
OfficePhone  NoteProperty System.String OfficePhone=01234 567 890,123
Surname      NoteProperty System.String Surname=Bloggs
Title        NoteProperty System.String Title=

This shows use a lot of useful information about the object we’ve created; giving use all the methods and properties of that object.
As an aside, note that the methods (Equals, GetHashCode etc) are methods inherited from the base Ojbect class. Every object in PowerShell has these methods as they are used for object reference and comparison. Because PowerShell follows the “Everything is an object” paradigm of coding, comparing 2 objects involves invoking the “Equals” method of one object with the other object as a parameter, and the specific implementation of “Equals” – the actual code that does the comparison – can be specified by the object itself. That’s not important to us for this task, but explains where and why these methods came from when they’re not referred to in any way in our CSV file.

Now we can directly access the relevant properties of users from the CSV file:

PS C:\Users\Administrator> $users[0].GivenName
Joe
PS C:\Users\Administrator> $users[0].EmailAddress
Joe.Bloggs@example.co.uk

That looks to be a great starting point for this script. Once we’ve got an array of users we’ll want to iterate though them and update our actual active directory users:

$users = Import-Csv C:\Users\Administrator\AD-user-update.csv
Foreach ($user in $users) {
    #find the user
    #update the user's properties
}

So how to find the user? The appropriate cmdlet is Get-ADUser. Given the data available, I’ll be using the email address attribute to find the users, thusly:

 $adUser = Get-ADUser Get-ADUser -Filter {EmailAddress -like $user.EmailAddress}

This will bind the $adUser variable to the user from Active Directory whose email address matches the email address of the current line in the CSV file. All that remains is to use the Set-ADUser cmdlet to update the properties of the object now bound to $adUser.

No Comments Yet.

Leave a Comment

*