notebooks/dotNETNotebooks/dbachecks/05 - Creating the PowerBi.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": ".net-powershell",
            "display_name": ".NET (PowerShell)"
        },
        "language_info": {
            "name": "PowerShell",
            "version": "7.0",
            "mimetype": "text/x-powershell",
            "file_extension": ".ps1",
            "pygments_lexer": "powershell"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "![First Power Bi](.\\images\\dbachecks-logo.png )\n",
                "# Creating the Power Bi"
            ],
            "metadata": {
                "azdata_cell_guid": "9bc7915e-3aee-47b6-a760-7902a9fb635f"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Displaying the results in Power Bi is another capability of dbachecks. You can use this to build dashboards for your team or enable quick identification of issues that need to be resolved. There is a Power Bi dashboard embedded with the dbachecks module that you downloaded. This will automatically use the json file that is created when you use the command `Update-DbcPowerBiDataSource`\r\n",
                "\r\n",
                "You **MUST** use the `-PassThru` parameter of `Invoke-DbcCheck` when running the checks otherwise `Update-PowerBiDataSource` will not have an object to work with. There is no way to check that you are going to use `Update-DbcPowerBiDataSource` so the first time you will realise that youhave not used `-Passthru` is after the checks have run which can be very frustrating when it has taken an hour or two to create the configuration and run the checks as the beard has found out!\r\n",
                "\r\n",
                "With your configuration set up you run as you wish, you run `Invoke-DbcCheck` with the `-PassThru` parameter. "
            ],
            "metadata": {
                "azdata_cell_guid": "92be6d4a-5a8a-4234-acd4-ea476ccc2254"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$FolderPath = $Env:USERPROFILE + '\\Documents\\dbachecks'\r\n",
                "$SqlInstances = 'localhost,15592','localhost,15593'\r\n",
                "$SqlCredential = Import-Clixml -Path $FolderPath\\sqladmin.cred\r\n",
                "$null = Reset-DbcConfig\r\n",
                "$invokeDbcCheckSplat = @{\r\n",
                "    Check = 'DatabaseExists', 'DatabaseStatus', 'DefaultFilePath', 'XpCmdShellDisabled', 'VirtualLogFile', 'TempDbConfiguration', 'MaxMemory', 'ModelDbGrowth', 'LogfileCount', 'LastGoodCheckDb'\r\n",
                "    SqlInstance = $SqlInstances\r\n",
                "    SqlCredential = $SqlCredential\r\n",
                "    Show = 'Summary'\r\n",
                "}\r\n",
                "\r\n",
                "Invoke-DbcCheck @invokeDbcCheckSplat -PassThru | Update-DbcPowerBiDataSource"
            ],
            "metadata": {
                "azdata_cell_guid": "b540a7b1-1c0c-4ecb-8f51-9d3e728cd82e"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "<script type=\"text/javascript\">#!javascript\r\nif ((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\nelse {\r\n    loadDotnetInteractiveApi();\r\n}\r\n\r\nfunction loadDotnetInteractiveApi(){\r\n    let apiRequire = requirejs.config({context:\"dotnet-interactive.33184.64243\",paths:{dotnetInteractive:\"http://localhost:64243/resources/dotnet-interactive\"}});\r\n    apiRequire(['dotnetInteractive'], \r\n    function(api) {       \r\n        api.createDotnetInteractiveClient(\"http://localhost:64243/\", window);\r\n    },\r\n    function(error){\r\n        console.log(error);\r\n    });\r\n}</script>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\u001b[97m    ____            __\n   / __ \\___  _____/ /____  _____\n  / /_/ / _ \\/ ___/ __/ _ \\/ ___/\n / ____/  __(__  ) /_/  __/ /\n/_/    \\___/____/\\__/\\___/_/\nPester v4.9.0\nExecuting all tests in 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1', 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Instance.Tests.ps1' with Tags DatabaseExists', 'DatabaseStatus', 'DefaultFilePath', 'XpCmdShellDisabled', 'VirtualLogFile', 'TempDbConfiguration', 'MaxMemory', 'ModelDbGrowth', 'LogfileCount', 'LastGoodCheckDb\u001b[0m\n\u001b[97mTests completed in 15.59s\u001b[0m\n\u001b[97mTests Passed: 84, \u001b[0m\u001b[91mFailed: 8, \u001b[0m\u001b[93mSkipped: 2, \u001b[0m\u001b[90mPending: 0, \u001b[0m\u001b[90mInconclusive: 0 \u001b[0m\n\u001b[96m[\u001b[0m\u001b[37m14:11:06\u001b[0m\u001b[96m][\u001b[0m\u001b[37mUpdate-DbcPowerBiDataSource\u001b[0m\u001b[96m] Wrote results to C:\\Windows\\temp\\dbachecks\\dbachecks_1_Default__DatabaseExists_DatabaseStatus_DefaultFilePath_XpCmdShellDisabled.json\u001b[0m\n"
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "You will also notice that for the Show Parameter, we have used `Summary` this will only display the summary and not any of the check results.\r\n",
                "\r\n",
                "The output will also show that the file that has been created is located in the windows temp directory. This is where the includd Power Bi expects to find the files. You can open the Power Bi file using `Start-DbcPowerBi`"
            ],
            "metadata": {
                "azdata_cell_guid": "099867c5-7f64-48ea-9338-b1ddb21bfbe6"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Start-DbcPowerBi"
            ],
            "metadata": {
                "azdata_cell_guid": "4953cb1b-68a8-4376-9f7b-6ccf3ac12795"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\u001b[96m[\u001b[0m\u001b[37m14:11:41\u001b[0m\u001b[96m][\u001b[0m\u001b[37mStart-DbcPowerBi\u001b[0m\u001b[96m] Launching the dbachecks dashboard. This may take a moment.\u001b[0m\n"
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "This will look ***exactly*** like this.\n",
                "\n",
                "![First Power Bi](.\\images\\firstpowerbi.png )\n",
                "\n",
                "This is the default view and to view your results you will need to refresh the data.\n",
                "\n",
                "![Refresh Power Bi](.\\images\\RefreshPowerBi.gif )\n",
                "\n"
            ],
            "metadata": {
                "azdata_cell_guid": "74c627b6-c214-4d75-bea9-c50a16bff926"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Combining Results\r\n",
                "\r\n",
                "Now the power of dbachecks is really released. You can create configurations for different types of checks, load the configuration, run the checks and then save ready for the Power Bi. Using the `-Environment` parameter of `Update-DbcPowerBiDataSource` you can enable filtering by the different sets of checks.\r\n",
                "\r\n",
                "The code below will use the configuration files created in Notebook 04 so you will need to have run through that notebook prior to running this code."
            ],
            "metadata": {
                "azdata_cell_guid": "563c48bf-a8da-4509-9d6d-66b55b8b1d76"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$FolderPath = $Env:USERPROFILE + '\\Documents\\dbachecks'\r\n",
                "$SqlCredential = Import-Clixml -Path $FolderPath\\sqladmin.cred\r\n",
                "# Just in case the config has been altered\r\n",
                "$null = Reset-DbcConfig\r\n",
                "$null = Import-DbcConfig -Path $FolderPath\\Morning-Checks-Production.json \r\n",
                "$invokeDbcCheckSplat = @{\r\n",
                "    SqlCredential = $SqlCredential\r\n",
                "    Check = 'InstanceConnection', 'FailedJob', 'LastBackup', 'ErrorLog', 'DiskCapacity', 'MemoryDump', 'LastJobRunTime', 'LongRunningJob', 'LogfileSize', 'FutureFileGrowth'\r\n",
                "    Show = 'Summary'\r\n",
                "}\r\n",
                "Invoke-DbcCheck @invokeDbcCheckSplat -PassThru | Update-DbcPowerBiDataSource -Environment Morning-Checks-Production\r\n",
                "\r\n",
                "\r\n",
                "$null = Reset-DbcConfig\r\n",
                "$null = Import-DbcConfig -Path $FolderPath\\New-Instance.json \r\n",
                "$invokeDbcCheckSplat = @{\r\n",
                "    SqlInstance = 'localhost,15592'\r\n",
                "    SqlCredential = $SqlCredential\r\n",
                "    Check = 'Instance','Agent'\r\n",
                "    Show = 'Summary'\r\n",
                "}\r\n",
                "Invoke-DbcCheck @invokeDbcCheckSplat -PassThru | Update-DbcPowerBiDataSource -Environment New-Instance\r\n",
                "\r\n",
                "$null = Reset-DbcConfig\r\n",
                "$null = Import-DbcConfig -Path $FolderPath\\Weekly-Check.json \r\n",
                "$invokeDbcCheckSplat = @{\r\n",
                "    SqlInstance = 'localhost,15592'\r\n",
                "    SqlCredential = $SqlCredential\r\n",
                "    Check = 'Agent', 'Database','Instance'\r\n",
                "    Show = 'Summary'\r\n",
                "}\r\n",
                "Invoke-DbcCheck @invokeDbcCheckSplat -PassThru | Update-DbcPowerBiDataSource -Environment Weekly-Check"
            ],
            "metadata": {
                "azdata_cell_guid": "db420b26-042f-44c6-9dcf-de70c29e4315",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\u001b[97m    ____            __\n   / __ \\___  _____/ /____  _____\n  / /_/ / _ \\/ ___/ __/ _ \\/ ___/\n / ____/  __(__  ) /_/  __/ /\n/_/    \\___/____/\\__/\\___/_/\nPester v4.9.0\nExecuting all tests in 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Agent.Tests.ps1', 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1', 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Instance.Tests.ps1', 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Server.Tests.ps1' with Tags InstanceConnection', 'FailedJob', 'LastBackup', 'ErrorLog', 'DiskCapacity', 'MemoryDump', 'LastJobRunTime', 'LongRunningJob', 'LogfileSize', 'FutureFileGrowth\u001b[0m\n\u001b[97mTests completed in 73.71s\u001b[0m\n\u001b[97mTests Passed: 69, \u001b[0m\u001b[91mFailed: 20, \u001b[0m\u001b[93mSkipped: 2, \u001b[0m\u001b[90mPending: 0, \u001b[0m\u001b[90mInconclusive: 0 \u001b[0m\n\u001b[96m[\u001b[0m\u001b[37m14:14:30\u001b[0m\u001b[96m][\u001b[0m\u001b[37mUpdate-DbcPowerBiDataSource\u001b[0m\u001b[96m] Wrote results to C:\\Windows\\temp\\dbachecks\\dbachecks_1_Morning-Checks-Production__InstanceConnection_FailedJob_LastBackup_ErrorLog.json\u001b[0m\n\u001b[97m    ____            __\n   / __ \\___  _____/ /____  _____\n  / /_/ / _ \\/ ___/ __/ _ \\/ ___/\n / ____/  __(__  ) /_/  __/ /\n/_/    \\___/____/\\__/\\___/_/\nPester v4.9.0\nExecuting all tests in 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Agent.Tests.ps1', 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Instance.Tests.ps1' with Tags Instance', 'Agent\u001b[0m\n\u001b[93mWARNING: You need to use Set-DbcConfig -Name policy.xevent.requiredstoppedsession -Value to add some Extended Events session names to run this check\u001b[0m\n\u001b[93mWARNING: You need to use Set-DbcConfig -Name policy.xevent.validrunningsession -Value to add some Extended Events session names to run this check\u001b[0m\n\u001b[93mWARNING: [14:14:56][Get-ComputerName] You must specify -ComputerName or use Set-DbcConfig app.computername to setup a list of servers\u001b[0m\n\u001b[97mTests completed in 17.23s\u001b[0m\n\u001b[97mTests Passed: 208, \u001b[0m\u001b[91mFailed: 14, \u001b[0m\u001b[93mSkipped: 12, \u001b[0m\u001b[90mPending: 0, \u001b[0m\u001b[90mInconclusive: 0 \u001b[0m\n\u001b[96m[\u001b[0m\u001b[37m14:14:56\u001b[0m\u001b[96m][\u001b[0m\u001b[37mUpdate-DbcPowerBiDataSource\u001b[0m\u001b[96m] Wrote results to C:\\Windows\\temp\\dbachecks\\dbachecks_1_New-Instance__Instance_Agent.json\u001b[0m\n\u001b[97m    ____            __\n   / __ \\___  _____/ /____  _____\n  / /_/ / _ \\/ ___/ __/ _ \\/ ___/\n / ____/  __(__  ) /_/  __/ /\n/_/    \\___/____/\\__/\\___/_/\nPester v4.9.0\nExecuting all tests in 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Agent.Tests.ps1', 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Database.Tests.ps1', 'C:\\Users\\mrrob\\Documents\\PowerShell\\Modules\\dbachecks\\1.2.25\\checks\\Instance.Tests.ps1' with Tags Agent', 'Database', 'Instance\u001b[0m\n"
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/plain": ""
                    },
                    "metadata": {}
                },
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\u001b[93mWARNING: You need to use Set-DbcConfig -Name policy.xevent.requiredstoppedsession -Value to add some Extended Events session names to run this check\u001b[0m\n\u001b[93mWARNING: You need to use Set-DbcConfig -Name policy.xevent.validrunningsession -Value to add some Extended Events session names to run this check\u001b[0m\n\u001b[97mTests completed in 53.85s\u001b[0m\n\u001b[97mTests Passed: 819, \u001b[0m\u001b[91mFailed: 51, \u001b[0m\u001b[93mSkipped: 24, \u001b[0m\u001b[90mPending: 0, \u001b[0m\u001b[90mInconclusive: 0 \u001b[0m\n\u001b[96m[\u001b[0m\u001b[37m14:15:58\u001b[0m\u001b[96m][\u001b[0m\u001b[37mUpdate-DbcPowerBiDataSource\u001b[0m\u001b[96m] Wrote results to C:\\Windows\\temp\\dbachecks\\dbachecks_1_Weekly-Check__Agent_Database_Instance.json\u001b[0m\n"
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "Now you can open the PowerBi with `Start-DbcPowerBi` and start exploring the results (and fixing the broken things!) \n",
                "\n",
                "[![PowerBi](./images/video.png)](https://www.youtube.com/watch?v=W7LRl2zKbzs)\n",
                "\n",
                "Clicking the image will open the video in YouTube\n",
                "\n",
                "If you have Power Bi open you can just refresh the data source to see the new data"
            ],
            "metadata": {
                "azdata_cell_guid": "25e75c3c-ebc7-49c4-bd14-0bca715acea6"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Start-DbcPowerBi"
            ],
            "metadata": {
                "azdata_cell_guid": "c4d8e1f7-37dc-47ad-966d-75cd8e30a0e9"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "[09:12:12][Start-DbcPowerBi] Launching the dbachecks dashboard. This may take a moment.\n"
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "code",
            "source": [
                "Start-DbcPowerBi -FromDatabase"
            ],
            "metadata": {
                "azdata_cell_guid": "1b8f42ff-3e10-4281-8bf9-74543ac0328c"
            },
            "outputs": [
                {
                    "name": "stdout",
                    "text": "\u001b[96m[\u001b[0m",
                    "output_type": "stream"
                },
                {
                    "name": "stdout",
                    "text": "\u001b[37m14:13:01\u001b[0m",
                    "output_type": "stream"
                },
                {
                    "name": "stdout",
                    "text": "\u001b[96m][\u001b[0m",
                    "output_type": "stream"
                },
                {
                    "name": "stdout",
                    "text": "\u001b[37mStart-DbcPowerBi\u001b[0m",
                    "output_type": "stream"
                },
                {
                    "name": "stdout",
                    "text": "\u001b[96m] Launching the dbachecks dashboard. This may take a moment.\u001b[0m\r\n",
                    "output_type": "stream"
                }
            ],
            "execution_count": 2
        }
    ]
}