How to work with a CSV of usernames in PowerShell

PowerShell Snobs will tell you that working with a CSV file in PowerShell isn’t best practice, but sometimes that’s all you’ve got to work with. Today, I’ll show you how to do that.

Working with PowerShell… it do be like that sometimes… Photo by JESHOOTS.COM on Unsplash

PowerShell.

I love PowerShell.

If you’ve read this blog for any amount of time, you probably know that. I sing its praises often enough, but the truth is that PowerShell can sometimes be…

Power Hell.

One of the main issues is that it’s not always obvious why something isn’t working. To make things worse, when you try to Google your error, you’ll often have self-appointed PowerShell gurus try to help, fail, and then say “doing it this way isn’t best practice.”

Yeah, thanks for nuthin’!

I ran into this scenario this week, so I’ll walk you through it today as it’s a great learning opportunity. What we’ll be doing is pulling a list of usernames from a CSV file and seeing if the users are active or inactive. (If you’re unfamiliar with what a CSV file is, it’s a “comma separated value” file. It’s just a list of items separated by commas. When you start your IT career, you’ll be working with these pretty often.) If you want to see the completed script and don’t want to read the whole article, just scroll down to the end.

So first, let me momentarily agree with the “PowerShell Snobs”: if you’re working with Active Directory and PowerShell, using a CSV file isn’t actually best practice. You lose a lot of functionality when you do this. Going into an explanation about objects and how rich with data they are compared to a list of words is beyond the scope of this post, but you can learn all about it by reading PowerShell in a Month of Lunches (that’s an affiliate link – thanks for supporting this blog!) which is how I learned how to use PS. If you’ve already started working in the field, this is probably the most important thing you can learn early on as PS interfaces with just about every other Microsoft product out there. (If you’re not in the field yet, consider reading the A+ book (also an affiliate link) first as you may not quite be ready for PowerShell yet. I wrote about why in this post about how to prepare for your job interview.)

Alright, now that we’ve agreed that using CSVs for manipulating user objects in PS is not best practice, can we all agree that sometimes it’s necessary? If your CTO (Chief Technical Officer) emails you a CSV file of users, can we agree it’s not “best practice” to tell him/her that you’d rather they create an Active Directory group with those users in it so you can follow best practice?

Great.

Now that we’ve got that disclaimer out of the way, let’s take a look at my situation this week. The ask was simple enough: I was provided a list of user names and I needed to quickly see which accounts were inactive. The file looked like this, but with around two hundred entries:

Samaccountname

Jsmith

Jdoe

Prabbit

Sclaus

It’s simple enough to loop through a list in PowerShell, so I thought this would a five minute task. But it was not to be. Read on.

The first thing I did was write a simple script. (Fair warning, this one doesn’t work. The one at the end of this post does.)

$Users = Import-Csv C:\Users\SiliconPath\Desktop\Users.csv

Foreach ($user in $Users) {

Get-ADUser –identity $user –properties * | select samaccountname,enabled

}

Walking through that in plain English:

Pull the list of users I have in this CSV called “Users.csv”. Then, go through each user in the list and print out the username and if it is enabled or not.

Delving a little deeper, the first line imports the contents of the CSV file into a variable called $Users. Then, the foreach statement goes through each line of the file. For each user in the users CSV file, it will use the Active Directory command “Get-ADUser” and look at the fields “samaccountname” and “enabled.” SamAccountName is Active Directory’s way of saying “username.”

I don’t actually have to tell PowerShell to print the results to my screen because that is the default behavior. But if I’d wanted to, I could have printed the results to another file, to a printer, or in a web page format.

Unfortunately, when I ran that script, I got this error repeated two hundred times:

Get-ADUser : Cannot validate argument on parameter ‘Identity’. The Identity property on the argument is null or empty.
At line:5 char:22
+ Get-ADUser -identity $user -properties * | select samaccountname,enab …
+                      ~~~~~
    + CategoryInfo          : InvalidData: (:) [Get-ADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.GetADUser

Not to worry, I thought smugly. I’m an old hand at PowerShell. I’m pretty sure that the issue is that PowerShell doesn’t realize that I’m giving it usernames in the variable. This is why it’s telling me that the parameter “identity” is empty. So I tried adding this in at the top:

[string]$user = “”

Basically, I’m telling PowerShell that the stuff in $user is a string. But there’s nothing in there yet, hence the empty quotes.

I hit F5 to run the script again aaaaaaaaaand… 200 more errors.

I typed $users at the prompt and hit enter. Sure enough, my two hundred user names printed out nice and neat. So it definitely wasn’t empty… But it did print with a header. Maybe that was the issue? I tried dropping the header, but no joy.

At that point, I did the only reasonable thing left to me: I ripped out a few hairs out of my head. I then tried a few more things. Then, there may or may not have been some cursing.

At this point, I thought I’d made a good enough effort (hey, that’s how you learn, right?) so I just went on Google and asked it to tell me the answer.

It turns out that a lot of people have had this issue, and there didn’t seem to be an answer online (other than the PowerShell snobs saying “don’t do it that way.” But I had to do it that way. All I had was a CSV…)

Now, to thank you for being a loyal reader of my blog I will reveal to you a PowerShell secret so PowerFull it will change your life forever: when you hit this point in your script writing, simply give up and go ask a developer/software engineer/whatever they call the people who write code all day at your workplace. Which is exactly what I did. It will go something like this:

You: “Hey, can you help me figure out this error?”

Big Brain: (Looks at code for a moment.) “It looks like (insert unintelligible jargon and gibberish here).”

You: (Nodding along sagely.) “Ah, I see. And… how would I go about fixing… that stuff you just mentioned?”

Big Brain: “Just add .samaccountname right there.”

And suddenly it just works!

Here is the full working script. You just need to update the path of the file to where you are saving your file, make sure your file is a .csv file, and that it starts with samaccountname as the first field. Then, it should run fine for you (assuming you have the Active Directory module installed, of course.) I’ll also throw in my usual warning about PowerShell here: PowerShell can be quite unforgiving, so make sure you understand what a script does before simply copying it from the Internet and running it in your production environment!

I hope this article saved you from a bit of frustration! If it did, show some love in the comments!

$Users = Import-Csv C:\Users\SiliconPath\Desktop\Users.csv

Foreach ($user in $Users.samaccountname) {

Get-ADUser –identity $user –properties* | select samaccountname,enabled

}

With each post, I cover a new topic to help you get your start (or keep progressing) in your IT career. If it’s your first time visiting this blog, start here. Or, see all my posts about interview questions you should definitely be prepared for.

Author: Silicon Wanderer

I'm a merry wanderer on the path to financial independence through IT. I'm doing it, and I want to show you how you can to!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s