In my last article, The Basics of PowerShell Day By Day, I covered some basics of getting started. These articles aren't intended to replace some of the getting started information in things like the Stairway to PowerShell. Instead, I am covering some of the tips, tricks, techniques, and bits of knowledge that I wish I'd have known early on when I started working with PoSh.

In this article, I want to look at comparisons and filtering of things out of the objects that we get when we run PoSh code. These were things that I struggled with early on, and it took me some time to get used to the syntax and structure of how to link objects together to get things done. We'll start looking at the comparison operators and then the Select-Object and Where-Object structures.

Comparing Things

In every language that I have used in my career, comparisons between variables were done with standard operators. For example, in C#, I'd do this:

int x = 4;
int y = 2;
if (x > y) { Console.WriteLine("X is greater"); };

Many of us have gotten used to the greater than (>), less than (<), equality (= or ==), and inequality (!= or <>) operators in our programming lives. There is some inconsistency here, especially with the = and == operations, which can vary by language, but lots of people have muscle memory in using these symbols in code.

That changes in PowerShell.

Use Parameters

In PowerShell we what look like parameters to specify a comparison operation. Let me show what I mean. I can write this code:

$x = 100
$y = 50
if ($x -gt $y) {write-host("x is greater")}

This will print out "x is greater" at the console, but the main thing is the comparison. I need to use the -gt to represent greater than. In PowerShell, here are the mappings.

I've included the LIKE options, since this maps to the work many of us perform in SQL Server, and it is useful in PowerShell as well. The T-SQL comparison operators are on the Docs site, as are the PowerShell operators.

There are a few other operators for use with Regex, but those get more complex quickly.

Examples

We can use these with variables or properties in PoSh to compare items to each other in some way, as I showed in the code above. For example, if I get two objects, I can compare parts of them together. I'll use dbatools here to get a list of logins on an Instance:

$logins = Get-DbaLogin -SqlInstance DevFrankD

This returns an object of logins, of which there are many. I can get properties of any object by using an index, for example, if I use the [1] index, I get the properties of the second item:

I don't want to discuss objects here, but comparisons, but I can access them with an index, and then get a property value with a period and the property. As I have a number of items, so I can do something like this code:

$Logins[1].Name -eq $Logins[2]

This returns false, as the name for the login with index 2 is  "DEVFRANKD\frank" and index #1, as shown in the image, is "##MS_PolicyTsqlExecutionLogin##".

If I change this to -ne, I'll get a TRUE result. Both results are shown in the image here.

Note I didn't have $Logins[2].Name, but PoSh looks for that property to try and guess what I'm doing here.

This allows me to perform comparisons of different properties. If I can't compare two things directly, PoSh tells me. If I try to compare the two objects themselves, without any properties, I get a message that things are not iComparable. You don't know to know what that means other than the things are not directly comparable.

We can also use the -like operator with wildcards, as we can in T-SQL. The wildcard character in PoSh is the *, which is different from the % in T-SQL. With this, we can do something similar like:

 "Frank" -Like "Fr*"

This returns true, as Fr* is like Frank. The two explicit characters (F and r) match, and the asterisk matches the "ank". However, what about Frank and FRANK.

 "Frank" -Like "FR*"

This returns TRUE. Why? PoSh is fundamentally case insensitive (I learned that on Stack Overflow). If I want case sensitivity, I can use the -clike comparison, which returns FALSE for that same code above.

Other than the strange operators, comparisons mostly work as we expect. We get two values and then compare them in some way. We can choose the method, and then get a TRUE/FALSE that can drive our other logic.

Now, let's see how we can start to use comparison operators to filter values.

Where-Object

One of the cmdlets I use quite often is Where-Object. This is like a WHERE clause in T-SQL, but one that's written for your objects. This allows you to filter out items from your pipeline or collection. For example, in my object above, I had all logins. If I show the variable, I see a number of logins that scroll off the screen (you can see the background outside the console).

If I want to limit this list to the sa account, I can limit this by pipeing the collection through Where-Object. I'll use this code, which has a comparison from above.

$Logins | Where-Object {$_.Name -eq 'sa'}

This will return this data:

ComputerName : DEVFRANKD
InstanceName : MSSQLSERVER
SqlInstance  : DEVFRANKD
Name         : sa
LoginType    : SqlLogin
CreateDate   : 4/8/2003 9:10:35 AM
LastLogin    : 10/5/2020 1:49:02 PM
HasAccess    : True
IsLocked     : False
IsDisabled   : False

If I use a LIKE operator, I could actually get more than one login.

If you notice here, the Where-Object takes a script block, which is enclosed in the {}. Inside of here, we can perform our comparison. The current object being tested is referred to with the $_ notation. I treat this structure as the object and then include the period and property, as in $_.Name for the name property, or $_.LoginType for the type of login.

Often when we are looking to limit the results from a large query of data, we can use Where-Object to filter things. By piping out collection through here, we can control what data will be operated on by the rest of our code.

There are other comparison operators in the documentation, but I typically haven't used them in my work.

The Where Method

The other option that we have is a Where() method, which is for all object. When we use this method, we have a few parameters that are available. The first is a script block, as in this code:

(Get-DbaLogin -SqlInstance Aristotle).Where({$_.Name -eq "sa"})

I put my object in parenthesis and then call the Where() method on that. The same script block structure is used here. There are other parameters are listed in the Where method section of the About Arrays docs. We have a second parameter that is the mode of operation. We have these modes:

• Default (0) - Return all items
• First (1) - Return the first item
• Last (2) - Return the last item
• SkipUntil (3) - Skip items until condition is true, the return the remaining items
• Until (4) - Return all items until condition is true
• Split (5) - Return an array of two elements
  • The first element contains matching items
  • The second element contains the remaining items

The third parameter is a number of items. We can use that to further limit any of these modes. I have 3 SQL Logins on my instance, and if I run this, I skip over the first few Windows Auth logins, and then return 2 with this code.

(Get-DbaLogin -SqlInstance DevFrankD).Where({$_.LoginType -eq "SqlLogin"}, "SkipUntil", 2)

I see the two logins here.

Filters

Many cmdlets have some sort of filter property. I know some of the Active Directory cmdlets have a -Filter parameter, where you can include some comparison operation. The dbaTools cmdlets have an -IncludeFilter and an -ExcludeFilter parameter. I can use this to filter things as soon as I run the cmdlet. For example, if I want just the SQL Logins, I can do this:

 Get-DbaLogin -SqlInstance DevFrankD -IncludeFilter "Frank*"

This means that only logins that start with Frank are returned. When I run this, I see this result:

Note that the cmdlet determines what is filtered. For this cmdlet, we only can filter on login name. The -ExcludeFilter parameter excludes logins matching the parameter. If I change this, I get all the logins that don't start with Frank.

For some of the cmdlets that have a -Filter parameter, they may allow a script block expression to be added in quotes. I haven't seen a lot of these, but some cmdlets implement this.

Choosing What is Returned

So far, each time we have run a cmdlet, we get the complete object, with all the properties. For Get-DbaLogin, the default shows us 10 properties, but there are others. If we run the output through Get-Member, as in this code, we'll see many more properties.

While we can filter on any of these, often we might want to show specific properties and not others. One of the easy ways to do this is with the Select-Object. If I use my $Logins variable, this code will filter to one login and then return specific properties.

 $Logins | Where-Object {$_.Name -eq "sa"} | Select-Object Name, Sid, MustChangePassword

The results I see from this are:

Name Sid MustChangePassword
---- --- ------------------
sa   {1}              False

I can just include a comma separated list of property names with Select-Object and then just have those objects. If I change my filter to include all the SQL Logins, I get these results.

Name                              Sid                     MustChangePassword
----                              ---                     ------------------
##MS_PolicyEventProcessingLogin## {240, 187, 115, 148...}              False
##MS_PolicyTsqlExecutionLogin##   {39, 87, 141, 133...}                False
FrankAdmin                        {31, 52, 76, 75...}                  False
FrankDev                          {204, 52, 180, 125...}               False
sa                                {1}                                  False

I'm not sure why the MS_PolicyXX logins are SqlLogins, but they are.

Summary

This is another short introduction to a few concepts that have helped me build PoSh scripts that are useful and helpful in my job. Knowing how to filter and select certain data, and perform comparisons, will enable you to build flexible scripts to get things done.

The comparison operators feel strange at first, but you quickly get used to them and the names are easy to guess if you forget them.