ex events schema pain

Query_Schema_By_Users_From_Extended_Events.ipynb

Query_Schema_By_Users_From_Extended_Events_Load_Data.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": [
                "# Load the DBA Database for the Query Schema By Users From Extended Events\r\n",
                "\r\n",
                "To load the latest data from the XE File into the database - run this code.\r\n",
                "\r\n",
                "It will interogate the `[xe].[SchemaQueryByUser_Processed]` table for the files that have already been processed and then stop the Extended Event session so that we dont miss any data when processing (because Murphys law states that we will do this just as the file has rolled over and then process that file with 1 row and it will be marked as processed and we will miss the other 1Gb of data)\r\n",
                "\r\n",
                "Then it will copy any files that need processing to your PC to the directory in the local path variable and then process them with dbatools and write the data to the  `[xe].[SchemaQueryByUser]` table and then update the `[xe].[SchemaQueryByUser_Processed]` with the name and the date processed\r\n",
                "\r\n",
                "Then it will start the session again\r\n",
                "\r\n",
                "Once this has been completed, you can run the notebook to query the data\r\n",
                "[Query Schema By Users From Extended Events](Query_Schema_By_Users_From_Extended_Events.ipynb)"
            ],
            "metadata": {
                "azdata_cell_guid": "8f4b976d-c4ee-4faa-ad32-74058511e0e2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$SQlinstance = ''\n",
                "$Database = ''\n",
                "$localpath = 'C:\\XEFilesForProcessing\\'\n",
                "$processed = 'C:\\XEFilesForProcessing\\Processed'\n",
                "\n",
                "try {\n",
                "    if(-not(Test-Path $localpath)){\n",
                "        New-Item $localpath -ItemType Directory\n",
                "    }\n",
                "    if(-not(Test-Path $processed)){\n",
                "        New-Item $processed -ItemType Directory\n",
                "    }\n",
                "}\n",
                "catch {\n",
                "    Write-Warning \"Failed to create directories $localpath or $processed - Cant really carry on\"\n",
                "    $_\n",
                "    Return\n",
                "}\n",
                "\n",
                "try {\n",
                "    Write-Output \"Getting the processed files from the database\"\n",
                "    $filesQuery = \"SELECT [filename] FROM [xe].[SchemaQueryByUser_Processed]\"\n",
                "    $ProcessedFiles = (Invoke-DbaQuery -SqlInstance $SQlinstance -Database $Database -Query $filesQuery).filename\n",
                "    Write-Output \"Got the processed files from the database\"\n",
                "}\n",
                "catch {\n",
                "    Write-Warning \"Step Failed\"\n",
                "    $_\n",
                "    Return\n",
                "}\n",
                "\n",
                "\n",
                "try {\n",
                "    Write-Output \"Getting the file names to process from the server\"\n",
                "    $files = Get-ChildItem  '\\\\SERVERNAME\\e$\\XEFiles' -File | Where Name -notin $ProcessedFiles\n",
                "    Write-Output \"Got the file names to process from the server\"\n",
                "}\n",
                "catch {\n",
                "    Write-Warning \"Step Failed\"\n",
                "    $_\n",
                "    Return\n",
                "}\n",
                "\n",
                "try {\n",
                "    Write-Output \"Stopping Session so that we dont lose data when processing\"\n",
                "    Stop-DbaXESession -SqlInstance $SQlinstance -Session SchemaQueryByUser\n",
                "    Write-Output \"Stopped Session so that we dont lose data when processing\"\n",
                "}\n",
                "catch {\n",
                "    Write-Warning \"Step Failed\"\n",
                "    $_\n",
                "    Return\n",
                "}\n",
                "\n",
                "try {\n",
                "    if ($files) {\n",
                "        Write-Output \"Copying files to $localpath\"\n",
                "        $files | Copy-Item -Destination $localpath \n",
                "        Write-Output \"Copied files to $localpath\"\n",
                "    }\n",
                "    else {\n",
                "        Write-Output \"All files have already been processed\"\n",
                "        Return\n",
                "    }   \n",
                "}\n",
                "catch {\n",
                "    Write-Warning \"Step Failed\"\n",
                "    $_\n",
                "    Return\n",
                "}\n",
                "\n",
                "\n",
                "try {\n",
                "    $FilesToBeProcessed = (Get-ChildItem $localpath -File)\n",
                "}\n",
                "catch {\n",
                "    Write-Warning \"Step Failed\"\n",
                "    $_\n",
                "    Return\n",
                "}\n",
                "\n",
                "\n",
                "if ($FilesToBeProcessed) {\n",
                "    foreach ($XEPath in $FilesToBeProcessed) {\n",
                "        try {\n",
                "            Write-Output \"Processing $($XEPath.FullName)\"\n",
                "            Read-DbaXEFile -Path $XEPath | Write-DbaDataTable -SqlInstance ''-Database  -Schema xe -Table SchemaQueryByUser \n",
                "            Write-Output \"Written $($XEPath.FullName) to database\"\n",
                "        }\n",
                "        catch {\n",
                "            Write-Warning \"Step Failed\"\n",
                "            $_\n",
                "            Return\n",
                "        }\n",
                "\n",
                "        try {\n",
                "            $Date = Get-Date\n",
                "            Write-Output \"Auditing $($XEPath.FullName)\"\n",
                "            $Query = \"INSERT INTO [xe].[SchemaQueryByUser_Processed]\n",
                "        ([filename]\n",
                "        ,[processed])\n",
                "    VALUES\n",
                "        ('$($XEPath.Name)'\n",
                "        ,'$Date'\n",
                "        )\n",
                "    GO\"\n",
                "            Invoke-DbaQuery -SqlInstance $SQlinstance -Database $Database -Query $Query\n",
                "            Write-Output \"Added $($XEPath.FullName) to audit table\"\n",
                "        }\n",
                "        catch {\n",
                "            Write-Warning \"Step Failed\"\n",
                "            $_\n",
                "            Return\n",
                "        }\n",
                "\n",
                "        try {\n",
                "            Write-Output \"Moving $($XEPath.FullName)\"\n",
                "            Move-Item $XEPath.FullName -Destination $processed\n",
                "            Write-Output \"Moved $($XEPath.FullName)\"\n",
                "        }\n",
                "        catch {\n",
                "            Write-Warning \"Step Failed\"\n",
                "            $_\n",
                "            Return\n",
                "        }\n",
                "\n",
                "    }\n",
                "}\n",
                "else {\n",
                "    Write-Output \"All files have already been processed\"\n",
                "}\n",
                "\n",
                "try {\n",
                "    Write-Output \"Starting Session so to gather more data\"\n",
                "    Start-DbaXESession -SqlInstance $SQlinstance -Session SchemaQueryByUser\n",
                "    Write-Output \"Started Session so to gather more data\"\n",
                "}\n",
                "catch {\n",
                "    Write-Warning \"Starting Session FAILED - You NEED to go and start it manually to continue gathering data\"\n",
                "    $_\n",
                "    Return\n",
                "}\n",
                "\n",
                "\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "037d4a79-476a-4427-8fb3-5e7773c2166b",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "Getting the processed files from the database\nGot the processed files from the database\nGetting the file names to process from the server\nGot the file names to process from the server\nStopping Session so that we dont lose data when processing\n\n\nComputerName : \nInstanceName : MSSQLSERVER\nSqlInstance  : \nName         : SchemaQueryByUser\nStatus       : Stopped\nStartTime    : \nAutoStart    : True\nState        : Existing\nTargets      : {package0.event_file}\nTargetFile   : {E:\\XEFiles\\SchemaQueryByUser_}\nEvents       : {sqlserver.sql_batch_completed}\nMaxMemory    : 4096\nMaxEventSize : 0\n\nStopped Session so that we dont lose data when processing\nCopying files to C:\\XEFilesForProcessing\\\nCopied files to C:\\XEFilesForProcessing\\\nProcessing C:\\XEFilesForProcessing\\SchemaQueryByUser__0_132406534774010000.xel\nWritten C:\\XEFilesForProcessing\\SchemaQueryByUser__0_132406534774010000.xel to database\nAuditing C:\\XEFilesForProcessing\\SchemaQueryByUser__0_132406534774010000.xel\nAdded C:\\XEFilesForProcessing\\SchemaQueryByUser__0_132406534774010000.xel to audit table\nMoving C:\\XEFilesForProcessing\\SchemaQueryByUser__0_132406534774010000.xel\nMoved C:\\XEFilesForProcessing\\SchemaQueryByUser__0_132406534774010000.xel\nStarting Session so to gather more data\nComputerName : schema6\nInstanceName : MSSQLSERVER\nSqlInstance  : schema6\nName         : SchemaQueryByUser\nStatus       : Running\nStartTime    : 31/07/2020 15:03:26\nAutoStart    : True\nState        : Existing\nTargets      : {package0.event_file}\nTargetFile   : {E:\\XEFiles\\SchemaQueryByUser_}\nEvents       : {sqlserver.sql_batch_completed}\nMaxMemory    : 4096\nMaxEventSize : 0\n\nStarted Session so to gather more data\n\n\n"
                }
            ],
            "execution_count": 2
        }
    ]
}