Problem
Moving forward with the PowerShell series, it is now time to talk about pipelines. As I mentioned in my previous article, likewise CMDLETs, I think that pipelines are one of those key things that make PowerShell special, and we’ll see how they can be used to solve problems in a particular way.
Solution
In this article, we will cover the basics of pipelines in PowerShell and take a look at several examples to get a better grasp of them.
Initial considerations
- A very basic knowledge of coding in any other programming language will be assumed.
- No direct interaction with SQL Server will be covered in this article, that will be covered in a separate article.
Important note: I will be presenting all the examples using Windows, but that shouldn’t be a showstopper for you to follow along in any other OS of your choosing.
Pipelines in PowerShell
Talking about pipes, not in the programming space but in the real world, a pipe is something used to carry stuff from point A all along the way to point B.
In computing, a pipeline is a set of data-processing elements connected serially, where the output of one element (pipe) is the input for the very next element within the pipeline.
In PowerShell, this concept is applied exactly like that, and it opens up a new door with many possibilities to craft a variety of solutions for a range of problems. As with all of the articles in the series so far, let’s start learning about pipelines with example.
Simple Example
Let’s see what happens when we take the output of the classic Get-Date cmdlet and send it to the Format-List cmdlet. By the way, the Format-List cmdlet formats the output of a command as a list of properties in which each property is displayed on a separate line.
Note: to separate each element of the pipeline you do it with the character “|” as follows:
Get-Date | Format-List
Using Multiple Pipes
Am I limited to just pipelining 2 elements? Let’s find out.
Get-Date | Format-List | Out-File -FilePath .\Pipeline.txt
In the third pipe I’ve instructed PowerShell to grab the output of “Format-List” and send it to a file, and it works flawlessly!
I’m not sure if there’s any limit in the amount of elements that the pipeline can have, at least I haven’t read anything from Microsoft’s official documentation or anywhere else, so I guess you can go wild there.
More Pipeline Examples
Let’s take a look at more examples to see the things we can achieve with pipelines.
System Administrators often use PowerShell for their day-to-day tasks. A classic example would be the usage of the “Get-Service” cmdlet to retrieve the list of services available in the server, and the respective status (Running/Stopped) for each. Since the output that the Get-Service cmdlet returns is a bit large, sometimes SysAdmins just want to check a few items and move on to another thing.
Let’s see how we can filter the list of services by using a pipeline.
If we use the “Where-Object” cmdlet, along with a specific property selector, we can filter only the service with a specific value, like this:
Get-Service | Where-Object -Property Status -eq Running
We can use a very similar approach if we want to check if there are services that match a certain string, like this.
Get-Service | Where-Object -Property DisplayName -match 'SQL*'
For this example, I want to see if there are services in my server that have anything to do with SQL.
An automation use case could be if you create a script that constantly checks if the SQL services are up and running, and attempt to start those in a Stopped status, which in theory should be up and running. This could be achieved by modifying a bit the previous pipeline, as follows:
Get-Service | Where-Object -Property DisplayName -match 'SQL*' | Where-Object -Property Status -eq Stopped | Start-Service
In a similar fashion, SysAdmins also use a lot the Get-Process cmdlet to query the list of processes being run in the system:
Pass Script Output Down the Pipeline
A valid question you might ask is: “can I pass the output of my custom script down the pipeline?”. Let’s find out.
I’ve created a script called pipe1.ps1 that defines an integer variable and returns its value times 2. Then, that output is passed down the pipeline and it displays the received value and the same value slashed by half.
Chain PowerShell Scripts with Pipelines
Another valid question might be: “can I chain my PowerShell scripts in the pipeline?”. Let’s find out.
Script pipe1.ps1 this time, for simplicity, will only return an integer.
As you can see, the trick for the script that will receive something from the pipeline is to set the ValueFromPipeline=$true in the attributes section of the “Param” block.
And if I change the value being returned by pipe1.ps1, the output will react accordingly:
Pretty cool if you ask me! Because this allows you to build complex solutions in a modular fashion. One use case that comes to my mind is having one script that returns a list of SQL Server instances and another script that grabs each instance and performs a connectivity test.
I’ll definitely be having some fun trying to come up with cool SQL Server-specific examples in future articles to share with you.
Next Steps
- In case you missed it, check out the previous article PowerShell for the DBA – CMDLETs and Advanced Functions
- As I’ve mentioned several times throughout the series, make sure to adhere as much as possible to best practices. Be very descriptive with the naming of your scripts so that even chaining them through pipelines makes enough sense for you, and anyone within your team.
- Use pipelines to leverage your creativity while designing solutions through scripting. By combining pipelines with what we have learned so far, you definitely can come up with very cool solutions.
- In the next article we will be taking a look at how to perform DBA tasks using SQL Server CMDLETs and pipelines, so stay tuned!
