Because you need to know PowerShell

Hey – hope you’re doing okay.

I want to take a few minutes to get you started with PowerShell, in a way that I think is less overwhelming than how I found most PowerShell material back in the day. Because you’ve told me you keep hearing about PowerShell, and that you can’t tell how to get into it.

The first thing to know is that it feels like Command Prompt on steroids, but a lot more useful than Command Prompt. You’ll see what I mean as soon as you open it. Let’s do that, but let’s open it using ‘Run as Administrator’, because that just helps for some of the demos. In real life, you’ll only open it with Admin rights if you really need to.

So click on the start button and start typing PowerShell. I don’t want you to open the ISE (yet), just find the “Windows PowerShell” shortcut, and right-click on it to choose ‘Run as Administrator’.

Now that it’s running, pin it to your Taskbar, so that you can access it more quickly next time. If you’re going to try to embrace PowerShell, you should make it easy to open.

Anyway, that window… straight away it feels like you need to have a clue what to write. Yes, that’s right. Things might’ve been a little easier in the ISE, and Aaron Nelson (@sqlvariant) has told me off for not using the ISE every time I do anything in PowerShell, but we’ll be okay here.

Let’s start by doing some basic things that we know from DOS. The commands cd and dir. Try this:

cd ..
dir

You’ll see that you move up to C:\WINDOWS, and then list the contents. No surprises here, it’s just like Command Prompt. Or DOS if you’re old enough.

The difference with PowerShell is that this is not just text output. These things are objects. And we can do stuff with that. PowerShell is a ‘typed’ environment. You already deal with objects all the time, like in Object Explorer in SSMS. PowerShell objects can include all those things, as well as .Net objects. This is where PowerShell gets its power from.

A few quick things…

1. PowerShell commands come in verb-noun pairs, such as Set-Location or Get-Content or Get-ChildItem. These are known as cmdlets, so when you see the word ‘cmdlet’, it just means a PowerShell command.
2. PowerShell command parameters use a hyphen. Like: Get-ChildItem -Recurse but you can also abbreviate them so long as there’s no ambiguity. So I could use -rec instead of -recurse.
3. Variables start with a dollar sign ($). We’ll get to this later.
4. We don’t use > in filters and tests, we use -gt. -eq for =, -ne for !=/<>, -le for <=, and so on. This is frustrating, but you’ll get used to it.
5. There are a bunch of abbreviations and aliases. One alias for Set-Location is cd. Get-ChildItem is also known as dir, and ls too. Where-Object is ? and ForEach-Object is %. We’ll see these as we go along too.
6. You get tab-completion on almost everything, so if you’ve typed Get-Chi, you can hit Tab and cycle through the options until you find what you want.
7. You can use help (which is an alias for Get-Help) by simply using: help dir, and at the bottom of that help, you’ll get a nice reminder that you could’ve used help dir -examples, and that adding -online would have opened a web page with the relevant help page on it (that’s really handy).
8. You can pass the results of one thing into another using the pipe symbol (|). Perhaps the most useful one is gm (an alias for Get-Member), which will list the Properties and Methods (the members) for whatever object you’ve passed in.
9. # will comment the line, or go between <# and #>

Let’s see some of this in action.

Here we can see some interesting things. Good to notice that we could use -Exclude and -Include. We’ll do that soon. We could get more information using -detailed, or -examples, or -full.

Let’s explore our SQL folder. Start by typing cd \p and then hit Tab until it shows you Program Files.

Now hit M and start hitting Tab again to find ‘Microsoft SQL Server’. If you go too far, press Shift-Tab to go backwards. (Oh, and if you should’ve been looking on D:, then start again with cd D:\p – but the principles are the same.)

Great. Let’s hit Enter and see that we’ve changed directory.

But now let’s type: dir -rec -inc ConfigurationFile.ini

You could’ve hit Tab after each parameter and seen them expand into the more correct versions. You could’ve used Get-ChildItem to be slightly stricter. I don’t really care though. When you run this, you’ll see some files listed. I don’t know how many you’ll get. Each one indicates that you ran an installation of SQL Server. But as a SQL expert, you know this. You just might not have known how easily PowerShell could list them for you.

Ok. Hopefully we’re seeing some use for this.

Now let’s do something with this output. I want to say ‘with these files’, but let’s do the data thing and think of this as a dataset of files.

Suppose we want to count them. I could loop through the results using ForEach-Object (but I’ll use the shortcut %), and increment a variable that I set up. This is a common pattern for looping – people put whole programs inside those curly brackets, across lots of lines.

This does the trick, but I’m sure there’s something built-in to let me count things.

Well, I can never remember what that command, so let’s do ask one of those fancy internet-search providers.

I don’t even want to open these pages. I just want to notice that there’s a command called Measure-Object. Let’s pipe our output into that. Hit the ‘up arrow’ key to go back to a previous command.

This takes a moment, and then happily tells us there are three.

It takes a moment because it’s having to search through the files again. How about we store those results somewhere?

Now, I call these things brackets, but Americans call them parentheses. And what you call brackets, I call square brackets. There are curly brackets too, which you call braces I think. I’ll try to call parentheses ‘round brackets’ to avoid confusion, but if I refer to a ‘bracket’, I mean a parenthesis.

I’m going to wrap my dir command in round brackets, and assign that to a variable.

Now I can pipe $configfilelist into Measure-Object and it will be more responsive, because I’ve stored my results.

But because $configfilelist is an array of files, I can also look at its length to see that there are three items. That’s way easier. And I can access each individual one by using square brackets from 0 to 2.

This isn’t exactly ground-breaking stuff yet. But those files do tell us some useful things. Let’s do a Get-Member command on our array.

Don’t get put off by this. At the top we can see that we’re dealing with objects that are of type System.IO.FileInfo. This is a .Net thing and is where the power comes from. We can see that we could call the Delete methods of these files, or CopyTo, or Decrypt (if they were encrypted, but they’re not), and more. Down the bottom we see Properties.

This is more interesting. Let’s look at LastWriteTime. Because we might be interested in when that is.

We can use Select-Object to show only certain properties.

As expected, nothing’s written to those files since the installs were actually done. But what if I wanted to filter this, only to ones since a particular point in time?

I’m going to use Get-Date to get the current date and time. I’m going to wrap that in round brackets and use its AddYears method to find a year ago. In the real world I’d be more inclined to use AddDays, but I do want to get some results.

And I’m going to filter $configfilelist by passing into Where-Object (except that I’m going to use a convenient shortcut which is the question-mark). My filter lives inside curly brackets (‘braces’, right?), and inside those curly brackets I refer to the things in $configfilelist using $_.

So my filter goes like this, saying that I want to list the ones that have a LastWriteTime greater than (-gt) a year before now:

And I only get one file. Terrific.

That’s all well and good, but now I want to know what that installation was about. For that we need to look inside the file. Get-Content is our friend here. Let’s start by using Get-Content against a file directly. It’ll be easy to hook it into our proper command later.

Get-Content actually returns an array of lines, and has really useful parameters like -TotalCount which stops after a certain number of lines, or -Tail which gets lines at the end of the file. I love this when I’m dealing with large files – it’s not like I can open them in Notepad.

Let me tell you about Select-String too. It’s like grep from Unix – you use it to get the lines you want in a file.

That output seems like a pain, but remember this is just the string representation of an object. If I pipe my command into gm (Get-Member), you’ll see what I mean.

So now I can tell that I can access the Path of the file, the Line, the LineNumber, etc., separately.

To look for multiple things, I want to pass an array of parameters to -Pattern. I do that by simply using a comma, to discover that it was an install of Analysis Services.

Hopefully you’re starting to see the power of this.

I can drop it all into the one line by using:

dir -rec -inc ConfigurationFile.ini | ? {$_.LastWriteTime -gt (Get-Date).AddYears(-1)} | select-string -Pattern 'FEATURES=','ACTION=' | select-object Path, Line

(Sorry, you'll likely have to scroll sideways to see all that – getting everything on a single line isn't exactly a virtue in HTML.)

When you’re working things out that you like, you’ll want to store the commands somewhere for later (such as in a .ps1 file that you run). For that, you want to be aware of Get-History. It lists everything you’ve run in that session.

And a neat little trick is that you can use negative numbers to hook into arrays from the other end. So you can do this:

But instead of just displaying it, pipe it into clip:

…which gets it onto the clipboard.

I’m not going to go much further with this, but you could keep push this further and further yourself, exploring the types of things that might be interesting in your job. You can dump results into files using > and append to files using >>.

Now consider commands like:

Get-EventLog -LogName Application -After (Get-Date).AddHours(-1) -EntryType Error

…and imagine what you could do with that.

Try doing: Import-Module SQLServer

Which then lets you go:

cd SQLSERVER:

And browse the SQL environment as if it’s a file system.

I don’t tend to use PowerShell to explore SQL like this very often, I must admit. But the fact that I can certainly gives me a bunch of options. The fact that I could use PowerShell to create a Word document like they do over at https://learn-powershell.net/2014/12/31/beginning-with-powershell-and-word/, or send emails as described at https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/send-mailmessage, or call web services (include Azure Machine Learning ones) as described over at https://docs.microsoft.com/en-gb/powershell/module/Microsoft.PowerShell.Utility/Invoke-WebRequest means that with a bit of imagination, you could use PowerShell to do just about whatever you want. I love using Invoke-SqlCommand to get data out of SQL, push it into Invoke-WebRequest to get scores from Azure ML, and then respond accordingly. PowerShell makes it really straightforward.

Oh, and to run PowerShell commands on other machines (which can be quite useful, of course), a quick ‘internet search’ showed me this useful-looking page: https://www.howtogeek.com/117192/how-to-run-powershell-commands-on-remote-computers/

You could have a list of computers inside a file, and loop through it like this:

Try some stuff. Knock yourself out. And explore what everyone is writing for T-SQL Tuesday this month, hosted by Rob Sewell (@sqldbawithbeard)

@rob_farley