Finding What You Need in PowerShell
In the last article, PowerShell Basics for DBAs, we talked about how PowerShell is organized as a set of drives that are accessible through psdrive. We also talked about cmdlets and how they take parameters. This time we’re talking about help, get-member, and format-table. These are going to be invaluable in your day to day PowerShell work. So let’s go ahead and get started.
One of the main ways you’ll learn to work with cmdlets is by using help. If you type help by itself at the prompt you get help about help. However, if you type help followed by a specific cmdlet, you’ll get help about that object like this:
If you look at the bottom of the above screenshot you’ll see in the ‘Remarks’ section that you can pass parameters to help. Sifting through the examples of each cmdlet is one of the best ways to learn about the different uses and functionality of it. Besides running help on a specific cmdlet, you can also search for cmdlets to learn about what’s available in PowerShell. You can run wildcard searches like this:
This will show you all the cmdlets that start with ‘get’.
Probably the most useful cmdlet for getting output to the screen is format-table. Its meaning should be clear; it tells PowerShell to format the output as a table to the console. Let’s take a look at how this works. Type the following at the prompt:
>get-service | format-table
Here’s what you’ll get:
Funny, it looks just like it did above when you only typed ‘get-service’, doesn’t it? That’s because the default output type for get-service is format-table. So why did I use it as an example then? Well, because along with format-table being the default output, the default columns of that table are Status, Name, and DisplayName. What if you wanted to see other information about the services? This s where format-table comes into play. With format-table you can define the columns you want to see in your output because let’s face it, the default output may not be what you’re interested in. Let’s take a look at how to get different information about the services:
>get-service | format-table MachineName, Name, Status, CanStop –auto
You’ll get this in return if you run it on your local box:
So this is pretty easy to understand. You use get-service to get a list of services, and pass the output to format-table and then list the columns you want to be displayed. The ‘-auto’ parameter tells format-table to adjust the width of the columns based off of the data instead of a fixed width.
Without it the output would look like this:
See, without the ‘-auto’ parameter the output stretches across the page which makes it harder to stay on the same line as you cross the screen. So using ‘-auto’ is always desirable. That’s not all you can do with format-table, but it’ll do for now.
Of course now the big question is how do you know which columns are available in the above example? I mean, where did I get the new columns to display? The answer is get-member. PowerShell is a .Net-based language so every object in the pipeline is an object. And in .Net, objects have members. To put it succinctly, an object’s members are just the methods and properties that the object supports. Let’s take a look at an object’s members and you’ll see what I mean. Since we started with get-service, we’ll use it here too:
>get-service | get-member
This will yield:
In the output above, methods can be thought of as functions, or verbs. A method is something you can do to an object. Looking at some of the methods, you can Start, Stop, and Pause a service. We’ve already seen methods in action in the first article (PowerShell basics for DBAs). Properties are the pieces of information you can view about an object. And in our example, properties translate directly into columns for our format-table. You can see the properties that I chose for my format-table above are in the property list. How about another example real quick? This time we’ll do get-process because it has lots of properties to choose from.
>get-process | get-member
I can’t show you all the properties because I wanted to show you some methods too, but you’ll get a good idea here. Starting with a glance across all the methods (remember, these are things you can do to each process) you see you can kill, start, and close processes. And as for properties, you can see the properties you can use as columns in your format-table. Using a combination of help and get-member are 2 of your biggest tools when working with PowerShell. I’ve been using PowerShell for years now and I live in get-member constantly. And the members that come up in the list will change depending on the object. And that makes sense because if you look at the members for a string, you won’t have the same types of things you can do as you could for an integer or even a date. There’s more to say on all of these topics and we’ll cover them in future articles.
More in SQL Server
Microsoft Introduces SQL Server Information Protection Sensitivity Labels
Apr 19, 2022 | Rabia Noureen
Microsoft Gives Up on Running SQL Server in a Windows Container
Jul 5, 2021 | Brad Sams
New High Availability and Disaster Recovery Features in SQL Server 2019
Nov 20, 2019 | Michael Otey
Ensuring Disaster Recoverability for Your Legacy SQL Server 2008/R2
Jul 22, 2019 | Michael Otey
Moving SQL Databases to the Cloud with the Data Migration Assistant
Jun 12, 2019 | Scott Allen
Disaster Recovery with Azure Backup for SQL Server VMs
May 28, 2019 | Michael Otey
Most popular on petri