Tag Archives: Pester

Pester 4.2.0 has a Because…… because :-)

Posted on by

I was going through my demo for the South Coast User Group meeting tonight and decided to add some details about the Because parameter available in the Pester pre-release version 4.2.0.

To install a pre-release version you need to get the latest  PowerShellGet module. This is pre-installed with PowerShell v6 but for earlier versions open PowerShell as administrator and run

Install-Module  PowerShellGet

You can try out the Pester pre-release version (once you have the latest PowerShellGet) by installing it from the PowerShell Gallery with

Install-Module -Name Pester -AllowPrerelease -Force # -Scope CurrentUser # if not admin

There are a number of improvements as you can see in the change log I particularly like the

  • Add -BeTrue to test for truthy values
  • Add -BeFalse to test for falsy values

This release adds the Because parameter to the all assertions. This means that you can add a reason why the test has failed. As JAKUB JAREŠ writes here

  • Reasons force you think more
  • Reasons document your intent
  • Reasons make your TestCases clearer
  • So you can do something like this
Describe "This shows the Because"{
    It "Should be true" {
        $false | Should -BeTrue -Because "The Beard said so"
    }
}

Which gives an error message like this 🙂

As you can see the Expected gives the expected value and then your Because statement and then the actual result. Which means that you could write validation tests like

Describe "My System" {
    Context "Server" {
        It "Should be using XP SP3" {
            (Get-CimInstance -ClassName win32_operatingsystem).Version | Should -Be '5.1.2600' -Because "We have failed to bother to update the App and it only works on XP"
        }
        It "Should be running as rob-xps\mrrob" {
            whoami | Should -Be 'rob-xps\mrrob' -Because "This is the user with the permissions"
        }
        It "Should have SMB1 enabled" {
            (Get-SmbServerConfiguration).EnableSMB1Protocol | Should -BeTrue -Because "We don't care about the risk"
        }
    }
}

and get a result like this

Or if you were looking to validate your SQL Server you could write something like this

It "Backups Should have Succeeeded" {
    $Where = {$_IsEnabled -eq $true -and $_.Name -like '*databasebackup*'}
    $Should = @{
        BeTrue = $true
        Because =  "WE NEED BACKUPS - OMG"
    }
    (Get-DbaAgentJob -SqlInstance $instance| Where-Object $where).LastRunOutcome -NotContains 'Failed' | Should @Should
}

or maybe your security policies allow Windows Groups as logins on your SQL instances. You could easily link to the documentation and explain why this is important. This way you could build up a set of tests to validate your SQL Server is just so for your environment

It "Should only have Windows groups as logins" {
    $Should = @{
        Befalse = $true
        Because = "Our Security Policies say we must only have Windows groups as logins - See this document"
    }
    (Get-DbaLogin -SqlInstance $instance -WindowsLogins).LoginType -contains 'WindowsUser' | Should @Should
}

Just for fun, these would look like this

and the code looks like

$Instances = 'Rob-XPS', 'Rob-XPS\Bolton'

foreach ($instance in $Instances) {
    $Server, $InstanceName = $Instance.Split('/')
    if ($InstanceName.Length -eq 0) {$InstanceName = 'MSSSQLSERVER'}

    Describe "Testing the instance $instance" {
        Context "SQL Agent Jobs" {
            It "Backups Should have Succeeeded" {
                $Where = {$_IsEnabled -eq $true -and $_.Name -like '*databasebackup*'}
                $Should = @{
                    BeTrue = $true
                    Because =  "WE NEED BACKUPS - OMG "
                }
                (Get-DbaAgentJob -SqlInstance $instance| Where-Object $where).LastRunOutcome -NotContains 'Failed' | Should @Should
            }
            Context "Logins" {
                It "Should only have Windows groups as logins" {
                    $Should = @{
                        Befalse = $true
                        Because = "Our Security Policies say we must only have Windows groups as logins - See this document"
                    }
                    (Get-DbaLogin -SqlInstance $instance -WindowsLogins).LoginType -contains 'WindowsUser' | Should @Should
                }
            }
        }
    }
}

This will be a useful improvement to Pester when it is released and enable you to write validation checks with explanations.

Chrissy has written about dbachecks the new up and coming community driven open source PowerShell module for SQL DBAs to validate their SQL Server estate. we have taken some of the ideas that we have presented about a way of using dbatools with Pester to validate that everything is how it should be and placed them into a meta data driven framework to make things easy for anyone to use. It is looking really good and I am really excited about it. It will be released very soon.

Chrissy and I will be doing a pre-con at SQLBits where we will talk in detail about how this works. You can find out more and sign up here

Using the AST in Pester for dbachecks

Posted on by

TagLine – My goal – Chrissy will appreciate Unit Tests one day 🙂

Chrissy has written about dbachecks the new up and coming community driven open source PowerShell module for SQL DBAs to validate their SQL Server estate. we have taken some of the ideas that we have presented about a way of using dbatools with Pester to validate that everything is how it should be and placed them into a meta data driven framework to make things easy for anyone to use. It is looking really good and I am really excited about it. It will be released very soon.

Chrissy and I will be doing a pre-con at SQLBits where we will talk in detail about how this works. You can find out more and sign up here

Cláudio Silva has improved my PowerBi For Pester file and made it beautiful and whilst we were discussing this we found that if the Pester Tests were not formatted correctly the Power Bi looked … well rubbish to be honest! Chrissy asked if we could enforce some rules for writing our Pester tests.

The rules were

The Describe title should be in double quotes
The Describe should use the plural Tags parameter
The Tags should be singular
The first Tag should be a unique tag in Get-DbcConfig
The context title should end with $psitem
The code should use Get-SqlInstance or Get-ComputerName
The Code should use the forEach method
The code should not use $_
The code should contain a Context block

She asked me if I could write the Pester Tests for it and this is how I did it. I needed to look at the Tags parameter for the Describe. It occurred to me that this was a job for the Abstract Syntax Tree (AST). I don’t know very much about the this but I sort of remembered reading a blog post by Francois-Xavier Cat about using it with Pester so I went and read that and found an answer on Stack Overflow as well. These looked just like what I needed so I made use of them. Thank you very much to Francois-Xavier and wOxxOm for sharing.

The first thing I did was to get the Pester Tests which we have located in a checks folder and loop through them and get the content of the file with the Raw parameter

Describe "Checking that each dbachecks Pester test is correctly formatted for Power Bi and Coded correctly" {
$Checks =(Get-ChildItem$ModuleBase\checks).Where{$_.Name-ne'HADR.Tests.ps1'}
$Checks.Foreach{
$Check =Get-Content$Psitem.FullName-Raw
Context "$($_.Name) - Checking Describes titles and tags" {
Then I decided to look at the Describes using the method that wOxxOm (I know no more about this person!) showed.
$Describes = [Management.Automation.Language.Parser]::ParseInput($check, [ref]$tokens, [ref]$errors).
FindAll([Func[Management.Automation.Language.Ast, bool]] {
        param($ast)
        $ast.CommandElements -and
        $ast.CommandElements[0].Value -eq 'describe'
    }, $true) |
    ForEach {
    $CE = $_.CommandElements
    $secondString = ($CE |Where { $_.StaticType.name -eq 'string' })[1]
    $tagIdx = $CE.IndexOf(($CE |Where ParameterName -eq'Tags')) + 1
    $tags = if ($tagIdx -and $tagIdx -lt $CE.Count) {
        $CE[$tagIdx].Extent
    }
    New-Object PSCustomObject -Property @{
        Name = $secondString
        Tags = $tags
    }
}
As I understand it, this code is using the Parser on the $check (which contains the code from the file) and finding all of the Describe commands and creating an object of the title of the Describe with the StaticType equal to String and values from the Tag parameter.
When I ran this against the database tests file I got the following results
Then it was a simple case of writing some tests for the values
@($describes).Foreach{
    $title = $PSItem.Name.ToString().Trim('"').Trim('''')
    It "$title Should Use a double quote after the Describe" {
        $PSItem.Name.ToString().Startswith('"')| Should be $true
        $PSItem.Name.ToString().Endswith('"')| Should be $true
    }
    It "$title should use a plural for tags" {
        $PsItem.Tags| Should Not BeNullOrEmpty
    }
    # a simple test for no esses apart from statistics and Access!!
    if ($null -ne $PSItem.Tags) {
        $PSItem.Tags.Text.Split(',').Trim().Where{($_ -ne '$filename') -and ($_ -notlike '*statistics*') -and ($_ -notlike '*BackupPathAccess*') }.ForEach{
            It "$PsItem Should Be Singular" {
                $_.ToString().Endswith('s')| Should Be $False
            }
        }
        It "The first Tag Should Be in the unique Tags returned from Get-DbcCheck" {
            $UniqueTags -contains $PSItem.Tags.Text.Split(',')[0].ToString()| Should Be $true
        }
    }
    else {
        It "You haven't used the Tags Parameter so we can't check the tags" {
            $false| Should be $true
        }
    }
}

The Describes variable is inside @() so that if there is only one the ForEach Method will still work. The unique tags are returned from our command Get-DbcCheck which shows all of the checks. We will have a unique tag for each test so that they can be run individually.

Yes, I have tried to ensure that the tags are singular by ensuring that they do not end with an s (apart from statistics) and so had to not check  BackupPathAccess and statistics. Filename is a variable that we add to each Describe Tags so that we can run all of the tests in one file. I added a little if block to the Pester as well so that the error if the Tags parameter was not passed was more obvious

I did the same with the context blocks as well

Context "$($_.Name) - Checking Contexts" {
    ## Find the Contexts
    $Contexts = [Management.Automation.Language.Parser]::ParseInput($check, [ref]$tokens, [ref]$errors).
    FindAll([Func[Management.Automation.Language.Ast, bool]] {
            param($ast)
            $ast.CommandElements -and
            $ast.CommandElements[0].Value -eq 'Context'
        }, $true) |
        ForEach {
        $CE = $_.CommandElements
        $secondString = ($CE |Where { $_.StaticType.name -eq 'string' })[1]
        New-Object PSCustomObject -Property @{
            Name = $secondString
        }
    }
    @($Contexts).ForEach{
        $title = $PSItem.Name.ToString().Trim('"').Trim('''')
        It "$Title Should end with `$psitem So that the PowerBi will work correctly" {
            $PSItem.Name.ToString().Endswith('psitem"')| Should Be $true
        }
    }
}
This time we look for the Context command and ensure that the string value ends with psitem as the PowerBi parses the last value when creating columns
Finally I got all of the code and check if it matches some coding standards
Context "$($_.Name) - Checking Code" {
    ## This just grabs all the code
    $AST = [System.Management.Automation.Language.Parser]::ParseInput($Check, [ref]$null, [ref]$null)
    $Statements = $AST.EndBlock.statements.Extent
    ## Ignore the filename line
    @($Statements.Where{$_.StartLineNumber -ne 1}).ForEach{
        $title = [regex]::matches($PSItem.text, "Describe(.*)-Tag").groups[1].value.Replace('"', '').Replace('''', '').trim()
        It "$title Should Use Get-SqlInstance or Get-ComputerName" {
            ($PSItem.text -Match 'Get-SqlInstance') -or ($psitem.text -match 'Get-ComputerName')| Should be $true
        }
        It "$title Should use the ForEach Method" {
            ($Psitem.text -match 'Get-SqlInstance\).ForEach{') -or ($Psitem.text -match 'Get-ComputerName\).ForEach{')| Should Be $true# use the \ to escape the )
    }
    It "$title Should not use `$_" {
        ($Psitem.text -match '$_')| Should Be $false
    }
    It "$title Should Contain a Context Block" {
        $Psitem.text -match 'Context'| Should Be $True
    }
}

I trim the title from the Describe block so that it is easy to see where the failures (or passes) are with some regex and then loop through each statement apart from the first line to ensure that the code is using our internal commands Get-SQLInstance or Get-ComputerName to get information, that we are looping through each of those arrays using the ForEach method rather than ForEach-Object and using $psitem rather than $_ to reference the “This Item” in the array and that each Describe block has a context block.

This should ensure that any new tests that are added to the module follow the guidance we have set up on the Wiki and ensure that the Power Bi results still look beautiful!

Anyone can run the tests using

Invoke-Pester .\tests\Unit.Tests.ps1 -show Fails
before they create a Pull request and it looks like
if everything is Green then they can submit their Pull Request 🙂 If not they can see quickly that something needs to be fixed. (fail early 🙂 )
03 fails.png

Converting a Datarow to a JSON object with PowerShell

Posted on by

This is just a quick post. As is frequent with these they are as much for me to refer to in the future and also because the very act of writing it down will aid me in remembering. I encourage you to do the same. Share what you learn because it will help you as well as helping others.

Anyway, I was writing some Pester tests for a module that I was writing when I needed some sample data. I have written before about using Json for this purpose This function required some data from a database so I wrote the query to get the data and used dbatools to run the query against the database using Get-DbaDatabase

$db = Get-DbaDatabase -SqlInstance $Instance -Database $Database
$variable = $db.Query($Query)

Simple enough. I wanted to be able to Mock $variable. I wrapped the code above in a function, let’s call it Run-Query

function Run-Query {(Param $query)
$db = Get-DbaDatabase -SqlInstance $Instance -Database $Database
$variable = $db.Query($Query)
}

Which meant that I could easily separate it for mocking in my test. I ran the code and investigated the $variable variable to ensure it had what I wanted for my test and then decided to convert it into JSON using ConvertTo-Json

Lets show what happens with an example using WideWorldImporters and a query I found on Kendra Littles blogpost about deadlocks

$query = @"
SELECT Top 10 CityName, StateProvinceName, sp.LatestRecordedPopulation, CountryName
FROM Application.Cities AS city
JOIN Application.StateProvinces AS sp on
city.StateProvinceID = sp.StateProvinceID
JOIN Application.Countries AS ctry on
sp.CountryID=ctry.CountryID
WHERE sp.StateProvinceName = N'Virginia'
ORDER BY CityName
"@
$db = Get-DbaDatabase -SqlInstance rob-xps -Database WideWorldImporters
$variable = $db.Query($Query)
If I investigate the $variable variable I get

data results

The results were just what I wanted so I thought I will just convert them to JSON and save them in a file and bingo I have some test data in a mock to ensure my code is doing what I expect. However, when I run

$variable | ConvertTo-Json

I get

json error.png

and thats just for one row!

The way to resolve this is to only select the data that we need. The easiest way to do this is to exclude the properties that we don’t need

$variable | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | ConvertTo-Json

which gave me what I needed and a good use case for -ExcludeProperty

json fixed.png

Handling Missing Instances when Looping with Pester

Posted on by

In my previous posts about writing your first Pester Test and looping through instances I described how you can start to validate that your SQL Server is how YOU want it to be.

Unavailable machines

Once you begin to have a number of tests for a number of instances you want to be able to handle any machines that are not available cleanly otherwise you might end up with something like this.

01 - error.png

In this (made up) example we loop through 3 instances and try to check the DNS Server entry is correct but for one of them we get a massive error and if we had created a large number of tests for each machine we would have a large number of massive errors.

Empty Collection

If we don’t successfully create our collection we might have an empty collection which will give us a different issue. No tests

02 - no tests.png

If this was in amongst a whole number of tests we would not have tested anything in this Describe block and might be thinking that our tests were OK because we had no failures of our tests. We would be wrong!

Dealing with Empty Collections

One way of dealing with empty collections is to test that they have more than 0 members

if ($instances.count -gt 0) {
    $instances.ForEach{
        ## Tests in here
    }
}
else {Write-Warning "Uh-Oh - The Beard is Sad! - The collection is empty. Did you set `$Instances correctly?"}
Notice the backtick ` before the $ to escape it in the Write-Warning. An empty collection now looks like
03 - uh-oh.png
Which is much better and provides useful information to the user

Dealing with Unavailable Machines

If we want to make sure we dont clutter up our test results with a whole load of failures when a machine is unavailable we can use similar logic.

First we could check if it is responding to a ping (assuming that ICMP is allowed by the firewall and switches) using

Test-Connection -ComputerName $computer -Count 1 -Quiet -ErrorAction SilentlyContinue

This will just try one ping and do it quietly only returning True or False and if there are any errors it shouldn’t mention it

In the example above I am using PSRemoting and we should make sure that that is working too. So whilst I could use

Test-WSMan -ComputerName $computer

this only checks if a WSMAN connection is possible and not other factors that could be affecting the ability to run remote sessions. Having been caught by this before I have always used this function from Lee Holmes (Thank you Lee) and thus can use

$instances.ForEach{
    $computer = $_.Split('\')[0]# To get the computername if there is an instance name
    # Check if machine responds to ping
    if (!(Test-Connection-ComputerName $computer-Count 1-Quiet -ErrorAction SilentlyContinue))
    {Write-Warning "Uh-Oh - $Computer is not responding to a ping - aborting the tests for this machine"; Return}
    # Check if PSremoting is possible for this machine
    # Requires Test-PSRemoting by Lee Holmes http://www.leeholmes.com/blog/2009/11/20/testing-for-powershell-remoting-test-psremoting/
    if (!(Test-PsRemoting$computer))
    {Write-Warning "Uh-Oh - $Computer is not able to use PSRemoting - aborting the tests for this machine"; Return}
    Describe "Testing Instance $($_)" {
        ## Put tests in here
    }
which provides a result like this

04 - better handling.png

Which is much better I think 🙂

Let dbatools do the error handling for you

If your tests are only using the dbatools module then there is built in error handling that you can use. By default dbatools returns useful messages rather than the exceptions from PowerShell (You can enable the exceptions using the -EnableExceptions parameter if you want/need to) so if we run our example from the previous post it will look like

05 - dbatools handling.png

which is fine for a single command but we don’t really want to waste time and resources repeatedly trying to connect to an instance if we know it is not available if we are running multiple commands against each instance.

dbatools at the beginning of the loop

We can use Test-DbaConnection to perform a check at the beginning of the loop as we discussed in the previous post

$instances.ForEach{
    if (!((Test-DbaConnection-SqlInstance $_ -WarningAction SilentlyContinue).ConnectSuccess))
    {Write-Warning "Uh-Oh - we cannot connect to $_ - aborting the tests for this instance"; Return}
Notice that we have used -WarningAction SilentlyContinue to hide the warnings from the command this tiime. Our test now looks like
06 - dbatools test-dbaconnection.png
Test-DbaConnection performs a number of tests so you can check for ping SQL version, domain name and remoting if you want to exclude tests on those basis

Round Up

In this post we have covered some methods of ensuring that your Pester Tests return what you expect. You don’t want empty collections of SQL Instances making you think you have no failed tests when you have not actually run any tests.

You can do this by checking how many instances are in the collection

You also dont want to keep running tests against a machine or instance that is not responding or available.

You can do this by checking a ping with Test-Connection or if remoting is required by using the Test-PSRemoting function from Lee Holmes

If you want to use dbatools exclusively you can use Test-DbaConnection

Here is a framework to put your tests inside. You will need to provide the values for the $Instances and place your tests inside the Describe Block

if ($instances.count -gt 0) {
    $instances.ForEach{
        $TestConnection = Test-DbaConnection-SqlInstance $_ -WarningAction SilentlyContinue
        # Check if machine responds to ping
        if (!($TestConnection.IsPingable))
        {Write-Warning "Uh-Oh - The Beard is Sad! - - $_ is not responding to a ping - aborting the tests for this instance"; Return}
        # Check if we have remote access to the machine
        if (!($TestConnection.PsRemotingAccessible))
        {Write-Warning "Uh-Oh - The Beard is Sad! - - $_ is not able to use PSRemoting - aborting the tests for this instance"; Return}
        # Check if we have SQL connection to the Instance
        if (!($TestConnection.ConnectSuccess))
        {Write-Warning "Uh-Oh - The Beard is Sad! - - we cannot connect to SQL on $_ - aborting the tests for this instance"; Return}
        Describe "Testing Instance $($_)" {
            ## Now put your tests in here - seperate them with context blocks if you want to
            Context "Networks" { }
        }
    }
}
else
## If the collection is empty
{Write-Warning "Uh-Oh - The Beard is Sad! - The collection is empty. Did you set `$Instances correctly?"}

2 Ways to Loop through collections in Pester

Posted on by

In my last post I showed you how to write your first Pester test to validate something. Here’s a recap

  • Decide the information you wish to test
  • Understand how to get it with PowerShell
  • Understand what makes it pass and what makes it fail
  • Write a Pester Test

You probably have more than one instance that you want to test, so how do you loop through a collection of instances? There are a couple of ways.

Getting the Latest Version of the Module

The magnificent Steve Jones wrote about getting the latest version of Pester and the correct way to do it. You can find the important information here

Test Cases

The first way is to use the Test Case parameter of the It command (the test) which I have written about when using TDD for Pester here

Lets write a test first to check if we can successfully connect to a SQL Instance. Running

Find-DbaCommand connection

shows us that the Test-DbaConnection command is the one that we want from the dbatools module. We should always run Get-Help to understand how to use any PowerShell command. This shows us that the results will look like this

01 - gethelp test-dbaconnection

So there is a ConnectSuccess result which returns True or false. Our test can look like this for a single instance

Describe 'Testing connection to ROB-XPS' {
    It "Connects successfully to ROB-XPS" {
        (Test-DbaConnection-SqlInstance ROB-XPS).ConnectSuccess | Should Be $True
    }
}

which gives us some test results that look like this

successful test.png
which is fine for one instance but we want to check many.
We need to gather the instances into a $Instances variable. In my examples I have hard coded a list of SQL Instances but you can, and probably should, use a more dynamic method, maybe the results of a query to a configuration database. Then we can fill our TestCases variable which can be done like this
$Instances = 'ROB-XPS','ROB-XPS\DAVE','ROB-XPS\BOLTON','ROB-XPS\SQL2016'
# Create an empty array
$TestCases = @()
# Fill the Testcases with the values and a Name of Instance
$Instances.ForEach{$TestCases += @{Instance = $_}}
Then we can write our test like this
# Get a list of SQL Servers
# Use whichever method suits your situation
# Maybe from a configuration database
# I'm just using a hard-coded list for example
$Instances = 'ROB-XPS','ROB-XPS\DAVE','ROB-XPS\BOLTON','ROB-XPS\SQL2016'

# Create an empty array
$TestCases = @()

# Fill the Testcases with the values and a Name of Instance
$Instances.ForEach{$TestCases += @{Instance = $_}}
Describe 'Testing connection to SQL Instances' {
    # Put the TestCases 'Name' in <> and add the TestCases parameter
    It "Connects successfully to <Instance>" -TestCases $TestCases {
        # Add a Parameter to the test with the same name as the TestCases Name
        Param($Instance)
        # Write the test using the TestCases Name
        (Test-DbaConnection -SqlInstance $Instance).ConnectSuccess | Should Be $True
    }
}
Within the title of the test we refer to the instance inside <> and add the parameter TestCases with a value of the $TestCases variable. We also need to add a Param() to the test with the same name and then use that variable in the test.
This looks like this
Testcases test.png

Pester is PowerShell

The problem with  Test Cases is that we can only easily loop through one collection, but as Pester is just PowerShell we can simply use ForEach if we wanted to loop through multiple ones, like instances and then databases.

I like to use the ForEach method as it is slightly quicker than other methods. It will only work with PowerShell version 4 and above. Below that version you need to pipe the collection to For-EachObject.

Lets write a test to see if our databases have trustworthy set on. We can do this using the Trustworthy property returned from Get-DbaDatabase. 

We loop through our Instances using the ForEach method and create a Context for each Instance to make the test results easier to read. We then place the call to Get-DbaDatabase inside braces and loop through those and check the Trustworthy property

# Get a list of SQL Servers
# Use whichever method suits your situation
# Maybe from a configuration database
# I'm just using a hard-coded list for example
$Instances = 'ROB-XPS','ROB-XPS\DAVE','ROB-XPS\BOLTON','ROB-XPS\SQL2016'
Describe 'Testing user databases' {
    # Loop through the instances
    $Instances.ForEach{
        # Create a Context for each Instance.
        Context "Testing User Databases on $($_)" {
            # Loop through the User databases on the instance
            (Get-DbaDatabase -SqlInstance $_ -ExcludeAllSystemDb).ForEach{
                # Refer to the database name and Instance name inside a $()
                It "Database $($_.Name) on Instance $($_.Parent.Name) should not have TRUSTWORTHY ON" {
                    $_.Trustworthy | Should Be $false
                }
            }
        }
    }
}
and it looks like this

testdatabasetrustworthy.png

So there you have two different ways to loop through collections in your Pester tests. Hopefully this can help you to write some good tests to validate your environment.
Happy Pestering

Spend a Whole Day With Chrissy & I at SQLBits

If you would like to spend a whole day with Chrissy LeMaire and I at SQLBits in London in February – we have a pre-con on the Thursday
You can find out more about the pre-con sqlps.io/bitsprecon
and you can register at sqlps.io/bitsreg

Write Your first Pester Test Today

Posted on by

I was in Glasgow this Friday enjoying the fantastic hospitality of the Glasgow SQL User Group @SQLGlasgow and presenting sessions with Andre Kamman, William Durkin and Chrissy LeMaire

I presented “Green is Good Red is Bad – Turning your checklists into Pester Tests”. I had to make sure I had enough energy beforehand so I treated myself to a fabulous burger.

20171110_114933-compressor.jpg

Afterwards I was talking to some of the attendees and realised that maybe I could show how easy it was to start writing your first Pester test. Here are the steps to follow so that you can  write your first Pester test

Decide the information you wish to test
Understand how to get it with PowerShell
Understand what makes it pass and what makes it fail
Write a Pester Test

The first bit is up to you. I cannot decide what you need to test for on your servers in your environments. Whatever is the most important. For now pick one thing.

Logins – Lets pick logins as an example for this post. It is good practice to disable the sa account is advice that you will read all over the internet and is often written into estate documentation so lets write a test for that

Now we need the PowerShell command to return the information to test for. We need a command that will get information about logins on a SQL server and if it can return disabled logins then all the better.

As always when starting to use PowerShell with SQL Server I would start with dbatools if we run Find-DbaCommand we can search for commands in the module that support logins. (If you have chosen something none SQL Server related then you can use Get-Command or the internet to find the command you need)

find-dbacommand.png

Get-DbaLogin . That looks like the one that we want. Now we need to understand how to use it. Always always use Get-Help to do this. If we run

Get-Help Get-DbaLogins -detailed

we get all of the information about the command and the examples. Example 8 looks like it will help us

get-dbalogin example

So now try running the command for our disabled sa account

Get-DbaLogin -SqlInstance rob-xps -Login sa -Disabled

disabled sa account

So we know that if we have a disabled sa account we get a result. Lets enable the sa account and run the command again

not disabled.png

We don’t get a result. Excellent, now we know what happens for a successful test – we get one result and for failed test we get zero results. We can check that by running

login count

The first one has the account disabled and the second one not. So now we can write our Pester Test. We can start with a Describe Block with a useful title. I am going to add a context block so that you can see how you can group your tests.

describe context

and then we will write our test. Pester Tests use the It keyword. You should always give a useful title to your test

it should

Now we can write our test. We know that the command returns one result when we want it to pass so we can write a test like this

login test.png

The code I have added is

(Get-DbaLogin -SqlInstance rob-xps -Login sa -Disabled).Count | Should Be 1
which is
  • the code for getting the information about the thing we wanted to test (The count of the disabled sa logins on the instance)
  • a pipe symbol |
  • The Should key word
  • The Be keyword
  • and the result we want to pass the test (1)

Ta Da! One Pester test written. You can run the test just by highlighting the code and running it in VS Code (or PowerShell ISE) and it will look like this for a passing test

passing test

It is better to save it for later use and then call it with Invoke-Pester

invoke

So now you can write your first Pester test. Just find the PowerShell to get the information that you need, understand what the results will be for passing and failing tests and write your test 🙂

Getting the Latest Version of the Module

The magnificent Steve Jones wrote about getting the latest version of Pester and the correct way to do it. You can find the important information here

Spend a Whole Day With Chrissy & I at SQLBits

If you would like to spend a whole day with Chrissy LeMaire and I at SQLBits in London in February – we have a pre-con on the Thursday
You can find out more about the pre-con sqlps.io/bitsprecon
and you can register at sqlps.io/bitsreg

A Pretty PowerBi Pester Results Template File

Posted on by

I have left the heat and humidity of Singapore where I have been presenting at the PowerShell Conference Asia and DevOpsDays Singapore to travel to Seattle for PASS Summit. During my Green is Good – Red is Bad session someone asked me if the PowerBi that I showed at the end would work with any Pester Test Results object and I said (without thinking) that it would.

It turns out that the PowerBi that I had set up for that session will work with my function to run Pester Tests against an Ola Hallengren installation but some of the formatting and custom columns were specific to that test.

I said that I would share a Power Bi file that people could plug any Pester Test Results into. This is the first iteration of that. I doubt that it will work for every single test but I think it will be a good starting point for people to use.

This is how to use it

Download the file from here.

Run your Pester Tests using the PassThru Parameter and set the results to a variable, you can also use the Show Parameter to reduce the output of the tests to the screen (and also speed up the tests)

$PesterResults = Invoke-Pester -Script  C:\temp\PBI-Test01.ps1 -Show Summary -PassThru

Then we convert the $PesterResults object into a JSON file

$PesterResults.TestResult | ConvertTo-Json -Depth 5 | Out-File C:\temp\pbi-test.json

Open the Power Bi file you downloaded

Click Home

then the words “Edit Queries”

then data source settings,

highlight the filename and click change source

then navigate to the JSON file you just created, click ok and close and the apply changes.

Which will load the data from the JSON file and display your pester results. You can then save this file with a new name and keep the template for other tests.

It’s not going to be perfect

It’s not going to work in all circumstances and I expect that with some test results it will display the results in a less than optimal manner but you should be able to modify this to suit your needs.

Please give it a try and see how you get on

Here is a sample report created with Demo 1 from my Green is Good session

You can click around and change the data you can see and also look at the other 4 pages

Here is another one that I created using my dbatools-scripts repo and a config file. Again, have a click around and see what it does.

$Config = (Get-Content GIT:\dbatools-scripts\TestConfig.json) -join "`n" | ConvertFrom-Json
$PesterResults = Invoke-Pester .\dbatools-scripts\ -PassThru
$PesterResults.TestResult | Convertto-Json |Out-File C:\temp\dbatools-scripts-pester.json

 

I also created a quick video showing the process too which I will upload when I am not at 35000 feet!!

Enjoy 🙂 Also, let me know if you think it would be better to have the file in Github which would allow contributions but it would only be seen as a binary file and therefore merging will be difficult. I am happy to do so.

TSQL2sDay – Get-PostRoundup

Posted on by

First an apology, this round up is late!

The reason for that is an error in the PowerShell testing module Pester (That’s not completely true as you shall see!!)

I spoke in Stuttgart at the PowerShell Saturday last weekend and had intended to write this blog post whilst travelling, unfortunately I found a major error in Pester (again not strictly true but it makes a good story!!)

I explained it with this slide in my presentation

Yep, I forgot to pack my NUC with my VMs on it and had to re-write all my demos!!

But anyway, on to the TSQL2sDay posts

What a response. You wonderful people. I salute you with a Rimmer salute

There are 34 TSQL2sDay posts about dbatools, about starting with PowerShell, If you should learn PowerShell, SSAS, SSRS, Log Shipping, backups, restores, Pester, Default settings, best practices, migrations, Warnings in Agent Jobs, sqlpackage, VLFs, CMS, Disabling Named Pipes, Orphaned users, AG Status, AG Agent Jobs, logging, classes, auditing, copying files, ETL and more.

I am really pleased to see so many first timers to the TSQL2sDay blog monthly blog party. Please don’t let this be your only TSQL2sDay post. Come back next month and write a post on that topic.

Here they are below in the media of tweets, so that you can also go and follow these wonderful people who are so willing to share their knowledge. Say thank you to them, ask them questions, interact.

Learn, Share, Network

Volker wrote about testing best practices with dbatools

https://twitter.com/VolkerBachmann/status/907664676150022144

Dave explains why PowerShell is so useful to him in his ETL processes

Steve writes about the time he has saved using PowerShell to automate restores and audit SQL Server instances

https://twitter.com/Steve_TSQL/status/907713842943115264

Nate talks about copying large files like SQL Server backups using BITS with PowerShell

Warren talks about his experience as a beginner, the amount of things he automates and his DBReboot module

THANK YOU every single one and apologies if I have missed anyone!

 

 

Presentation Nerves

Posted on by

My previous post on interviews and a number of conversations this year inspired me to write this post. I am lucky enough to have been selected to speak at numerous events over the past few years and I am really lucky because I thoroughly enjoy doing them. The feedback I receive from those sessions has been wonderful and it seems that in general most people really enjoy them.

This leads to some misconceptions though. Recently people have said to me “Oh I am not like you, I get far to nervous to do a session” and also “I am so glad that you get just as nervous as me before presenting I thought it was just me” even though I have blogged about this before. I think it is important for newer speakers as well as more established ones to know that more presenters than you realise get very nervous before they speak.

Many don’t publicise this (which is fine) but I will. I get nervous before I speak. I know that it doesn’t show when I start my presentation but it is there. My stomach does back flips, my hands shake, I forget to bring things to the room. I worry that I will make a catastrophic mistake or that I’ll open my mouth and nothing will come out.

It’s ok. It doesn’t last very long, it’s gone at the moment I start speaking. Other speakers need a few moments into their session before they stop really feeling those nerves but it goes.

Whenever I am involved in a conversation about nerves and presentations on twitter I respond in the same way

I love this quote by Joan Jett (young people link) To me it means that you should be nervous before speaking because that energy will ensure that you give a good presentation. If you get up to do a presentation and you are blasé or complacent about it this will be obvious to your audience and not in a good way.

So what to do?

Practice

You can’t just approach a presentation knowing that you will be nervous and expect it to be ok. You need to have a background of confidence that your presentation will turn out ok.

You need to practice.

You need to practice your presentation.

You need to practice your presentation out loud.

You need to practice your presentation out loud more than once.

You have to get used to hearing your own voice when presenting. It can be off-putting hearing yourself blathering on and you don’t want that to surprise you or interrupt your flow. This will also help with projecting away from your screen and into the room if you practice correctly. Imagine all the people in the room and try to speak in their direction with your head up and not pointing down at the screen.

You also need to practice your timings, so that you know that your session will fit in the allocated time. Make notes of your timings at certain points in your presentation so that when you are presenting your session you can be aware of whether you are still on your expected time. Some people will speak faster in their actual session than the practice and some slower. As you practice and learn you will understand your own rhythm and cadence and be able to alter it if required. This will help you to build that confidence that your presentation will be ok.

More Practice

You need to practice.

You need to practice your demos.

You need to practice your demos more than once.

Being able to reset your demos and run them through will teach you more skills. Using Pester to make sure your environment is in place correctly will help.

Run your demos with your machine set up as it will be for the presentation. If you need to have PowerPoint, SSMS, Visual Studio, Visual Studio Code and three SQL instances running then practice with them all running. You should do this so that your timings when running your demos are the same as when you actual present your session. This is even more important if you are doing a webinar as that software will require some of your machines resources which may slow your demo down.

Knowing that your demos are consistently repeatable and how long they will take will also help to give you the confidence that your presentation will be ok.

Deal with them

If we accept that you will have nerves and that’s not a bad thing you have to be able to deal with them, to use them to make your presentation rock.

This is a distinctly personal thing and I have no idea what will work for you. You will have to try some things and see if they work or not. Recently I found a new way for myself

Normally I like to be in the room I will be presenting in before I do my session as this gives me something that I can listen to, I can see and feel the layout of the room and also usually prepare my laptop with the correct programmes and run Pester to make sure all is as it should be for my demos. In Portugal I was chatting with someone and missed the start of the session and because of the room layout I did not want to disturb the presenter before me. Slava Oks was giving a presentation which I started to watch and it was so mind-melting I completely forgot that I was presenting in the next time slot! Surprisingly, I had almost no time to be nervous and for this time that was a good thing. The fact that I had already opened my presentation and run my Pester tests also helped.

Some speakers like to be amongst the hustle and bustle of a common area. Some like the peace and quiet of a speaker room or work area. Some put their headphones on. Some go outside. Some pace up and down. Some sit quietly. Many sit in a session in the room. Find the one that works for you.

A few deep breaths

Then just before you are giving your presentation take a few deep breathes, reassure yourself that it’s all good and go and be amazing.

Deep breaths will also be useful if you start to feel nervousness overtaking you during your session. Stop, take a deep breath and carry on.

Incidentally, during a presentation in Exeter at my first SQL Saturday I felt decidedly light-headed and as if I was going to pass out. I had literally forgotten to breathe!

What about…… ?

Don’t forget to leave time for questions at the end. Don’t practice to fill all of the allotted time with your presentation. You will need some time for the audience to ask you questions about your presentations.

Having people ask you questions is a good thing. It means that people are engaged in your presentation and interested in what you have shared. Well done, you have achieved what you set out to do and this is some validation

Repeat the question

Repeating the question that you are asked is recommended best practice for presentations but it has another advantage to you. It allows you a little thinking time to organise your thoughts and calm your nerves if needed.

I don’t know

It’s ok to answer a question with I don’t know. Follow up by asking if anyone in the audience can add some value or say I will research that and find out for you come and give me your contact details afterwards.

Feedback

Some events will provide you with feedback from your attendees. You can also ask your friends or other friendly community members for feedback on your session. Use this to improve. Don’t take all the feedback to heart. Look for trends in the data. Don’t let the poor feedback get you down and don’t let the good feedback go to your head (Remember the complacent quote at the top of this post!)

On a side note, whilst providing a score for feedback is useful, what is more useful is some reasoning behind the score. Remember also that the speaker is a human being with feelings. Be kind whilst being constructive.

Your knowledge

Don’t let worry about nerves prevent us from hearing the great knowledge and experience that you have to share. You wont be alone in feeling nervous and you can help yourself to overcome those nerves and get as much out of speaking as I do.

You will find members of the SQL community wiling to help you if you visit the SQL Community Slack you can ask questions in #presentingorspeaking

 

Writing Dynamic and Random Tests Cases for Pester

Posted on by

I have written a module SQLDiagAPI for consuming the SQL Server Diagnostics API with PowerShell. I blogged about how I used Pester to develop one of the functions . Whilst writing Get-SQLDiagFix I wrote some Pester Tests to make sure that the output from the code was as expected.

Pester

For those that don’t know. Pester is a PowerShell module for Test Driven Development

Pester provides a framework for running unit tests to execute and validate PowerShell commands from within PowerShell. Pester consists of a simple set of functions that expose a testing domain-specific language (DSL) for isolating, running, evaluating and reporting the results of PowerShell commands

If you have PowerShell version 5 then you will have Pester already installed although you should update it to the latest version. If not you can get Pester from the PowerShell Gallery follow the instructions on that page to install it. This is a good post to start learning about Pester

The Command Get-SQLDiagFix

Get-SQLDiagFix  returns the Product Name, Feature Name/Area, KB Number, Title and URL for the Fixes in the Cumulative Updates returned from the SQL Server Diagnostics Recommendations API. One Fix looks like this

07 - Get-SQLDiagFix result.png

This is how I wrote the Pester tests for that command

Mocking the results

In my describe block for each function I mock Get-SQLDiagRecommendations. This is the command that each of the current available commands in the module use to get the recommendations from the SQL Server Diagnostic Recommendations API. I did this by creating a json file from the API and saving it in a json folder inside the tests folder

01 - JSON folder.png

I can then mock Get-SQLDiagRecommendations inside a BeforeAll code block using

Describe "Get-SQLDiagFix" -Tags Build , Unit, Fix {
BeforeAll {
    $Recommendations = (Get-Content $PSScriptRoot\json\recommendations.JSON) -join "`n" | ConvertFrom-Json
    Mock Get-SQLDiagRecommendations {$Recommendations}
}
This means that every time the code in the test calls Get-SQLDiagRecommendations it will not use the internet to connect to the API and return an object. Instead it will return the $Recommendations object which is loaded from a file on the file system. I am not, therefore, depending on any external factors and I have a known set of data for my test results.
I also have a set of mocks in my Output Context code block
Context "Output" {
BeforeAll {
    $Fixes = (Get-Content $PSScriptRoot\json\fix.JSON) -join "`n" | ConvertFrom-Json
    $Products = Get-SQLDiagProduct
    $Features = Get-SQLDiagFeature
}

The fixes.json is a file which was created from the recommendations.json and only contains the properties returned by GetSQLDiagFix which is what we are testing here. I can set variables for Products and Features using the commands from the module as these will call Get-SQLDiagRecommendations which we have already mocked.

Test All of the Fixes

I can now test that the code I have written for Get-SQLDiagFix returns the correct data without any parameters using this test with Compare-Object.

It "returns all of the fixes with no parameter" {
    Compare-Object (Get-SQLDiagFix) $Fixes | Should BeNullOrEmpty
}
If there is no difference between the object returned from Get-SQLDiagFix and the $fixes object which uses the json file then the code is working as expected and the test will pass.

Test Cases

I learned about test cases from Mike Robbins blog post. Test cases enable you to provide a hash table of options and loop through the same test for each of them. Here is an example

There are the following products in the Recommendation API

  • SQL Server 2012 SP3
  • SQL Server 2016 SP1
  • SQL Server 2016 RTM
  • SQL Server 2014 SP1
  • SQL Server 2014 SP2
and I want to run a test for each product to check that the fixes returned from Get-SQLDiagFix for that product match the $fixes object filtered by Product for those products. Here is the code
$TestCases = @{ ProductName = 'SQL Server 2012 SP3'},
@{ ProductName = 'SQL Server 2016 SP1'},
@{ ProductName = 'SQL Server 2016 RTM'},
@{ ProductName ='SQL Server 2014 SP1'},
@{ ProductName = 'SQL Server 2014 SP2'}
It "Returns the correct results with a single product parameter &amp;amp;amp;amp;lt;ProductName&amp;amp;amp;amp;gt;" -TestCases $TestCases {
    param($productname)
    $results = $fixes.Where{$_.Product -in $ProductName}
    Compare-Object (Get-SQLDiagFix -Product $productname) $results | Should BeNullOrEmpty
}
You can click on the image below to see a larger, more readable version.
02 Test Cases.png

The $TestCases variable holds an array of hashtables, one for each product with a Name that matches the parameter that I use in the test and a value of the product name.

I wrote one test, one It code block.  I refer to the product in the title inside <> using the same name as the name in the hashtable. The test (It) needs a parameter of -TestCases with a value (in this example) of the $TestCases variable we have just defined. It also needs a param block with a parameter that matches the Name value from the hashtables.

The expected test results are placed in a $results variable by filtering the $Fixes variable (defined in the BeforeAll code block above) by the parameter $Productname

The test will then run for each of the test cases in the $TestCases variable comparing the results of Get-SQLDiagFix -Product $Productname with the expected results from the $fixes variable

Here are the test results

03 - product test results.png

Multiple Products in Test Cases

I also want to test that Get-SQLDiagFix will work for multiple Products. I need to create TestCases for those too. I do that in exactly the same way

$TestCases = @{ ProductName = 'SQL Server 2012 SP3', 'SQL Server 2016 SP1'},
@{ ProductName = 'SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM'},
@{ ProductName = 'SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1'},
@{ ProductName = 'SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1', 'SQL Server 2014 SP2'}
    It "Returns the correct results with multiple product parameter <ProductName>" -TestCases $TestCases {
        param($productname)
        $results = $fixes.Where{$_.Product -in $ProductName}
        Compare-Object (Get-SQLDiagFix -Product $productname) $results | Should BeNullOrEmpty
}
Which looks like this when the tests run
04 - mulitple product test results.png

Single Feature Dynamic Test Cases

Get-SQLDiagFix can also filter the fixes by feature area. The features are returned from Get-SQLDiagFeature. This means that I can create a test for each of the features by using the $features variable which was defined in the BeforeAll block as

$Features = Get-SQLDiagFeature

Then I can dynamically create test cases using

$TestCases = @()
$Features | Foreach-Object {$TestCases += @{Feature = $_}}
It "Returns the correct results with a single feature <Feature>" -TestCases $TestCases {
    param($Feature)
    $results = $fixes.Where{$_.Feature -in $Feature}
    Compare-Object (Get-SQLDiagFix -Feature $Feature) $results | Should BeNullOrEmpty
}

and the results look like

05 - single feature test results.png

Random Dynamic Multiple Feature Test Cases

I also need to test that Get-SQLDiagFix returns the correct results for multiple features and whilst I could create those by hand like the products example above why not let PowerShell do that for me?

I created 10 test cases. Each one has a random number of features between 2 and the number of features.  I can then write one test to make use of those test cases. This is how I do that

## Generate 10 TestCases of a random number of Features
$TestCases = @()
$x = 10
While ($x -gt 0) {
    ## We are testing multiples so we need at least 2
    $Number = Get-Random -Maximum $Features.Count -Minimum 2
    $Test = @()
    While ($Number -gt 0) {
        $Test += Get-Random $Features
        $Number --
    }
## Need unique values
$Test = $test | Select-Object -Unique
$TestCases += @{Feature = $Test}
$X --
}
It "Returns the correct results with multiple features <Feature>" -TestCases $TestCases {
 param($Feature)
 $results = $fixes.Where{$_.Feature -in $Feature}
 Compare-Object (Get-SQLDiagFix -Feature $Feature) $results | Should BeNullOrEmpty
 }

Now there are 10 tests each with a random number of features and the results look like this. Each time the test is run it will use a different set of features for each of the 10 tests but I will know that I am testing that the code will return the correct results for multiple features

06 - multiple features.png

Two Sets of Test Cases?

It is also possible for Get-SQLDiagFix to have one or more products and one or more features passed as parameters, which obviously also need to be tested to ensure the code is returning the correct results. As Pester is just PowerShell we can use normal PowerShell code. This means that I can test for a single product and a single feature using a foreach loop and Test Cases like this

foreach ($Product in $Products) {
    $TestCases = @()
    $Features = Get-SQLDiagFeature -Product $Product
    $Features | Foreach-Object {$TestCases += @{Feature = $_}}
    It "Returns the correct results for a single product parameter $Product with a single feature <Feature>" -TestCases $TestCases {
        param($Feature)
        $results = $fixes.Where{$_.Product -eq $product -and $_.Feature -in $Feature}
        Compare-Object (Get-SQLDiagFix -Product $Product -Feature $Feature) $results | Should BeNullOrEmpty
    }
}

To test for a single product and multiple features I use this code

 foreach ($Product in $Products) {
    ## Generate 10 TestCases of a random number of Features
    $TestCases = @()
    $x = 10
    While ($x -gt 0) {
        ## We are testing multiples so we need at least 2
        $Number = Get-Random -Maximum $Features.Count -Minimum 2
        $Test = @()
        While ($Number -gt 0) {
            $Test += Get-Random $Features
            $Number --
        }
        ## Need unique values
        $Test = $test | Select-Object -Unique
        $TestCases += @{Feature = $Test}
        $X --
    }
    It "Returns the correct results for a single product parameter $Product with a multiple features <Feature>" -TestCases $TestCases {
        param($Feature)
        $Test = (Get-SQLDiagFix -Product $Product -Feature $Feature)
        ## If there are no results Compare-Object bombs out even though it is correct
        ## This is a risky fix for that
        if ($Test) {
        $results = $fixes.Where{$_.Product -eq $product -and $_.Feature -in $Feature}
        Compare-Object $test $results | Should BeNullOrEmpty
        }
    }
 }

Because it is dynamically creating the values for the two parameters, I have to check that there are some results to test on line 23 as Compare-Object will throw an error if the object to be compared is empty. I need to do this because it is possible for the test to pick products and features in a combination that there are no fixes in the results.

The reason I have commented it as a risky fix is because if someone changes the code and Get-SQLDiagFix does not return any results then the test would not run and therefore there would be no information from this test that the code had a bug. However, in this suite of tests there are many tests that would fail in that scenario but be careful in your own usage.

I test for multiple products with a single feature and multiple products with multiple features like this

 $Products = @('SQL Server 2012 SP3', 'SQL Server 2016 SP1'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1', 'SQL Server 2014 SP2')
 foreach ($Product in $Products) {
     $TestCases = @()
     $Features = Get-SQLDiagFeature -Product $Product
     $Features | Foreach-Object {$TestCases += @{Feature = $_}}
     It "Returns the correct results for multiple products parameter $Product with a single feature <Feature>" -TestCases $TestCases {
         param($Feature)
         $results = $fixes.Where{$_.Product -in $product -and $_.Feature -in $Feature}
         Compare-Object (Get-SQLDiagFix -Product $Product -Feature $Feature) $results | Should BeNullOrEmpty
     }
}
 $Products = @('SQL Server 2012 SP3', 'SQL Server 2016 SP1'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1', 'SQL Server 2014 SP2')
 foreach ($Product in $Products) {
     ## Generate 10 TestCases of a random number of Features
     $TestCases = @()
     $x = 10
     While ($x -gt 0) {
         ## We are testing multiples so we need at least 2
         $Number = Get-Random -Maximum $Features.Count -Minimum 2
         $Test = @()
         While ($Number -gt 0) {
             $Test += Get-Random $Features
             $Number --
         }
     ## Need unique values
     $Test = $test | Select-Object -Unique
     $TestCases += @{Feature = $Test}
     $X --
 }
 It "Returns the correct results for multiple products parameter $Product with a multiple feature <Feature>" -TestCases $TestCases {
     param($Feature)
     $Test = (Get-SQLDiagFix -Product $Product -Feature $Feature)
     ## Annoyingly if there are no results Compare-Object bombs out even though it is correct
     ## This is a risky fix for that
     if ($Test) {
         $results = $fixes.Where{$_.Product -in $product -and $_.Feature -in $Feature}
         Compare-Object $test $results | Should BeNullOrEmpty
        }
    }
 }

You can see all of the unit tests for the SQLDiagAPI module in my GitHub repository

The module is available on the PowerShell Gallery which means that you can install it using

Install-Module SQLDiagAPI