notebooks/NotDotNet/dbachecks/03-dbachecks - Simple configurations.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "powershell",
            "display_name": "PowerShell"
        },
        "language_info": {
            "name": "powershell",
            "codemirror_mode": "shell",
            "mimetype": "text/x-sh",
            "file_extension": ".ps1"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "![First Power Bi](.\\images\\dbachecks-logo.png )\r\n",
                "# dbachecks - Configurations\r\n",
                "\r\n",
                "You can use dbachecks on the command line as we have seen in the previous notebook but you will not always want to have the default settings for the checks\r\n",
                "\r\n",
                "You can alter any of the configurations using the `Set-DbcConfig` command.\r\n",
                "\r\n",
                "This configuration will be the configuration that is used until `Reset-DbcConfig` is run or the configuration is changed.\r\n",
                "This configuration will be used for the user who has run the code.\r\n",
                "\r\n",
                "Again, this notebook requires that you have the containers set up from the Notebook 00 - Setting up the containers for the rest of the containers.ipynb\r\n",
                "\r\n",
                "If you have altered the folder path you will need to alter it below.\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "eade8b6a-a343-45c6-b917-f018cb2d0e7e"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Setting the SqlInstances and ComputerNames\r\n",
                "\r\n",
                "A useful configuration to set is the list of SQL Instances and Computer Names that you want the checks to run against. There are two configurations that will do that.\r\n",
                "\r\n",
                "- app.sqlinstance will set the list of SQL Instances\r\n",
                "- app.computername will set the list of ComputerNames\r\n",
                "\r\n",
                "## Setting a configuration\r\n",
                "\r\n",
                "You set a configuration using the `Set-DbcConfig` command. You provide the name (which will auto-complete) and the value\r\n",
                "\r\n",
                "```powershell\r\n",
                "Set-DbcConfig -Name ConfigName -Value ConfigValue\r\n",
                "```\r\n",
                "\r\n",
                "For the app.sqlinstance you could even set this from your registerd server list or CMS\r\n",
                "\r\n",
                "```powershell\r\n",
                "$Production = (Get-DbaRegisteredServer -SqlInstance CMSServer -Group \"Production\").ServerName\r\n",
                "Set-DbcConfig -Name app.sqlinstance -Value $Production\r\n",
                "```\r\n",
                "You can set the SqlInstance and the ComputerName for the checks in this notebook\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "caaebcc0-a835-4fcf-87b9-4edbe8fed032"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Just in case the config has been altered\r\n",
                "$null = Reset-DbcConfig\r\n",
                "$SqlInstances = 'localhost,15592','localhost,15593'\r\n",
                "Set-DbcConfig -Name app.sqlinstance -Value $SqlInstances\r\n",
                "Set-DbcConfig -Name app.computername -Value $SqlInstances\r\n",
                "$FolderPath = $Env:USERPROFILE + '\\Documents\\dbachecks\\'\r\n",
                "$SqlCredential = Import-Clixml -Path $FolderPath\\sqladmin.cred"
            ],
            "metadata": {
                "azdata_cell_guid": "8e8c36f2-b4b0-4d36-8dec-1f474a3e27a5"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\nName             Value                              Description                                                       \n----             -----                              -----------                                                       \napp.sqlinstance  {localhost,15592, localhost,15593} List of SQL Server instances that SQL-based tests will run against\napp.computername {localhost,15592, localhost,15593} List of Windows Servers that Windows-based tests will run against \n\n\n"
                }
            ],
            "execution_count": 11
        },
        {
            "cell_type": "markdown",
            "source": [
                "and you will no longer need to specify them when calling `Invoke-DbcCheck`"
            ],
            "metadata": {
                "azdata_cell_guid": "d3fa1aee-8bca-4e10-9679-1d546795d3ad"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Invalid Database Owners\r\n",
                "\r\n",
                "You may want to check that databases are not owned by certain accounts.\r\n",
                "\r\n",
                "By default the `InvalidDatabaseOwner` checks that the databases are not owned by the sa account. When we run this check against the containers we pass all of the checks"
            ],
            "metadata": {
                "azdata_cell_guid": "989a170f-842c-4f6a-8cc8-539a7a8ea930"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Invoke-DbcCheck -SqlCredential $SqlCredential  InvalidDatabaseOwner"
            ],
            "metadata": {
                "azdata_cell_guid": "3627b53c-63a5-4e21-9a2d-96b1a092c913",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "Executing all tests in 'C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1' with Tags InvalidDatabaseOwner\n\nExecuting script C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1\n\n  Describing Invalid Database Owner\n\n    Context Testing Database Owners on localhost,15592\n      [+] Database AdventureWorks2017 - owner sqladmin should Not be in this list ( sa ) on localhost,15592 2ms\n      [+] Database Northwind - owner sqladmin should Not be in this list ( sa ) on localhost,15592 8ms\n      [+] Database pubs - owner sqladmin should Not be in this list ( sa ) on localhost,15592 3ms\n\n  Describing Invalid Database Owner\n\n    Context Testing Database Owners on localhost,15593\n      [+] Database AdventureWorks2017 - owner sqladmin should Not be in this list ( sa ) on localhost,15593 4ms\n      [+] Database Northwind - owner sqladmin should Not be in this list ( sa ) on localhost,15593 2ms\n      [+] Database pubs - owner sqladmin should Not be in this list ( sa ) on localhost,15593 2ms\nTests completed in 663ms\nTests Passed: 6, Failed: 0, Skipped: 0, Pending: 0, Inconclusive: 0 \n"
                }
            ],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": [
                "If our organistation requirements are that no databases should be owned by members of the DBA Team user accounts, we can alter the configuration to check for that. \r\n",
                "\r\n",
                "To find out which configuration we need to change we can use `Get-DbcCheck`"
            ],
            "metadata": {
                "azdata_cell_guid": "0b36fbd4-d266-493e-9aef-f6c66f76f97c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbcCheck -Pattern InvalidDatabaseOwner | Format-List"
            ],
            "metadata": {
                "azdata_cell_guid": "c03ce802-b2e9-4da0-b302-0f916e03ece7"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\n\nGroup       : Database\nType        : Sqlinstance\nUniqueTag   : InvalidDatabaseOwner\nAllTags     : InvalidDatabaseOwner, Medium, Database\nConfig      : app.sqlinstance policy.invaliddbowner.name policy.invaliddbowner.excludedb \nDescription : Tests that the Database Owner is NOT in the specified (default blank) list\n\n\n\n"
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "markdown",
            "source": [
                "We can see that there are 3 configurations that this check will use\r\n",
                "- app.sqlinstance\r\n",
                "- policy.invaliddbowner.name \r\n",
                "- policy.invaliddbowner.excludedb \r\n",
                "\r\n",
                "We can get further information about the checks with `Get-DbcConfig`"
            ],
            "metadata": {
                "azdata_cell_guid": "6f7e0921-11ae-4cca-a8c5-4cd4e9f2abe3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbcConfig -Name policy.invaliddbowner.name | Format-List"
            ],
            "metadata": {
                "azdata_cell_guid": "4318390f-f330-496e-aecb-a7d546bd62e4"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\n\nName        : policy.invaliddbowner.name\nValue       : sa\nDescription : The database owner account should not be this user\n\n\n\n"
                }
            ],
            "execution_count": 8
        },
        {
            "cell_type": "markdown",
            "source": [
                "We can change the value of that configuration item using `Set-DbcConfig`. We will set it to the members of the DBA Team AD Group\r\n",
                "\r\n",
                "This is an example - don't run this unless you are connected to THEBEARD.local domain and your DBAs are in a group called SQL_DBAs_The_Cool_Ones :-)"
            ],
            "metadata": {
                "azdata_cell_guid": "8b57c94f-95f7-4575-a2e4-db1e84df87e4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$DBA_Accounts =  (Get-ADGroup -Identity 'CN=SQL_DBAs_The_Cool_Ones,OU=TheBeardGroups,OU=TheBeard,DC=TheBeard,DC=local' |Get-ADGroupMember).ForEach{\"THEBEARD\\$($Psitem.SamAccountName)\"} \r\n",
                "Set-DbcConfig -Name policy.invaliddbowner.name -Value $DBA_Accounts"
            ],
            "metadata": {
                "azdata_cell_guid": "1d12f62f-107a-440c-b87d-b0b8c2a2ba08"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\nName                       Value                                                                Description            \n----                       -----                                                                -----------            \npolicy.invaliddbowner.name {THEBEARD\\Rob, THEBEARD\\DBA, THEBEARD\\wdurkin, THEBEARD\\gsartori...} The database owner a...\n\n\n"
                }
            ],
            "execution_count": 19
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Altering a Configuration\r\n",
                "This is how you can alter a check to test the values that YOU want by setting a configuration.  \r\n",
                "We have renamed the sa acccount and disabled it in the containers. There is a sysadmin account called `sysadmin` created.  \r\n",
                "Perhaps we do not want our databases owned by the new sysadmin account `sqladmin` We can set that value in the configuration and run the test"
            ],
            "metadata": {
                "azdata_cell_guid": "cbf7ed68-9fe1-42a7-a890-a06acf016cfd"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Set-DbcConfig -Name policy.invaliddbowner.name -Value sqladmin\r\n",
                "Invoke-DbcCheck -SqlCredential $SqlCredential -Check InvalidDatabaseOwner"
            ],
            "metadata": {
                "azdata_cell_guid": "c2aed9d7-6fcc-4c04-b91c-5007a8ddc27a"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\nExecuting all tests in 'C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1' with Tags InvalidDatabaseOwner\n\nExecuting script C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1\n\n  Describing Invalid Database Owner\n\n    Context Testing Database Owners on localhost,15592\n      [-] Database AdventureWorks2017 - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15592 3ms\n        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\n        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\n        at <ScriptBlock>, C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1: line 176\n      [-] Database Northwind - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15592 5ms\n        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\n        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\n        at <ScriptBlock>, C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1: line 176\n      [-] Database pubs - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15592 22ms\n        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\n        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\n        at <ScriptBlock>, C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1: line 176\n\n  Describing Invalid Database Owner\n\n    Context Testing Database Owners on localhost,15593\n      [-] Database AdventureWorks2017 - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15593 6ms\n        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\n        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\n        at <ScriptBlock>, C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1: line 176\n      [-] Database Northwind - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15593 4ms\n        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\n        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\n        at <ScriptBlock>, C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1: line 176\n      [-] Database pubs - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15593 4ms\n        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\n        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\n        at <ScriptBlock>, C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1: line 176\nTests completed in 711ms\nTests Passed: 0, Failed: 6, Skipped: 0, Pending: 0, Inconclusive: 0 \nName                       Value    Description                                       \n----                       -----    -----------                                       \npolicy.invaliddbowner.name sqladmin The database owner account should not be this user\n\n\n"
                }
            ],
            "execution_count": 13
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Running a test, fixing the errors and re-running\r\n",
                "\r\n",
                "Ah\r\n",
                "\r\n",
                "We had better have a word with that fellow Rob and alter all of those database owners!! \r\n",
                "\r\n",
                "This is how we could ask our junior DBA to do this and test they have done so correctly\r\n",
                "\r\n",
                "We could change the owner with `Get-DbaDatabase -SqlInstance sql2016n1 -ExcludeSystem -Owner THEBEARD\\Rob | Set-DbaDbOwner -TargetLogin THEBEARD\\DatabaseOwner`\r\n",
                "\r\n",
                "We can also set a confiuguration for our desired database owner and test for that. With dbachecks we can check for both Valid and Invalid database owner accounts.\r\n",
                "\r\n",
                "The code below will \r\n",
                "- Get all databases owned by sysadmin and set the owner to be thebeard\r\n",
                "- Run the check to ensure that no databases are owned by sysadmin because we set that configuration above\r\n",
                "- Set a configuration for the database owner we expect to thebeard\r\n",
                "- Run the check to ensure that the databases are owned by the thebeard\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "d76f851d-c6ae-4978-856c-3e9ab76459be"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Set the correct owner\r\n",
                "$null = Get-DbaDatabase -SqlInstance $SqlInstances -SqlCredential $SqlCredential -ExcludeSystem -Owner sqladmin | Set-DbaDbOwner -TargetLogin thebeard\r\n",
                "# Check that we do not have any databases owned by the unexpected accounts\r\n",
                "Invoke-DbcCheck -SqlCredential $SqlCredential -Check InvalidDatabaseOwner\r\n",
                "# Set the value for expected database owner\r\n",
                "Set-DbcConfig -Name policy.validdbowner.name -Value thebeard\r\n",
                "# Check our databases  are owned by the correct account\r\n",
                "Invoke-DbcCheck -SqlCredential $SqlCredential -Check ValidDatabaseOwner "
            ],
            "metadata": {
                "azdata_cell_guid": "e567857c-6450-40d6-b02e-685a8fbd29e4"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "Executing all tests in 'C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1' with Tags InvalidDatabaseOwner\n\nExecuting script C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1\n\n  Describing Invalid Database Owner\n\n    Context Testing Database Owners on localhost,15592\n      [+] Database AdventureWorks2017 - owner thebeard should Not be in this list ( sqladmin ) on localhost,15592 1ms\n      [+] Database Northwind - owner thebeard should Not be in this list ( sqladmin ) on localhost,15592 2ms\n      [+] Database pubs - owner thebeard should Not be in this list ( sqladmin ) on localhost,15592 5ms\n\n  Describing Invalid Database Owner\n\n    Context Testing Database Owners on localhost,15593\n      [+] Database AdventureWorks2017 - owner thebeard should Not be in this list ( sqladmin ) on localhost,15593 3ms\n      [+] Database Northwind - owner thebeard should Not be in this list ( sqladmin ) on localhost,15593 1ms\n      [+] Database pubs - owner thebeard should Not be in this list ( sqladmin ) on localhost,15593 1ms\nTests completed in 662ms\nTests Passed: 6, Failed: 0, Skipped: 0, Pending: 0, Inconclusive: 0 \n\nExecuting all tests in 'C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1' with Tags ValidDatabaseOwner\n\nExecuting script C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1\n\n  Describing Valid Database Owner\n\n    Context Testing Database Owners on localhost,15592\n      [+] Database AdventureWorks2017 - owner thebeard should be in this list ( thebeard ) on localhost,15592 2ms\n      [+] Database Northwind - owner thebeard should be in this list ( thebeard ) on localhost,15592 1ms\n      [+] Database pubs - owner thebeard should be in this list ( thebeard ) on localhost,15592 2ms\n\n  Describing Valid Database Owner\n\n    Context Testing Database Owners on localhost,15593\n      [+] Database AdventureWorks2017 - owner thebeard should be in this list ( thebeard ) on localhost,15593 1ms\n      [+] Database Northwind - owner thebeard should be in this list ( thebeard ) on localhost,15593 1ms\n      [+] Database pubs - owner thebeard should be in this list ( thebeard ) on localhost,15593 1ms\nTests completed in 542ms\nTests Passed: 6, Failed: 0, Skipped: 0, Pending: 0, Inconclusive: 0 \nName                     Value    Description                                   \n----                     -----    -----------                                   \npolicy.validdbowner.name thebeard The database owner account should be this user\n\n\n"
                }
            ],
            "execution_count": 14
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Ensuring Databases exist\r\n",
                "\r\n",
                "You might want to test that a particular database exists on your instance. This could be your DBA database or perhaps you have a check for your companies software system that you want to run on a clients estate and your software requires certain databases to exist \r\n",
                "\r\n",
                "You can run the default `DatabaseExists` which will check that the system databases exist as you did in the quick examples notebook."
            ],
            "metadata": {
                "azdata_cell_guid": "ebca0026-3a00-496b-aeec-730f3d5bd799"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Invoke-DbcCheck -SqlCredential $SqlCredential -Check DatabaseExists"
            ],
            "metadata": {
                "azdata_cell_guid": "6657827a-36ca-435d-9624-4e18c20d8593"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "Executing all tests in 'C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1' with Tags DatabaseExists\n\nExecuting script C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1\n\n  Describing Database Exists\n\n    Context Database exists on localhost,15592\n      [+] Database master should exist on  163ms\n      [+] Database msdb should exist on  126ms\n      [+] Database tempdb should exist on  126ms\n      [+] Database model should exist on  121ms\n\n  Describing Database Exists\n\n    Context Database exists on localhost,15593\n      [+] Database master should exist on  117ms\n      [+] Database msdb should exist on  122ms\n      [+] Database tempdb should exist on  123ms\n      [+] Database model should exist on  116ms\nTests completed in 1.72s\nTests Passed: 8, Failed: 0, Skipped: 0, Pending: 0, Inconclusive: 0 \n"
                }
            ],
            "execution_count": 15
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Finding the configurations for the check\r\n",
                "\r\n",
                "You can find the configurations that you can set for a check with `Get-DbcCheck`"
            ],
            "metadata": {
                "azdata_cell_guid": "7ee90397-1031-4fe3-b679-6bb017b3f6c1"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbcCheck DatabaseExists | Format-List"
            ],
            "metadata": {
                "azdata_cell_guid": "80668f2f-9a92-4930-89bb-43861de533ba"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\n\nGroup       : Database\nType        : Sqlinstance\nUniqueTag   : DatabaseExists\nAllTags     : DatabaseExists, Database\nConfig      : app.sqlinstance database.exists \nDescription : Tests that the databases are specified are on the instance (Note - Does not check if they are available \n              - Use DatabaseStatus for that))\n\n\n\n"
                }
            ],
            "execution_count": 16
        },
        {
            "cell_type": "markdown",
            "source": [
                "\r\n",
                "## Setting the configuration for a check\r\n",
                "You can see that you can set the configuration `database.exists` Note that we are using the `-Append` parameter to add to the configuration instead of replacing it."
            ],
            "metadata": {
                "azdata_cell_guid": "b03439f2-0c50-4487-bec3-cc2001a6ab4a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Set-DbcConfig -Name database.exists -Value 'pubs','NorthWind','AdventureWorks2017','WideWorldImporters' -Append"
            ],
            "metadata": {
                "azdata_cell_guid": "0c09af8d-8447-403b-8096-282ec554fa0e"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\nName            Value                            Description                                   \n----            -----                            -----------                                   \ndatabase.exists {master, msdb, tempdb, model...} The databases we expect to be on the instances\n\n\n"
                }
            ],
            "execution_count": 17
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Run the check with the new configuration\r\n",
                "and then we can run the check again and confirm that we have all of the expected databases."
            ],
            "metadata": {
                "azdata_cell_guid": "732fb678-3816-46ec-a54c-2e4a84244183"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Invoke-DbcCheck -SqlCredential $SqlCredential -Check DatabaseExists"
            ],
            "metadata": {
                "azdata_cell_guid": "1d421c8c-bc48-43a9-a15a-d5c95c5c7b68"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\nExecuting all tests in 'C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1' with Tags DatabaseExists\n\nExecuting script C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1\n\n  Describing Database Exists\n\n    Context Database exists on localhost,15592\n      [+] Database master should exist on  119ms\n      [+] Database msdb should exist on  116ms\n      [+] Database tempdb should exist on  114ms\n      [+] Database model should exist on  162ms\n      [+] Database pubs should exist on  153ms\n      [+] Database NorthWind should exist on  149ms\n      [+] Database AdventureWorks2017 should exist on  174ms\n      [-] Database WideWorldImporters should exist on  360ms\n        Expected 'WideWorldImporters' to be found in collection @('AdventureWorks2017', 'master', 'model', 'msdb', 'Northwind', 'pubs', 'tempdb'), because We expect WideWorldImporters to be on localhost,15592, but it was not found.\n        70:     $Actual | Should -Contain $expecteddb -Because \"We expect $expecteddb to be on $Instance\"\n        at Assert-DatabaseExists, C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\internal\\assertions\\Database.Assertions.ps1: line 70\n        at <ScriptBlock>, C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1: line 935\n\n  Describing Database Exists\n\n    Context Database exists on localhost,15593\n      [+] Database master should exist on  129ms\n      [+] Database msdb should exist on  132ms\n      [+] Database tempdb should exist on  120ms\n      [+] Database model should exist on  123ms\n      [+] Database pubs should exist on  118ms\n      [+] Database NorthWind should exist on  125ms\n      [+] Database AdventureWorks2017 should exist on  135ms\n      [-] Database WideWorldImporters should exist on  130ms\n        Expected 'WideWorldImporters' to be found in collection @('AdventureWorks2017', 'master', 'model', 'msdb', 'Northwind', 'pubs', 'tempdb'), because We expect WideWorldImporters to be on localhost,15593, but it was not found.\n        70:     $Actual | Should -Contain $expecteddb -Because \"We expect $expecteddb to be on $Instance\"\n        at Assert-DatabaseExists, C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\internal\\assertions\\Database.Assertions.ps1: line 70\n        at <ScriptBlock>, C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Database.Tests.ps1: line 935\nTests completed in 3.14s\nTests Passed: 14, Failed: 2, Skipped: 0, Pending: 0, Inconclusive: 0 \nName                       Value    Description                                       \n----                       -----    -----------                                       \npolicy.invaliddbowner.name sqladmin The database owner account should not be this user\n\n\n"
                }
            ],
            "execution_count": 18
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Ensuring the Agent Alerts are as expected\r\n",
                "\r\n",
                "You might want to check that you have all of the expected Agent Alerts. By default dbachecks will check for alerts for Severity 16-25 and messageid 823,824,825"
            ],
            "metadata": {
                "azdata_cell_guid": "41f2997a-83b2-493b-bf76-c152e73b903e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Invoke-DbcCheck -SqlCredential $SqlCredential -Check AgentAlert"
            ],
            "metadata": {
                "azdata_cell_guid": "18432613-5b44-41e3-8da9-667d704cebf2"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "Executing all tests in 'C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Agent.Tests.ps1' with Tags AgentAlert\n\nExecuting script C:\\Program Files\\WindowsPowerShell\\Modules\\dbachecks\\1.2.15\\checks\\Agent.Tests.ps1\n\n  Describing Agent Alerts\n\n    Context Testing Agent Alerts Severity exists on localhost,15592\n      [+] localhost,15592 should have Severity 16 Alert 148ms\n      [+] localhost,15592 should have Severity 16 Alert enabled 5ms\n      [+] localhost,15592 should have notification for Severity 16 Alert 10ms\n      [+] localhost,15592 should have Severity 17 Alert 3ms\n      [+] localhost,15592 should have Severity 17 Alert enabled 3ms\n      [+] localhost,15592 should have notification for Severity 17 Alert 4ms\n      [+] localhost,15592 should have Severity 18 Alert 3ms\n      [+] localhost,15592 should have Severity 18 Alert enabled 3ms\n      [+] localhost,15592 should have notification for Severity 18 Alert 3ms\n      [+] localhost,15592 should have Severity 19 Alert 5ms\n      [+] localhost,15592 should have Severity 19 Alert enabled 6ms\n      [+] localhost,15592 should have notification for Severity 19 Alert 4ms\n      [+] localhost,15592 should have Severity 20 Alert 3ms\n      [+] localhost,15592 should have Severity 20 Alert enabled 6ms\n      [+] localhost,15592 should have notification for Severity 20 Alert 3ms\n      [+] localhost,15592 should have Severity 21 Alert 8ms\n      [+] localhost,15592 should have Severity 21 Alert enabled 3ms\n      [+] localhost,15592 should have notification for Severity 21 Alert 3ms\n      [+] localhost,15592 should have Severity 22 Alert 3ms\n      [+] localhost,15592 should have Severity 22 Alert enabled 3ms\n      [+] localhost,15592 should have notification for Severity 22 Alert 7ms\n      [+] localhost,15592 should have Severity 23 Alert 2ms\n      [+] localhost,15592 should have Severity 23 Alert enabled 5ms\n      [+] localhost,15592 should have notification for Severity 23 Alert 3ms\n      [+] localhost,15592 should have Severity 24 Alert 5ms\n      [+] localhost,15592 should have Severity 24 Alert enabled 4ms\n      [+] localhost,15592 should have notification for Severity 24 Alert 3ms\n      [+] localhost,15592 should have Severity 25 Alert 2ms\n      [+] localhost,15592 should have Severity 25 Alert enabled 3ms\n      [+] localhost,15592 should have notification for Severity 25 Alert 3ms\n\n    Context Testing Agent Alerts MessageID exists on localhost,15592\n      [+] localhost,15592 should have Message_ID 823 Alert 7ms\n      [+] localhost,15592 should have Message_ID 823 Alert enabled 9ms\n      [+] localhost,15592 should have notification for Message_ID 823 Alert 5ms\n      [+] localhost,15592 should have Message_ID 824 Alert 5ms\n      [+] localhost,15592 should have Message_ID 824 Alert enabled 3ms\n      [+] localhost,15592 should have notification for Message_ID 824 Alert 4ms\n      [+] localhost,15592 should have Message_ID 825 Alert 5ms\n      [+] localhost,15592 should have Message_ID 825 Alert enabled 3ms\n      [+] localhost,15592 should have notification for Message_ID 825 Alert 2ms\n\n  Describing Agent Alerts\n\n    Context Testing Agent Alerts Severity exists on localhost,15593\n      [+] localhost,15593 should have Severity 16 Alert 5ms\n      [+] localhost,15593 should have Severity 16 Alert enabled 3ms\n      [+] localhost,15593 should have notification for Severity 16 Alert 4ms\n      [+] localhost,15593 should have Severity 17 Alert 3ms\n      [+] localhost,15593 should have Severity 17 Alert enabled 3ms\n      [+] localhost,15593 should have notification for Severity 17 Alert 2ms\n      [+] localhost,15593 should have Severity 18 Alert 2ms\n      [+] localhost,15593 should have Severity 18 Alert enabled 3ms\n      [+] localhost,15593 should have notification for Severity 18 Alert 3ms\n      [+] localhost,15593 should have Severity 19 Alert 3ms\n      [+] localhost,15593 should have Severity 19 Alert enabled 5ms\n      [+] localhost,15593 should have notification for Severity 19 Alert 3ms\n      [+] localhost,15593 should have Severity 20 Alert 2ms\n      [+] localhost,15593 should have Severity 20 Alert enabled 2ms\n      [+] localhost,15593 should have notification for Severity 20 Alert 3ms\n      [+] localhost,15593 should have Severity 21 Alert 4ms\n      [+] localhost,15593 should have Severity 21 Alert enabled 3ms\n      [+] localhost,15593 should have notification for Severity 21 Alert 4ms\n      [+] localhost,15593 should have Severity 22 Alert 4ms\n      [+] localhost,15593 should have Severity 22 Alert enabled 2ms\n      [+] localhost,15593 should have notification for Severity 22 Alert 2ms\n      [+] localhost,15593 should have Severity 23 Alert 5ms\n      [+] localhost,15593 should have Severity 23 Alert enabled 3ms\n      [+] localhost,15593 should have notification for Severity 23 Alert 2ms\n      [+] localhost,15593 should have Severity 24 Alert 4ms\n      [+] localhost,15593 should have Severity 24 Alert enabled 2ms\n      [+] localhost,15593 should have notification for Severity 24 Alert 2ms\n      [+] localhost,15593 should have Severity 25 Alert 2ms\n      [+] localhost,15593 should have Severity 25 Alert enabled 3ms\n      [+] localhost,15593 should have notification for Severity 25 Alert 5ms\n\n    Context Testing Agent Alerts MessageID exists on localhost,15593\n      [+] localhost,15593 should have Message_ID 823 Alert 2ms\n      [+] localhost,15593 should have Message_ID 823 Alert enabled 3ms\n      [+] localhost,15593 should have notification for Message_ID 823 Alert 2ms\n      [+] localhost,15593 should have Message_ID 824 Alert 3ms\n      [+] localhost,15593 should have Message_ID 824 Alert enabled 3ms\n      [+] localhost,15593 should have notification for Message_ID 824 Alert 3ms\n      [+] localhost,15593 should have Message_ID 825 Alert 3ms\n      [+] localhost,15593 should have Message_ID 825 Alert enabled 3ms\n      [+] localhost,15593 should have notification for Message_ID 825 Alert 2ms\nTests completed in 5.95s\nTests Passed: 78, Failed: 0, Skipped: 0, Pending: 0, Inconclusive: 0 \n"
                }
            ],
            "execution_count": 19
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Get the configuration items available for the check\r\n",
                "You can check the configuration items available for the check"
            ],
            "metadata": {
                "azdata_cell_guid": "d9618240-c7af-48d0-b194-2f6afe8ac49c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbcCheck -Pattern AgentAlert | Format-List"
            ],
            "metadata": {
                "azdata_cell_guid": "98c2a0f5-05e3-429e-b896-b25c4fa6ddee"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\n\nGroup       : Agent\nType        : Sqlinstance\nUniqueTag   : AgentAlert\nAllTags     : AgentAlert, Agent\nConfig      : app.sqlinstance agent.alert.Severity agent.alert.messageid agent.alert.Job agent.alert.Notification \nDescription : Tests that there are Agent Alerts set up for the specified (default 16-25) alert severities and ids \n              (default 823-825) and if specified Agent Jobs and/or notifications\n\n\n\n"
                }
            ],
            "execution_count": 20
        },
        {
            "cell_type": "markdown",
            "source": [
                "The configuration items available are\r\n",
                "- app.sqlinstance \r\n",
                "- agent.alert.Severity \r\n",
                "- agent.alert.messageid \r\n",
                "- agent.alert.Job \r\n",
                "- agent.alert.Notification\r\n",
                "\r\n",
                "(Until Rob fixes the code) You can get the configuration values and descriptions for the configuration items"
            ],
            "metadata": {
                "azdata_cell_guid": "5f996f6b-213a-464b-af59-ea8af3169159"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "'app.sqlinstance', 'agent.alert.Severity', 'agent.alert.messageid', 'agent.alert.Job', 'agent.alert.Notification' | ForEach-Object {\r\n",
                "    Get-DbcConfig -Name $PSItem | Format-List\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "4da84c61-fdca-403b-be54-b6a5e2fed90f"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "If you use all of the TigerTeams Agent Alerts then you might want to check that they are installed and enabled. You can alter the configuration to do this as follows. This time we have not used the append parameter and have replaced all of the values."
            ],
            "metadata": {
                "azdata_cell_guid": "d9702c33-8b03-4c62-802d-f44c99e647d9"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$Ids = '1101','1105','1121','1214','17130','17179','17300','17883','17884','17887','17888','17890','2508','2511','28036','3271','3452','3619','3624','5180','5228','5229','5242','5243','5250','5572','5901','605','701','802','823','824','825','832','833','845','855','856','8966','9002','9100'\r\n",
                "Set-DbcConfig -Name agent.alert.messageid -Value $Ids"
            ],
            "metadata": {
                "azdata_cell_guid": "c6028a63-c3eb-405f-8cbf-8f18da21db32"
            },
            "outputs": [],
            "execution_count": 39
        },
        {
            "cell_type": "markdown",
            "source": [
                "and then you can check for those as well"
            ],
            "metadata": {
                "azdata_cell_guid": "0092da39-1d8b-4473-b984-f0d4402d34cf"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$FolderPath = $Env:USERPROFILE + '\\Documents\\dbachecks'\r\n",
                "$SqlCredential = Import-Clixml -Path $FolderPath\\sqladmin.cred\r\n",
                "Invoke-DbcCheck -SqlCredential $SqlCredential -Check AgentAlert"
            ],
            "metadata": {
                "azdata_cell_guid": "a1d04314-f8db-42fe-a429-8532a68a6e87"
            },
            "outputs": [],
            "execution_count": 40
        }
    ]
}