Art of the DBA Rotating Header Image

May, 2015:

Your #Powershell Profile

Keeping with the theme of my last blog post, let’s talk a little more about Powershell basics. More specifically, let’s talk about the basics of reusing your Powershell scripts. Most DBAs have their library of scripts that they take with them from job to job, helping them be more effective at their job. Whether it’s SQL, Powershell, or some other language, experienced data professionals always make sure to retain their work for when they’ll need it again.

Enter the profile. This is actually a concept that’s been around for a long, long while in the *nix world. The concept is simple: There is a script that runs every time you start up a session in your shell, configuring your environment. Many times in *nix, this will configure system variables like PATH or HOME, but you can also use it to load custom functions and aliases within your shell.

The Microsoft team borrowed this concept when constructing the Powershell language. We have access to a profile (well, 4 of them) that help us configure and customize our own environments. Similar *nix, it’s a .ps1 script that runs every time you open a new session. This allows you a lot of flexibility for reuse of your code.

Getting Started

As linked above, there are 4 different types of profiles you can use. We won’t focus on these different profiles, but instead stay with the basic profile: the one that applies to the current user and the current host (in these examples, the ISE). To view your profile, open it up in your text editor of choice by referencing the $profile system variable:

ise $profile

If you don’t currently have a profile, you’ll probably get an error about the file not existing. To fix this, create the file:

New-Item -ItemType File -Path $profile -Force

Vioala! You now have a completely empty profile, which is what you will see when you try and open it again.

So Now What?

I can hear the internal dialog now:

“Woo hoo! A profile!”

“Ummm…yeah. That’s great. But what do we do with it?”

“…….”

“That’s what I was thinking, too.

I don’t want to leave you hanging, so let’s start making use of it. The first order of business with the profile is to use it for customizing your environment. Since I am a SQL DBA, I like  to load up the SQLPS module and the SMO objects. This means any time I’m in Powershell, I’ve got the SQL Server cmdlets and objects ready to go.

Next up, I have a function I wrote a while back for reporting on the available disk space on a server. It’s very handy and I use it almost every day. The code is not original, but I wrapped it in a function to save myself a LOT of typing. Usually, you’d package such a function in a module.  I ended putting it in my profile because it was easy. I also add to an alias to save myself even more typing.

This means I end up with a profile that looks a lot like this:

#load SMO library
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#Load SQLPS Module
Import-Module SQLPS -DisableNameChecking

#Set Aliases
New-Alias -Name gfs -Value Get-FreeSpace

function Get-FreeSpace{
<#
.SYNOPSIS
Uses WMI to get capacity and freespace for all disks/mounts on a host.

.DESCRIPTION
Uses WMI Win32_Volume to query logical disks and provide drive size and usage for all
logical disks and mountpoints.  If no parameter is given, localhost is used.  Otherwise
the host name should be passed.

Mike Fal (http://www.mikefal.net) 2012-10-10

.PARAMETER
host - Name of machine information is being queried from, defaults to localhost

.EXAMPLE
Get-FreeSpace "CCX-SQL-PRD-01"
#>

param([string] $hostname = ($env:COMPUTERNAME))
gwmi win32_volume -computername $hostname  | where {$_.drivetype -eq 3} | Sort-Object name `
| ft name,@{l="Size(GB)";e={($_.capacity/1gb).ToString("F2")}},@{l="Free Space(GB)";e={($_.freespace/1gb).ToString("F2")}},@{l="% Free";e={(($_.Freespace/$_.Capacity)*100).ToString("F2")}}

}

Now if I had just added this to my profile and wanted to load it into the current session, I’d just have to execute it, like so:

. $profile

This loads the profile, running it as if it were any other .ps1 script (because it is).

The Power of the Profile

Using this, I have a customized Powershell environment ready to go any time I start. It lets me use Powershell the way I want to, allowing me to extend my shell with my own custom code or pre-packaged modules that I need access to. I’ve talked several times before about building Powershell tools, but at some point we all need a toolbox. By leveraging the profile, you can start building your own custom Powershell toolbox that you can rely on.

Want to know more? You can learn about Powershell profiles right from the shell itself: Get-Help about_profiles. How cool is that?

I’ll Just Leave This Here…

Last year you might have heard about a dust up around PASS voter eligibility. I got pretty vocal about it, but in the end PASS did the right thing. Now PASS is trying to do the right thing again by getting ahead of the issue and notifying the community to check their eligibility.

Here’s the deal. You need to update your PASS profile and make sure some mandatory fields are filled in. You’ll see if you’re eligible if you look at your profile page on the sqlpass.org website. You have until June 1 to do this.

But why wait?  Do it now. It won’t take long and helps both you and PASS maintain the integrity of the voting process.

Read more about it on the PASS website.

Exploring #Powershell

Over the past months, I’ve devoted a lot of time to scripts and tips on using Powershell. While helpful, these articles assume a level of familiarity with Powershell that a lot of SQL Server administrators don’t have. I wanted to take some time to help out those who are looking for a place to start.

The great thing about Powershell is that the Microsoft team has put a lot of effort into building cmdlets that allow you to discover how to use the language. The term ‘cmdlets’ was invented by the Powershell team to help make the language distinct, but they’re essentially functions. Three cmdlets, in particular, will help teach you the language and discover how you can use it:

  • Get-Command
  • Get-Help
  • Get-Member

I like to refer to these cmdlets with the mildly cheesy name “The Holy Trinity of Powershell Self Discovery”.  Yes, that’s a mouthful. With such a clunky name, though, it is hard to forget about them. Because of the information they provide, not a day goes by where I use one of these cmdlets while working with Powershell.

Get-Command

The first of these three, Get-Command, is the cmdlet that lets you browse the cmdlet ‘directory’. While there are several arguments we can pass to this cmdlet, really all you should concern yourself with at the beginning is passing wildcard searches for cmdlets you might be looking for. If you keep in mind that there’s a defined list of Powershell verbs, Get-Command becomes a good way to find cmdlets.

For example, let’s say we wanted to find a cmdlets to add a Windows firewall rule. The best way to start is to simple filter all the cmdlets containing the word “firewall”:

Get-Command *firewall*

Quickly, you’ll get a list of 30 functions for manipulating different parts of the firewall. A lot easier to read that the full list of 1000+ Powershell cmdlets. But we want to filter our list down even more, so let’s improve our filter. Since we know we want a new rule, we can improve our wildcard search:

Get-Command New*firewall*rule

Our result is a single cmdlet, New-NetFirewallRule. Remember that anytime we create something in Powershell, the standard syntax will be “New-” and the noun. We add this into our wildcard search, plus the word “rule” to further filter down. Combine this all into how we use Get-Command, and we have a powerful way to search the language.

Get-Help

Once you find your cmdlet, though, how do you learn how to use it? Fortunately for us, the Powershell team took a page from the *nix world. For anyone who as worked in Unix or Linux, you’ve probably made heavy use of the ‘man’ pages. This is a built in help system to help describe shell commands. Powershell has its own system and you access it using Get-Help (conveniently aliased in Powershell as ‘man’).

Using our previous example, let’s learn about the New-NetFirewallRule cmdlet. The syntax is easy enough:

Get-Help New-NetFirewallRule

Running this will output text to the host window that describes the cmdlet. By default, we’ll see a synopsis, a description, and an outline of the parameters the cmdlet accepts. Extremely useful, but this is just scratching the surface. Get-Help will also accept additional arguments that can give you even more detail or functionality  if you want it:

  • -Examples – Examples of how the cmdlet gets used
  • -Detailed – Even more information on how the cmdlet functions
  • -Full – The entire help file for the cmdlet in question
  • -ShowWindow – Output the full help file to a separate windows
  • -Online – Open up the TechNet page for the cmdlet

But wait, there’s more! Two more awesome things about the Powershell help system are that you can find help on more than just cmdlets and it is constantly updated. Powershell contains a list of topics that you can read on to see how to use the language. These are all found with the text “about_” and you can get a listing of these using:

Get-Help about*

If you want to learn more about how to use variables, for example, you can look at the about_Variables topic and read the specifics about using Powershell variables. There are over 100 different topics in the help system for Powershell 4.0, baking your learning right into the language.

Of course, this information can change and update frequently. In order to keep pace with this rate of change, the Powershell team separated the help system from your standard Windows updates and made manageable online. This means if you ever think your help system is out of date, you only have to run the Update-Help cmdlet when connected to the internet and your local help system will get all the current information.

Get-Member

One of the strongest advantages of Powershell is that it is built on .Net. This means that everything in Powershell is a .Net object. To explore these objects and what they have to offer, you can use the Get-Member cmdlet to provide information about a cmdlet.

The use is pretty simple. Take your variable and pass it via the pipeline to Get-Member:

[string]$AString = ‘Tea. Earl Grey. Hot.’
$AString | Get-Member

What will follow is a list of the object type, methods, and properties you can leverage with that object. You get a comprehensive view of all the capabilities of the object.

If we go back a few posts, you can read how I compared sqlcmd with Invoke-SqlCmd. Get-Member was key to this comparison, because it allowed me to explore the outputs of both:

$query = ‘SELECT type,count(1) FROM sys.objects GROUP BY type;’
$cmdout = sqlcmd -Q $query
$invout = Invoke-Sqlcmd -Query $query

$cmdout | Get-Member
$invout | Get-Member

Comparing these two outputs shows us a lot about the difference between the two commands. We can see that the sqlcmd output is an array of System.Strings and Invoke-SqlCmd is a collection of System.Data.Datarow. For more on the importance of this difference, read my other blog post.  The key is to understand how you can explore and discover this difference using Get-Member.

A Compass and a Map

Understanding Powershell is a long road. There’s a lot of depth to it, providing a great deal of functionality. It can be easy to get lost in the details. While we live in the day and age of LMGTFY, it still helps to have some basic tools at our disposal to find our way through something new. Get-Command, Get-Help, and Get-Member are three cmdlets you will always have access to, helping you find your way to solving problems in Powershell.