The Surly Admin

Exporting User Information

Need to export user information to a CSV?  Perhaps as part of your termination user process?  Incredibly easy with PowerShell, let’s see how to do it.

Quick Side Track

Before we dive in, just a quick note.  From time to time I get requests to write scripts for people.  And while I’d love to just sit here all day and write free scripts for people, I do have to put food on the table, and occasionally play with my daughter–who, for some bizarre reason, seems to enjoy this.  This post is actually a response to this kind of request, and I’ll be honest you’re not typically going to get this out of me unless you’re willing to become a paying customer!

But this request is easy, and is a great way of demonstrating how to use PowerShell, cmdlet’s and the power of objects.

The Setup

We need a script that can export select fields from a user and save it to a CSV.  Additionally, we’d like to be able to run the script against a whole list of users if needed.  Obviously, you can take this and expand it to do a lot of the work for you but let’s keep it simple for now.

First thing, I’m going to need to craft the Get-ADUser cmdlet to display the fields I want.  The beauty of PowerShell is it’s ability to run each small step of a script independently, so you don’t have to kill yourself writing test scripts.  To start we need to display group memberships, Email, Last Logon Time, Description, Organization and Manager name.  Last Logon Time is interesting, here’s why, but we’re just going to bypass this and go with the baked in field, it should be accurate enough.

 

That’s pretty good, but there are a few problems.  Get-ADUser has a few default properties and those are in there, but MemberOf is an array so not too good for a CSV, not to mention all of the groups are in FQDN format which isn’t to user friend.  The Manager field has the full FQDN too, so I’ll need to deal with that too.  I’m going to use Select for this, and I’ll have to modify some fields on the fly.  To do this I have to use calculated properties, which is a technique for making custom properties.

First problem is groups is an array, and it’s the FQDN of the group, not just the friendly group name.  We’ll need to fix that:

A lot going on with this little calculated field, let’s break it down:

1. This is the name of the calculated, or custom, field.
2. I’m taking the MemberOf property from the user object, and using ForEach to enumerate through each value in that array.
3. Then I use Get-ADGroup to get the group object for the group from Active Directory.  Surround that in parenthesis to make sure PowerShell returns an object, than reference a single property in that object.  In this case the Name property.  This will be the friendly name of the group and not that ugly FQDN.
4. Notice this parenthesis?  I have an opening one all the way at the beginning of the Expression section.  This takes all of the group names that have been retrieved and puts them all together, PowerShell will kindly make it an array for us too.
5. Once the new array is created I use the -join operator to flatten it into a single string and use a comma followed by a space as the delimiter between the elements in the array.

Next up, I need to do something similar with the Manager field.  It too is a FQDN so I need to query AD for the friendly name.  It’s a little easier in that this is only a single value so no goofy ForEach looping.  I’ve actually covered Manager before here (and a few other places), so here’s what our calculated field would look like:

You may have noticed the use of the pipeline object ($_ and in PowerShell 3.0 and later you can use $PSItem) in both of these?  This represents each element in the array we’re piping into Select-Object.  Giving us full access to each object so we can make these kinds of fields and other calculations.  That’s really all the extra processing I needed, here’s the final result:

This is where I think Select has its biggest problem.  With large lists of fields it quickly scrolls off the screen and becomes difficult to read.  Still, being able to do everything in one swoop like this is hugely powerful, so something we can live with.  Now we have the results we need, so time to export it to a CSV.  But, since this is still an object we aren’t limited to CSV.  Out-Gridview, Format-List, Format-Table, ConvertTo-HTML, ConvertTo-CLIXML, ConvertTo-AdvHTML (that’s mine) are all on the table.  When I, and other PowerShell people, talk about the power of objects, this is what we mean.  Most cmdlet’s in PowerShell are designed to use objects, and that means we can do all those things with them!   Since this is an archive of user information I think it’s best we save it to a single CSV and just append every time.  We’ll use the -Append parameter that comes with Export-CSV to accomplish this.  The -Append parameter is new with PowerShell 3.0 so you’ll need to upgrade to use this script.

Assuming all the code above, put this at the end of it:

The nice thing about the -Append parameter is that if the file doesn’t exist Export-CSV will still work and just create the CSV for you, but if it does exist it will append your data to the end and move on.  So for a single user, we pretty much did it, right?  Let’s add some named parameters so you don’t have to edit the script in order to use it every time.  I also added a variable with the path to the CSV we’re creating.  You can now edit that and have it save wherever.

Now run Export-SingleUser.ps1 -User Martin9700 and it’ll archive Martin9700’s information to your CSV.

Now Multiple Users

Notice the inward going outward style of script writing?  This is how I write all my scripts.  I find the base problem and solve it (in this case Get-ADUser and Select) then begin layering on more complexity as I go.  The advantage to this style is you start with a small script and solve your base problem, then in theory the things you’re layering on later are named parameters, error trapping, reporting, etc which is typically the easier thing to work with.  If you write the entire thing in one go, you then have to troubleshoot the whole thing and maybe find a fundamental problem which requires you to rewrite the base code which in turn forces you to rewrite the entire script.  Better to start small and get bigger once that base problem is done.  Of course, your mileage may vary!

The next complexity is dealing with multiple users.  I can just wrap our script above in a loop so that part should be pretty easy, but the hard part is how do we get the list of users into the script?  I could put a simple Get-Content in script, right?  But then I have to edit that file each time, maybe even edit the script to point to a new file and one of the things we try to avoid in PowerShell is the need for the end-user (even if that’s you) to edit the script.  Wouldn’t it be nice if the script could be written to accept data from the pipeline?  Then it could use the same conventions as any other cmdlet out there!  Never saw that coming, right?

Just so happens I’ve written about doing this before, so go ahead and read it, I’ll wait.  So we need an argument decorator in our Param section to tell PowerShell it’s ok to receive data from the pipeline.  I then need to use the Begin/Process/End scriptblocks to handle the influx of data.  Let’s start:

So I use the Parameter decorator, and simply designate that it can get its value from the pipeline.  If you’re still running PowerShell 2.0 you will have to alter that to read ValueFromPipeline=$true, but since this script is already PowerShell 3.0 only, we can take advantage of this little shorthand.

Let’s think about how this script works, so we can determine how to use the Begin/Process/End scriptblocks.  The script is getting a list of users from the pipeline, but they come in one at a time.  The workflow is the script will run the Begin scriptblock before any data from the pipeline is processed, then it will process each user individually in the Process block and finally run the End block when it’s all done.  Since $Path only needs to be defined once, we can put it in the Begin block.  As for the End block there’s nothing for us to finish up with so I think we can skip it entirely.

That’s it.  We now have a simple PowerShell script that solves the problem at hand.  We used objects and an inside-out development process to get there.  Calculated fields to make the output pretty and full pipeline support to either have a single user or multitude of users!  How would pipe information to it?  Here are a couple of examples:

 

 

 

 

 

 

July 14, 2014 - Posted by Martin9700 | Powershell - Getting Started | Calculated Properties, Get-ADUser, Objects, pipeline, Select-Object