In this article I show how to use the Where method with the Split option to quickly assign two variables. As an example we will check if our SQL Service Accounts have the Instant File Initialization Privilege.

Case

Sometimes we want to apply a filter to an array or other collection of objects, but keep both the items that pass the filter and those that fail it. Instead of cycling twice through the collection, there's a one-step method.

Instant File Initialization is a privilege assigned in the local security policy. Here's some explanation by MSSQL Tiger Team.
There's a lot to tell about it, but I'm not going to do that here. Let's just assume it's a good thing to assign that privilege to the account with which the SQL Service runs.

Solution

The Where-method has some options. We can use the Split option to assign all items to the appropriate variable.

We use two functions from dbatools:
Get-DbaPrivilege
Get-DbaSqlService

History

Where-Object

Where-Object has always been there for us, even though we mostly call it Where. It's a cmdlet that accepts pipeline input to filter whatever is delivered. To do this, it uses the parameter binder, which takes some time.

$MyArray | Where-Object { $_.value -gt 100 }

or, with the simplified syntax in PS V3:

$MyArray | Where value -gt 100

TechNet

Where method

With PowerShell V4 came a lot of new exciting functionality. One of the new toys was the Where-method. It works similar to Where-Object, but is called in a different way. It's actually an operator, but applied using method syntax.
It's supposed to be faster than Where-Object, while using more RAM. Although I've seen test results on other blogs that claim exceptions.

Bruce Payette and Richard Siddaway explain all about that in 'Windows PowerShell in Action', a book I recommend to every PowerShell user, new or experienced.

$MyArray.Where({ $_.value -gt 100 })

The ( ) are optional, but I always write them anyway.

Options of the Where method

While it's a leap forward to replace Where-Object with this fast method, there's more! This method comes with some options:

• First
• Last
• Split
• Until
• SkipUntil

# Get the SQL server with the highest sql plans memory usage:
(Get-DbaMemoryUsage -ComputerName sql01,sql02,sql03 | Sort-Object Pages -Descending).where({$_.counterinstance -eq 'plan cache(sql plans)'}, 'First')
# Get the 3 SQL servers with the highest sql plans memory usage:
(Get-DbaMemoryUsage -ComputerName sql01,sql02,sql03 | Sort-Object Pages -Descending).where({$_.counterinstance -eq 'plan cache(sql plans)'}, 'First', 3)

Until will return all objects until the first one that matches the filter. SkipUntil will skip all objects until the first one that matches the filter. For the following objects it does not matter whether they match or not.

Split

Using the 'Split' option, the whole collection is returned, but 'split'. What did you expect?

When we let the results return to the console, we won't see much, because both collections are written consecutively. There's no separator or colouring to indicate we have two collections now.
But when assigned to a variable, there will be two object collections in it.

$hasifi = (Get-DbaPrivilege -ComputerName $AllSQLHosts).where({$_.InstantFileInitializationPrivilege}, 'Split')
$hasifi.count
$hasifi.gettype()
$hasifi[0].count
$hasifi[0].gettype()

We see that the variable $hasifi is an array with 2 items, which are collections themselves. In this case, we have 39 objects in the first ($hasifi[0]) and 41 in the second ($hasifi[1]) collection.

We can use the split functionality like this, but it's a bit neater to assign the collections to different variables, like this:

$ifi,$noifi = (Get-DbaPrivilege -ComputerName $AllSQLHosts).where({$_.InstantFileInitializationPrivilege}, 'Split')
$ifi.count
$noifi.count

Do my SQL Service accounts have the IFI privilege?

preparation

We will need the SQL Servers we want to check. How we can list our servers is explained in gather computers.

We use some functions from the dbatools module.
In PowerShell V3 and higher, there is auto-loading. That means a module will implicitly be loaded the moment you call a function in it. If we are going to save our statements in a script we have to import explicitly, however.

Import-Module dbatools

methodology

We can check the sql service accounts against the Instant File Initialization Privilege.

There are 4 types of users as a result of this cross check. We will assign 3 of those categories to variables:

We can show those categories in different collections by using the Where-method with 'Split' option twice:

$ifi = (Get-DbaPrivilege -ComputerName $AllSQLHosts).where({$_.InstantFileInitializationPrivilege})  

The first statement gets all computer-user combinations that have IFI privilege. We use the Where-method, but not the 'Split' option. We could have done that, and then split both collections again to get our 4 groups. Instead we will split the $ifi in 3.

We see some warnings because there are computers in $AllSqlHosts where this function does not work.

$Serviceaccounts = Get-DbaSqlService -ComputerName $AllSQLHosts -Type Engine | Select-Object ComputerName, @{l='User';e={$_.startname}}  

The second statement gets all accounts that run the SQL Service as a computer-user combo, and because we want to compare the two result sets, we rename the 'StartName' from Get-DbaSqlService to 'User'.

Again a warning about a failed execution, but $ServiceAccounts holds the users that run SQL Service. This is a test lab, so don't worry about best practices just now. :-)

$SqlAccNoIFI,$HasIFI = (Compare-Object -ReferenceObject $Serviceaccounts -DifferenceObject $ifi -Property ComputerName,User -IncludeEqual ).where({$_.sideindicator -eq '<='}, 'split')  

The third statement has a little trick: Compare-Object itself divides all 'records' into 3 groups already. The Side Indicator is just a string with possible values '==', '<=' and '=>'. The '==' group is left out by default. We add -IncludeEqual and have them included. By using '<=' as filter, we split the remaining 2 groups into

• Those that are in $ServiceAccounts but not in $ifi
• Those that are in $ifi but are no SQL Service Accounts

$SqlAccWithIFI,$OtherAccWithIFI = $HasIFI.where({$_.sideindicator -eq '=='}, 'Split')

And finally, we split $HasIFI once again on the sideindicator.

We see both collections are populated. Let's take a look at the actual accounts in all collections:

There's only one SQL Service account that has the IFI privilege. So we discovered we have some work to do! Unless the account is member of the 'BUILTIN\Administrators'.
Whether that's a good idea can be subject of another article, or maybe a starting point for another script to check our SQL Servers...

To be continued

Now you have one example of how to use the 'Split' option.

The same technique can be used for thousands of objects.

You can also decide to save the results in a database, to a file, or send them in an e-mail, like I showed with backup results. We can keep it very simple by just sending it to a GridView.

Let the creativity flow.

Previous Post Next Post