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

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "eade8b6a-a343-45c6-b917-f018cb2d0e7e"
   },
   "source": [
    "![First Power Bi](.\\images\\dbachecks-logo.png )\r\n",
    "# dbachecks - Configuration - altereds\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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "caaebcc0-a835-4fcf-87b9-4edbe8fed032"
   },
   "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",
  Produ Sna the SqlInstance and the ComputerName for the checks in this notebook\r\n",
    "\r\n",
    "\r\n",
    "\r\n",
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "azdata_cell_guid": "8e8c36f2-b4b0-4d36-8dec-1f474a3e27a5"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script type=\"text/javascript\">#!javascript\r\n",
       "if ((typeof(requirejs) !==  typeof(Function)) || (typeof(requirejs.config) !== typeof(Function))) { \r\n",
       "    let script = document.createElement(\"script\"); \r\n",
       "    script.setAttribute(\"src\", \"https://cdnjs.cloudflare.com/ajax/libs/require.js/2.3.6/require.min.js\"); \r\n",
       "    script.onload = function(){\r\n",
       "        loadDotnetInteractiveApi();\r\n",
       "    };\r\n",
       "    document.getElementsByTagName(\"head\")[0].appendChild(script); \r\n",
       "}\r\n",
       "else {\r\n",
       "    loadDotnetInteractiveApi();\r\n",
       "}\r\n",
       "\r\n",
       "function loadDotnetInteractiveApi(){\r\n",
       "    let apiRequire = requirejs.config({context:\"dotnet-interactive.32244.63653\",paths:{dotnetInteractive:\"http://localhost:63653/resources/dotnet-interactive\"}});\r\n",
       "    apiRequire(['dotnetInteractive'], \r\n",
       "    function(api) {       \r\n",
       "        api.createDotnetInteractiveClient(\"http://localhost:63653/\", window);\r\n",
       "    },\r\n",
       "    function(error){\r\n",
       "        console.log(error);\r\n",
       "    });\r\n",
       "}</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Name             Value                              Description\n",
      "----             -----                              -----------\n",
      "app.sqlinstance  {localhost,15592, localhost,15593} List of SQL Server instances that SQL-based te…\n",
      "app.computername {localhost,15592, localhost,15593} List of Windows Servers that Windows-based tes…\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cd C:\\Users\\mrrob\\OneDrive\\Documents\\GitHub\\dbachecks\n",
    "ipmo .\\dbachecks.psd1\n",
    "# Just in case the config has been altered\r\n",
    "$null = Reset-DbcConfig\r\n",
    "$SqlInstances =SQL92','localhost,15593'\r\n",
    "Set-DbcConfig -Name app.sqlinstance -Value $SqlInstances\r\n",
    "Set-DbcConfig -Name app.computername -Value $SqlInstann.cred"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "d3fa1aee-8bca-4e10-9679-1d546795d3ad"
   },
   "source": [
    "and you will no longer need to specify them when calling `Invoke-DbcCheck`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "989a170f-842c-4f6a-8cc8-539a7a8ea930"
   },
   "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"
   "execution_count": 2,
   "metadata": {
    "azdata_cell_guid": "3627b53c-63a5-4e21-9a2d-96b1a092c913",
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[97m    ____            __\n",
      "   / __ \\___  _____/ /____  _____\n",
      "  / /_/ / _ \\/ ___/ __/ _ \\/ ___/\n",
      " / ____/  __(__  ) /_/  __/ /\n",
      "/_/    \\___/____/\\__/\\___/_/\n",
      "Pester v4.9.0\n",
      "Executing all tests in 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1' with Tags InvalidDatabaseOwner\u001b[0m\n",
      "\n",
      "\u001b[92mExecuting script C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Invalid Database Owner\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Testing Database Owners on localhost,15592\u001b[0m\n",
      "\u001b[32m      [+] Database AdventureWorks2017 - owner sqladmin should Not be in this list ( sa ) on localhost,15592\u001b[0m\u001b[90m 30ms\u001b[0m\n",
      "\u001b[32m      [+] Database Northwind - owner sqladmin should Not be in this list ( sa ) on localhost,15592\u001b[0m\u001b[90m 2ms\u001b[0m\n",
      "\u001b[32m      [+] Database pubs - owner sqladmin should Not be in this list ( sa ) on localhost,15592\u001b[0m\u001b[90m 3ms\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Invalid Database Owner\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Testing Database Owners on localhost,15593\u001b[0m\n",
      "\u001b[32m      [+] Database AdventureWorks2017 - owner sqladmin should Not be in this list ( sa ) on localhost,15593\u001b[0m\u001b[90m 2ms\u001b[0m\n",
      "\u001b[32m      [+] Database Northwind - owner sqladmin should Not be in this list ( sa ) on localhost,15593\u001b[0m\u001b[90m 4ms\u001b[0m\n",
      "\u001b[32m      [+] Database pubs - owner sqladmin should Not be in this list ( sa ) on localhost,15593\u001b[0m\u001b[90m 3ms\u001b[0m\n",
      "\u001b[97mTests completed in 1.46s\u001b[0m\n",
      "\u001b[32mTests Passed: 6, \u001b[0m\u001b[90mFailed: 0, \u001b[0m\u001b[90mSkipped: 0, \u001b[0m\u001b[90mPending: 0, \u001b[0m\u001b[90mInconclusive: 0 \u001b[0m\n"
     ]
    }
   ],
   "source": [
    "Invoke-DbcCheck -SqlCredential $SqlCredential  InvalidDatabaseOwner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "0b36fbd4-d266-493e-9aef-f6c66f76f97c"
   },
   "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`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "azdata_cell_guid": "c03ce802-b2e9-4da0-b302-0f916e03ece7"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Group       : Database\n",
      "Type        : Sqlinstance\n",
      "UniqueTag   : InvalidDatabaseOwner\n",
      "AllTags     : InvalidDatabaseOwner, Medium, Database\n",
      "Config      : app.sqlinstance policy.invaliddbowner.name policy.invaliddbowner.excludedb \n",
      "Description : Tests that the Database Owner is NOT in the specified (default blank) list\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "Get-DbcCheck -Pattern InvalidDatabaseOwner | Format-List"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "6f7e0921-11ae-4cca-a8c5-4cd4e9f2abe3"
   },
   "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`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "azdata_cell_guid": "4318390f-f330-496e-aecb-a7d546bd62e4"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Name        : policy.invaliddbowner.name\n",
      "Value       : sa\n",
      "Description : The database owner account should not be this user\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "Get-DbcConfig -Name policy.invaliddbowner.name | Format-List"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "8b57c94f-95f7-4575-a2e4-db1e84df87e4"
   },
   "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 :-)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "azdata_cell_guid": "1d12f62f-107a-440c-b87d-b0b8c2a2ba08"
   },
   "outputs": [],
   "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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "cbf7ed68-9fe1-42a7-a890-a06acf016cfd"
   },
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "azdata_cell_guid": "c2aed9d7-6fcc-4c04-b91c-5007a8ddc27a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\u001b[97m    ____            __\n",
      "   / __ \\___  _____/ /____  _____\n",
      "  / /_/ / _ \\/ ___/ __/ _ \\/ ___/\n",
      " / ____/  __(__  ) /_/  __/ /\n",
      "/_/    \\___/____/\\__/\\___/_/\n",
      "Pester v4.9.0\n",
      "Executing all tests in 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1' with Tags InvalidDatabaseOwner\u001b[0m\n",
      "\n",
      "\u001b[92mExecuting script C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Invalid Database Owner\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Testing Database Owners on localhost,15592\u001b[0m\n",
      "\u001b[91m      [-] Database AdventureWorks2017 - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15592\u001b[0m\u001b[90m 17ms\u001b[0m\n",
      "\u001b[91m        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\u001b[0m\n",
      "\u001b[91m        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\u001b[0m\n",
      "\u001b[91m        at <ScriptBlock>, C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1: line 176\u001b[0m\n",
      "\u001b[91m      [-] Database Northwind - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15592\u001b[0m\u001b[90m 5ms\u001b[0m\n",
      "\u001b[91m        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\u001b[0m\n",
      "\u001b[91m        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\u001b[0m\n",
      "\u001b[91m        at <ScriptBlock>, C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1: line 176\u001b[0m\n",
      "\u001b[91m      [-] Database pubs - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15592\u001b[0m\u001b[90m 4ms\u001b[0m\n",
      "\u001b[91m        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\u001b[0m\n",
      "\u001b[91m        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\u001b[0m\n",
      "\u001b[91m        at <ScriptBlock>, C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1: line 176\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Invalid Database Owner\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Testing Database Owners on localhost,15593\u001b[0m\n",
      "\u001b[91m      [-] Database AdventureWorks2017 - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15593\u001b[0m\u001b[90m 4ms\u001b[0m\n",
      "\u001b[91m        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\u001b[0m\n",
      "\u001b[91m        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\u001b[0m\n",
      "\u001b[91m        at <ScriptBlock>, C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1: line 176\u001b[0m\n",
      "\u001b[91m      [-] Database Northwind - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15593\u001b[0m\u001b[90m 6ms\u001b[0m\n",
      "\u001b[91m        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\u001b[0m\n",
      "\u001b[91m        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\u001b[0m\n",
      "\u001b[91m        at <ScriptBlock>, C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1: line 176\u001b[0m\n",
      "\u001b[91m      [-] Database pubs - owner sqladmin should Not be in this list ( sqladmin ) on localhost,15593\u001b[0m\u001b[90m 7ms\u001b[0m\n",
      "\u001b[91m        Expected collection sqladmin to not contain 'sqladmin', because The database owner was one specified as incorrect, but it was found.\u001b[0m\n",
      "\u001b[91m        176:                         $psitem.Owner | Should -Not -BeIn $TargetOwner -Because \"The database owner was one specified as incorrect\"\u001b[0m\n",
      "\u001b[91m        at <ScriptBlock>, C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1: line 176\u001b[0m\n",
      "\u001b[97mTests completed in 652ms\u001b[0m\n",
      "\u001b[97mTests Passed: 0, \u001b[0m\u001b[91mFailed: 6, \u001b[0m\u001b[90mSkipped: 0, \u001b[0m\u001b[90mPending: 0, \u001b[0m\u001b[90mInconclusive: 0 \u001b[0m\n",
      "Name                       Value    Description\n",
      "----                       -----    -----------\n",
      "policy.invaliddbowner.name sqladmin The database owner account should not be this user\n",
      "\n"
     ]
    }
   ],
   "source": [
    "Set-DbcConfig -Name policy.invaliddbowner.name -Value sqladmin\r\n",
    "Invoke-DbcCheck -SqlCredential $SqlCredential -Check InvalidDatabaseOwner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "d76f851d-c6ae-4978-856c-3e9ab76459be"
   },
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "azdata_cell_guid": "e567857c-6450-40d6-b02e-685a8fbd29e4"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[97m    ____            __\n",
      "   / __ \\___  _____/ /____  _____\n",
      "  / /_/ / _ \\/ ___/ __/ _ \\/ ___/\n",
      " / ____/  __(__  ) /_/  __/ /\n",
      "/_/    \\___/____/\\__/\\___/_/\n",
      "Pester v4.9.0\n",
      "Executing all tests in 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1' with Tags InvalidDatabaseOwner\u001b[0m\n",
      "\n",
      "\u001b[92mExecuting script C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Invalid Database Owner\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Testing Database Owners on localhost,15592\u001b[0m\n",
      "\u001b[32m      [+] Database AdventureWorks2017 - owner thebeard should Not be in this list ( sqladmin ) on localhost,15592\u001b[0m\u001b[90m 3ms\u001b[0m\n",
      "\u001b[32m      [+] Database Northwind - owner thebeard should Not be in this list ( sqladmin ) on localhost,15592\u001b[0m\u001b[90m 3ms\u001b[0m\n",
      "\u001b[32m      [+] Database pubs - owner thebeard should Not be in this list ( sqladmin ) on localhost,15592\u001b[0m\u001b[90m 3ms\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Invalid Database Owner\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Testing Database Owners on localhost,15593\u001b[0m\n",
      "\u001b[32m      [+] Database AdventureWorks2017 - owner thebeard should Not be in this list ( sqladmin ) on localhost,15593\u001b[0m\u001b[90m 1ms\u001b[0m\n",
      "\u001b[32m      [+] Database Northwind - owner thebeard should Not be in this list ( sqladmin ) on localhost,15593\u001b[0m\u001b[90m 2ms\u001b[0m\n",
      "\u001b[32m      [+] Database pubs - owner thebeard should Not be in this list ( sqladmin ) on localhost,15593\u001b[0m\u001b[90m 3ms\u001b[0m\n",
      "\u001b[97mTests completed in 600ms\u001b[0m\n",
      "\u001b[32mTests Passed: 6, \u001b[0m\u001b[90mFailed: 0, \u001b[0m\u001b[90mSkipped: 0, \u001b[0m\u001b[90mPending: 0, \u001b[0m\u001b[90mInconclusive: 0 \u001b[0m\n",
      "\n",
      "\u001b[97m    ____            __\n",
      "   / __ \\___  _____/ /____  _____\n",
      "  / /_/ / _ \\/ ___/ __/ _ \\/ ___/\n",
      " / ____/  __(__  ) /_/  __/ /\n",
      "/_/    \\___/____/\\__/\\___/_/\n",
      "Pester v4.9.0\n",
      "Executing all tests in 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1' with Tags ValidDatabaseOwner\u001b[0m\n",
      "\n",
      "\u001b[92mExecuting script C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Valid Database Owner\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Testing Database Owners on localhost,15592\u001b[0m\n",
      "\u001b[32m      [+] Database AdventureWorks2017 - owner thebeard should be in this list ( thebeard ) on localhost,15592\u001b[0m\u001b[90m 2ms\u001b[0m\n",
      "\u001b[32m      [+] Database Northwind - owner thebeard should be in this list ( thebeard ) on localhost,15592\u001b[0m\u001b[90m 2ms\u001b[0m\n",
      "\u001b[32m      [+] Database pubs - owner thebeard should be in this list ( thebeard ) on localhost,15592\u001b[0m\u001b[90m 2ms\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Valid Database Owner\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Testing Database Owners on localhost,15593\u001b[0m\n",
      "\u001b[32m      [+] Database AdventureWorks2017 - owner thebeard should be in this list ( thebeard ) on localhost,15593\u001b[0m\u001b[90m 1ms\u001b[0m\n",
      "\u001b[32m      [+] Database Northwind - owner thebeard should be in this list ( thebeard ) on localhost,15593\u001b[0m\u001b[90m 3ms\u001b[0m\n",
      "\u001b[32m      [+] Database pubs - owner thebeard should be in this list ( thebeard ) on localhost,15593\u001b[0m\u001b[90m 3ms\u001b[0m\n",
      "\u001b[97mTests completed in 556ms\u001b[0m\n",
      "\u001b[32mTests Passed: 6, \u001b[0m\u001b[90mFailed: 0, \u001b[0m\u001b[90mSkipped: 0, \u001b[0m\u001b[90mPending: 0, \u001b[0m\u001b[90mInconclusive: 0 \u001b[0m\n",
      "Name                     Value    Description\n",
      "----                     -----    -----------\n",
      "policy.validdbowner.name thebeard The database owner account should be this user\n",
\     aDatabase -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",e -Value thebeard\r\n",
    "# Check our databases  are owned by the correct account\r\n",
    "Invoke-DbcCheck -SqlCredential $SqlCredential -Check ValidDatabaseOwner "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "ebca0026-3a00-496b-aeec-730f3d5bd799"
   },
   "source": [
    "## Ensuring Databases exist\r\n",
    "You can run the default `DatabaseExists` which will check that the system databases exist as you did in the qu
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "azdata_cell_guid": "6657827a-36ca-435d-9624-4e18c20d8593"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[97m    ____            __\n",
      "   / __ \\___  _____/ /____  _____\n",
      "  / /_/ / _ \\/ ___/ __/ _ \\/ ___/\n",
      " / ____/  __(__  ) /_/  __/ /\n",
      "/_/    \\___/____/\\__/\\___/_/\n",
      "Pester v4.9.0\n",
      "Executing all tests in 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1' with Tags DatabaseExists\u001b[0m\n",
      "\n",
      "\u001b[92mExecuting script C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Database Exists\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Database exists on localhost,15592\u001b[0m\n",
      "\u001b[32m      [+] Database master should exist on \u001b[0m\u001b[90m 91ms\u001b[0m\n",
      "\u001b[32m      [+] Database msdb should exist on \u001b[0m\u001b[90m 87ms\u001b[0m\n",
      "\u001b[32m      [+] Database tempdb should exist on \u001b[0m\u001b[90m 90ms\u001b[0m\n",
      "\u001b[32m      [+] Database model should exist on \u001b[0m\u001b[90m 80ms\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Database Exists\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Database exists on localhost,15593\u001b[0m\n",
      "\u001b[32m      [+] Database master should exist on \u001b[0m\u001b[90m 74ms\u001b[0m\n",
      "\u001b[32m      [+] Database msdb should exist on \u001b[0m\u001b[90m 90ms\u001b[0m\n",
      "\u001b[32m      [+] Database tempdb should exist on \u001b[0m\u001b[90m 78ms\u001b[0m\n",
      "\u001b[32m      [+] Database model should exist on \u001b[0m\u001b[90m 77ms\u001b[0m\n",
      "\u001b[97mTests completed in 1.11s\u001b[0m\n",
      "\u001b[32mTests Passed: 8, \u001b[0m\u001b[90mFailed: 0, \u001b[0m\u001b[90mSkipped: 0, \u001b[0m\u001b[90mPending: 0, \u001b[0m\u001b[90mInconclusive: 0 \u001b[0m\n"
     ]
    }
   ],
   "source": [
    "Invoke-DbcCheck -SqlCredential $SqlCredential -Check DatabaseExists"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "7ee90397-1031-4fe3-b679-6bb017b3f6c1"
   },
   "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`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "azdata_cell_guid": "80668f2f-9a92-4930-89bb-43861de533ba"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Group       : Database\n",
      "Type        : Sqlinstance\n",
      "UniqueTag   : DatabaseExists\n",
      "AllTags     : DatabaseExists, Database\n",
      "Config      : app.sqlinstance database.exists \n",
      "Description : Tests that the databases are specified are on the instance (Note - Does not check if \n",
      "              they are available - Use DatabaseStatus for that))\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "Get-DbcCheck DatabaseExists | Format-List"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "b03439f2-0c50-4487-bec3-cc2001a6ab4a"
   },
   "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "azdata_cell_guid": "0c09af8d-8447-403b-8096-282ec554fa0e"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Name            Value                          Description\n",
      "----            -----                          -----------\n",
      "database.exists {master, msdb, tempdb, model…} The databases we expect to be on the instances\n",
      "\n"
     ]
    }
   ],
   "source": [
    "Set-DbcConfig -Name database.exists -Value 'pubs','NorthWind','AdventureWorks2017','WideWorldImporters' -Append"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "azdata_cell_guid": "732fb678-3816-46ec-a54c-2e4a84244183"
   },
   "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "azdata_cell_guid": "1d421c8c-bc48-43a9-a15a-d5c95c5c7b68"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
      " / ____/  __(__  ) /_/  __/ /\n",
      "/_/    \\___/____/\\__/\\___/_/\n",
      "Pester v4.9.0\n",
      "Executing all tests in 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1' with Tags DatabaseExists\u001b[0m\n",
      "\n",
      "\u001b[92mExecuting script C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Database Exists\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Database exists on localhost,15592\u001b[0m\n",
      "\u001b[32m      [+] Database master should exist on \u001b[0m\u001b[90m 80ms\u001b[0m\n",
      "\u001b[32m      [+] Database msdb should exist on \u001b[0m\u001b[90m 93ms\u001b[0m\n",
      "\u001b[32m      [+] Database tempdb should exist on \u001b[0m\u001b[90m 92ms\u001b[0m\n",
      "\u001b[32m      [+] Database model should exist on \u001b[0m\u001b[90m 78ms\u001b[0m\n",
      "\u001b[32m      [+] Database pubs should exist on \u001b[0m\u001b[90m 81ms\u001b[0m\n",
      "\u001b[32m      [+] Database NorthWind should exist on \u001b[0m\u001b[90m 76ms\u001b[0m\n",
      "\u001b[32m      [+] Database AdventureWorks2017 should exist on \u001b[0m\u001b[90m 81ms\u001b[0m\n",
      "\u001b[91m      [-] Database WideWorldImporters should exist on \u001b[0m\u001b[90m 91ms\u001b[0m\n",
      "\u001b[91m        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.\u001b[0m\n",
      "\u001b[91m        72:     $Actual | Should -Contain $expecteddb -Because \"We expect $expecteddb to be on $Instance\"\u001b[0m\n",
      "\u001b[91m        at Assert-DatabaseExists, C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\internal\\assertions\\Database.Assertions.ps1: line 72\u001b[0m\n",
      "\u001b[91m        at <ScriptBlock>, C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1: line 934\u001b[0m\n",
      "\n",
      "\u001b[92m  Describing Database Exists\u001b[0m\n",
      "\n",
      "\u001b[92m    Context Database exists on localhost,15593\u001b[0m\n",
<<<<<<< HEAD
      "\u001b[32m      [+] Database master should exist on \u001b[0m\u001b[90m 185ms\u001b[0m\n",
      "\u001b[32m      [+] Database msdb should exist on \u001b[0m\u001b[90m 199ms\u001b[0m\n",
      "\u001b[32m      [+] Database tempdb should exist on \u001b[0m\u001b[90m 256ms\u001b[0m\n",
      "\u001b[32m      [+] Database model should exist on \u001b[0m\u001b[90m 199ms\u001b[0m\n",
      "\u001b[32m      [+] Database pubs should exist on \u001b[0m\u001b[90m 265ms\u001b[0m\n",
      "\u001b[32m      [+] Database NorthWind should exist on \u001b[0m\u001b[90m 190ms\u001b[0m\n",
      "\u001b[32m      [+] Database AdventureWorks2017 should exist on \u001b[0m\u001b[90m 242ms\u001b[0m\n",
      "\u001b[91m      [-] Database WideWorldImporters should exist on \u001b[0m\u001b[90m 202ms\u001b[0m\n",
      "\u001b[91m        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.\u001b[0m\n",
      "\u001b[91m        72:     $Actual | Should -Contain $expecteddb -Because \"We expect $expecteddb to be on $Instance\"\u001b[0m\n",
      "\u001b[91m        at Assert-DatabaseExists, C:\\Users\\mrrob\\OneDrive\\Documents\\GitHub\\dbachecks\\internal\\assertions\\Database.Assertions.ps1: line 72\u001b[0m\n",
      "\u001b[91m        at <ScriptBlock>, C:\\Users\\mrrob\\OneDrive\\Documents\\GitHub\\dbachecks\\checks\\Database.Tests.ps1: line 963\u001b[0m\n",
\====== should exist on \u001b[0m\u001b[90m 81ms\u001b[0m\n",d exist on \u001b[0m\u001b[90m 95ms\u001b[0m\n",
      "\u001b[32m      [+] Database model should exist on \u001b[0m\u001b[90m 79ms\u001b[0m\n",
      "\u001b[32m      [+] Database pubs should exist on \u001b[0m\u001b[90m 83ms\u001b[0m\n",
      "\u001b[32m      [+] Database NorthWind should exist on \u001b[0m\u001b[90m 86ms\u001b[0m\n",
      "\u001b[32m      [+] Database AdventureWorks2017 should exist on \u001b[0m\u001b[90m 80ms\u001b[0m\n",
      "\u001b[91m      [-] Database WideWorldImporters should exist on \u001b[0m\u001b[90m 82ms\u001b[0m\n",
      "\u001b[91m        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.\u001b[0m\n",
      "\u001b[91m        72:     $Actual | Should -Contain $expecteddb -Because \"We expect $expecteddb to be on $Instance\"\u001b[0m\n",
      "\u001b[91m        at Assert-DatabaseExists, C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\internal\\assertions\\Database.Assertions.ps1: line 72\u001b[0m\n",
      "\u001b[91m        at <ScriptBlock>, C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1: line 934\u001b[0m\n",
      "\u001b[97mTests completed in 1.81s\u001b[0m\n",
>>>>>>> 2f85b70fc1ef940e340563c2c3c925ccaf91b56c
      "\u001b[97mTests Passed: 14, \u001b[0m\u001b[91mFailed: 2, \u001b[0m\u001b[90mSkipped: 0, \u001b[0m\u001b[90mPending: 0, \u001b[0m\u001b[90mInconclusive: 0 \u001b[0m\n"
     ]
    }
   ],
   "source": [
    "Invoke-DbcCheck -SqlCredential $SqlCredential -Check DatabaseExists"
   ]
  },
  "cell_type": "markd: 
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "azdata_cell_guid": "18432613-5b44-41e3-8da9-667d704cebf2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[97m    ____            __\n",
      "   / __ \\___  _____/ /____  _____\n",
      "  / /_/ / _ \\/ ___/ __/ _ \\/ ___/\n",
      " / ____/  __(__  ) /_/  __/ /\n",
      "/_/    \\___/____/\\__/\\___/_/\n",
      "Pester v4.9.0\n",
E     "\n",[0m\n",
      "\n",
      "\u001b[92m    Context Testing Agent Alerts Severity exists on localhost,15592\u001b[0m\n",
      "\u001b[32m      [+] localhost,15592 should have Severity 16 Alert\u001b[0m\u001b[90m 27ms\u001b[0m\n",
      "\u001b[32m      [+] localhost,15592 should have Severity 16 Alert enabled\u001b[0m\u001b[90m 3ms\u001b[0m\n",
      "\u001b[32m      [+] localhost,15592 should have notification for Severity 16 Alert\u001b[0m\u001b[90m 9ms\u001b[0m\n",
      "\u001b[32m      [+] localhost,15592 should have Severity 17 Alert\u001b[0m\u001b[90m 3ms\u001b[0m\n",
      "\u001b[32m      [+] localhost,15592 should have Severity 17 Alert enabled\u001b[0m\u001b[90m 2ms\u001b[0m\n",
      "\u001b[32m      [+] localhost,15592 should have notification for Severity 17 Alert\u001b[0m\u001b[90m 2ms\u001b[0m\n",
      "\u001b[32m      [+] localhost,15592 should have Severity 18 Alert\u001b[0m\u001b[90m 2ms\u001b[0m\n",
   "mm