ads-notebook-sql-server-audit.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "Get Instances"
            ],
            "metadata": {
                "azdata_cell_guid": "71e91469-099d-4ae7-a7dd-08c55425045a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "DECLARE @Instances TABLE (\r\n",
                "       Value VARCHAR(100),\r\n",
                "       InstanceName VARCHAR(100),\r\n",
                "       Data VARCHAR(100)\r\n",
                "       )\r\n",
                "\r\n",
                "INSERT @Instances\r\n",
                "EXECUTE xp_regread\r\n",
                "  @rootkey = 'HKEY_LOCAL_MACHINE',\r\n",
                "  @key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server',\r\n",
                "  @value_name = 'InstalledInstances' \r\n",
                "\r\n",
                "-- return your data\r\n",
                "SELECT InstanceName FROM @Instances"
            ],
            "metadata": {
                "azdata_cell_guid": "07a5de6c-5c94-4bfe-a2ad-23b3053b5913",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get Services"
            ],
            "metadata": {
                "azdata_cell_guid": "8fdb3f72-674f-424e-a3bc-98179f6bfe69"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "select\t@@SERVERNAME AS servername\r\n",
                "\t\t,DEFAULT_DOMAIN() as domain\r\n",
                "\t\t,servicename\r\n",
                "\t\t,startup_type_desc\r\n",
                "\t\t,status_desc\r\n",
                "\t\t,last_startup_time\r\n",
                "\t\t,service_account\r\n",
                "\t\t,CASE is_clustered\r\n",
                "\t\t\tWHEN 'Y' THEN 1\r\n",
                "\t\t\tWHEN 'N' THEN 0\r\n",
                "\t\tEND AS is_clustered\r\n",
                "\t\t,cluster_nodename\r\n",
                "\t\t,CASE instant_file_initialization_enabled\r\n",
                "\t\t\tWHEN 'Y' THEN 1\r\n",
                "\t\t\tWHEN 'N' THEN 0\r\n",
                "\t\tEND AS instant_file_initialization_enabled\r\n",
                "from\tsys.dm_server_services;"
            ],
            "metadata": {
                "azdata_cell_guid": "747ba695-b798-4119-9a19-0379ede7a90e",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get Server Detail and Config"
            ],
            "metadata": {
                "azdata_cell_guid": "c8d7225e-65b4-496c-b7df-517536d8df57"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "\r\n",
                "DECLARE @ProductVersion VARCHAR(50) = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128))\r\n",
                "\t\t,@Version VARCHAR(50) = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128)),2)\r\n",
                "\t\t,@sql_memory_model_desc VARCHAR(120)\r\n",
                "\t\t,@affinity_type_desc VARCHAR(60)\r\n",
                "\t\t,@BiosReleaseDate DATE\r\n",
                "\t\t,@ProcessorNameString VARCHAR(255);\r\n",
                "\t\t\r\n",
                "EXEC master.sys.xp_instance_regread\r\n",
                "\t@rootkey=N'HKEY_LOCAL_MACHINE'\r\n",
                "\t,@key=N'HARDWARE\\DESCRIPTION\\System\\BIOS'\r\n",
                "\t,@value_name=N'BiosReleaseDate'\r\n",
                "    ,@value=@BiosReleaseDate OUTPUT;\r\n",
                "\t\r\n",
                "EXEC master.sys.xp_instance_regread\r\n",
                "\t@rootkey=N'HKEY_LOCAL_MACHINE'\r\n",
                "\t,@key=N'HARDWARE\\DESCRIPTION\\System\\CentralProcessor\\0'\r\n",
                "\t,@value_name=N'ProcessorNameString'\r\n",
                "    ,@value=@ProcessorNameString OUTPUT;\r\n",
                "\r\n",
                "IF @Version >= 11\r\n",
                "\tSELECT\t@sql_memory_model_desc = sql_memory_model_desc\r\n",
                "\t\t\t,@affinity_type_desc = affinity_type_desc\r\n",
                "\tFROM\tsys.dm_os_sys_info;\r\n",
                "\r\n",
                "CREATE TABLE #os_sys_info_adds (\r\n",
                "\tsocket_count INT\r\n",
                "\t,cores_per_socket INT\r\n",
                "\t,numa_node_count INT\r\n",
                "\t,softnuma_configuration_desc VARCHAR(60)\r\n",
                ");\r\n",
                "\r\n",
                "IF @Version >= 13\r\n",
                "\tINSERT #os_sys_info_adds (socket_count, cores_per_socket, numa_node_count, softnuma_configuration_desc)\r\n",
                "\tSELECT\tsocket_count\r\n",
                "\t\t\t,cores_per_socket\r\n",
                "\t\t\t,numa_node_count\r\n",
                "\t\t\t,softnuma_configuration_desc\r\n",
                "\tFROM\tsys.dm_os_sys_info;\r\n",
                "\r\n",
                "CREATE TABLE #os_windows_info (\r\n",
                "\twindows_release VARCHAR(256)\r\n",
                "\t,windows_service_pack_level VARCHAR(256)\r\n",
                "\t,host_platform VARCHAR(256)\r\n",
                "\t,host_distribution VARCHAR(256)\r\n",
                ");\r\n",
                "\r\n",
                "IF @Version >= 14\r\n",
                "\tINSERT #os_windows_info (windows_release, windows_service_pack_level, host_platform, host_distribution)\r\n",
                "\tSELECT\thost_release\r\n",
                "\t\t\t,host_service_pack_level\r\n",
                "\t\t\t,host_platform\r\n",
                "\t\t\t,host_distribution\r\n",
                "\tFROM sys.dm_os_host_info\r\n",
                "ELSE\r\n",
                "\tINSERT #os_windows_info (windows_release, windows_service_pack_level)\r\n",
                "\tSELECT\twindows_release\r\n",
                "\t\t\t, REPLACE(windows_service_pack_level,'Service Pack ','')\r\n",
                "\tFROM\tsys.dm_os_windows_info\r\n",
                "\r\n",
                "SELECT\tSERVERPROPERTY('ServerName') AS HostName -- Both the Windows server and instance information associated with a specified instance of SQL Server.\r\n",
                "\t\t,SERVERPROPERTY('MachineName') AS MachineName -- For a clustered instance, Windows computer name on which the server instance is running.\r\n",
                "\t\t,DEFAULT_DOMAIN() AS Domain\r\n",
                "\t\t,ec.local_net_address AS IP\r\n",
                "\t\t,ec.local_tcp_port AS Port\r\n",
                "\t\t--,SERVERPROPERTY('Edition') AS Product -- Installed product edition of the instance of SQL Server.\r\n",
                "\t\t,CASE WHEN @ProductVersion LIKE '8.%' THEN '2000'\r\n",
                "\t\t\tWHEN @ProductVersion LIKE '9.%' THEN '2005'\r\n",
                "\t\t\tWHEN @ProductVersion LIKE '10.0%' THEN '2008'\r\n",
                "\t\t\tWHEN @ProductVersion LIKE '10.5%' THEN '2008 R2'\r\n",
                "\t\t\tWHEN @ProductVersion LIKE '11.%' THEN '2012'\r\n",
                "\t\t\tWHEN @ProductVersion LIKE '12.%' THEN '2014'\r\n",
                "\t\t\tWHEN @ProductVersion LIKE '13.%' THEN '2016'\r\n",
                "\t\t\tWHEN @ProductVersion LIKE '14.%' THEN '2017'\r\n",
                "\t\t\tWHEN @ProductVersion LIKE '15.%' THEN '2019'\r\n",
                "\t\tEND + ' ' + \r\n",
                "\t\tCASE CAST(SERVERPROPERTY('EngineEdition') AS NVARCHAR(128))\r\n",
                "\t\t\tWHEN '2' THEN 'Std'\r\n",
                "\t\t\tWHEN '3' THEN 'Ent'\r\n",
                "\t\t\tWHEN '4' THEN 'Exp'\r\n",
                "\t\tEND  +\r\n",
                "\t\tCASE WHEN CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128)) LIKE '%64%' THEN ' x64'\r\n",
                "\t\t\tELSE ' x32'\r\n",
                "\t\tEND\r\n",
                "\t\tAS Product\r\n",
                "\t\t,SERVERPROPERTY('ProductLevel') AS SP -- Level of the version of the instance of SQL Server.\r\n",
                "\t\t,@ProductVersion AS Version -- Version of the instance of SQL Server, in the form of 'major.minor.build.revision'.\r\n",
                "\t\t,ISNULL(SERVERPROPERTY('ProductUpdateLevel'),'') AS CU -- Update level of the current build.\r\n",
                "\t\t,CASE WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'Y' ELSE 'N' END AS IsClustered -- Server instance is configured in a failover cluster.\r\n",
                "\t\t,CASE WHEN SERVERPROPERTY('IsFullTextInstalled') = 1 THEN 'Y' ELSE 'N' END AS IsFullTextInstalled -- Full-text and semantic indexing components are installed.\r\n",
                "\t\t,CASE WHEN SERVERPROPERTY('IsHadrEnabled') = 1 THEN 'Y' ELSE 'N' END AS IsHadrEnabled -- Always On Availability Groups is enabled on this server instance.\r\n",
                "\t\t,CAST(ROUND(os.physical_memory_kb / 1024.0 / 1024.0,0) AS INT) AS MemoryGB -- Specifies the total amount of physical memory on the machine. Not nullable. Converted to GB.\r\n",
                "\t\t,@sql_memory_model_desc AS MemoryModel\r\n",
                "\t\t,mn.memory_nodes AS MemoryNodes\r\n",
                "\t\t,@ProcessorNameString AS Processor\r\n",
                "\t\t,os.cpu_count AS LogicalCPU\r\n",
                "\t\t,tmp_add.socket_count AS SocketCount\r\n",
                "\t\t,tmp_add.cores_per_socket AS CoresPerSocket\r\n",
                "\t\t,tmp_add.numa_node_count AS NUMANodeCount\r\n",
                "\t\t,os.max_workers_count AS MaxWorkersCount\r\n",
                "\t\t,@affinity_type_desc AS AffinityType\r\n",
                "\t\t,tmp_add.softnuma_configuration_desc AS SoftNUMAConfiguration\r\n",
                "\t\t,COALESCE(wi.host_platform\r\n",
                "\t\t\t,CASE WHEN wi.windows_release = '6.1' THEN 'Windows Server 2008 R2'\r\n",
                "\t\t\t\tWHEN wi.windows_release = '6.2' THEN 'windows Server 2012'\r\n",
                "\t\t\t\tWHEN wi.windows_release = '6.3' THEN 'windows Server 2012 R2'\r\n",
                "\t\t\t\tWHEN wi.windows_release = '10.0' THEN 'Windows Server 2016'\r\n",
                "\t\t\t\tELSE 'UNKNOWN'\r\n",
                "\t\t\tEND -- https://msdn.microsoft.com/library/ms724832%28vs.85%29.aspx?f=255&MSPPError=-2147217396\r\n",
                "\t\t) AS OS\r\n",
                "\t\t,wi.host_distribution AS OSDescription\r\n",
                "\t\t,wi.windows_release AS OSVersion -- For Windows, returns the release number. Cannot be NULL.\r\n",
                "\t\t,wi.windows_service_pack_level AS OSServicePack -- For Windows, returns the service pack number. Cannot be NULL.\r\n",
                "\t\t,@BiosReleaseDate AS BiosDate\r\n",
                "FROM\tsys.dm_os_sys_info os\r\n",
                "\t\tCROSS JOIN #os_windows_info wi\r\n",
                "\t\tCROSS JOIN sys.dm_exec_connections ec\r\n",
                "\t\tCROSS JOIN (select COUNT(*) as memory_nodes from sys.dm_os_memory_nodes where memory_node_id <> 64) mn\r\n",
                "\t\tCROSS JOIN #os_sys_info_adds tmp_add\r\n",
                "WHERE\tec.session_id = @@SPID;\r\n",
                "\r\n",
                "DROP TABLE IF EXISTS #os_sys_info_adds;\r\n",
                "DROP TABLE IF EXISTS #os_windows_info;\r\n",
                "\r\n",
                "DECLARE @LoginAuditing INT\r\n",
                "\t\t,@DefaultData NVARCHAR(512)\r\n",
                "\t\t,@DefaultLog NVARCHAR(512)\r\n",
                "\t\t,@DefaultBackup NVARCHAR(512)\r\n",
                "\t\t,@NUMANodes INT\r\n",
                "\t\t,@TempDbFiles INT\r\n",
                "\t\t,@RegKey VARCHAR(100)\r\n",
                "\t\t,@SQLVersion VARCHAR(10)\r\n",
                "\t\t,@FailSafeOperator VARCHAR(50)\r\n",
                "\t\t,@DatabaseMailProfile VARCHAR(50);\r\n",
                "\r\n",
                "EXEC master..xp_instance_regread \r\n",
                "    @rootkey='HKEY_LOCAL_MACHINE',\r\n",
                "    @key='SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer',\r\n",
                "    @value_name='AuditLevel',\r\n",
                "    @value=@LoginAuditing OUTPUT;\r\n",
                "\t\r\n",
                "IF @Version >= 11\r\n",
                "BEGIN\r\n",
                "\tSET @DefaultData = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(128));\r\n",
                "\tSET @DefaultLog = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS VARCHAR(128));\r\n",
                "END;\r\n",
                "ELSE\r\n",
                "BEGIN\r\n",
                "\tEXEC master.dbo.xp_instance_regread \r\n",
                "\t\t@rootkey='HKEY_LOCAL_MACHINE',\r\n",
                "\t\t@key='SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer',\r\n",
                "\t\t@value_name='DefaultData',\r\n",
                "\t\t@value=@DefaultData OUTPUT;\r\n",
                "\r\n",
                "\tEXEC master.dbo.xp_instance_regread \r\n",
                "\t\t@rootkey='HKEY_LOCAL_MACHINE',\r\n",
                "\t\t@key='SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer',\r\n",
                "\t\t@value_name='DefaultLog',\r\n",
                "\t\t@value=@DefaultLog OUTPUT;\r\n",
                "END;\r\n",
                "\r\n",
                "EXEC master.dbo.xp_instance_regread \r\n",
                "    @rootkey='HKEY_LOCAL_MACHINE',\r\n",
                "    @key='SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer',\r\n",
                "    @value_name='BackupDirectory',\r\n",
                "    @value=@DefaultBackup OUTPUT;\r\n",
                "\t\r\n",
                "SELECT @SQLVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128)),2)\r\n",
                "SET @RegKey = REPLACE('SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL00.MSSQLSERVER\\SQLServerAgent', '00', @SQLVersion);\r\n",
                "EXECUTE master.sys.xp_regread 'HKEY_LOCAL_MACHINE', @RegKey, 'AlertFailSafeOperator', @FailSafeOperator output;\r\n",
                "EXECUTE master.sys.xp_regread 'HKEY_LOCAL_MACHINE', @RegKey, 'DatabaseMailProfile', @DatabaseMailProfile output;\r\n",
                "\r\n",
                "/*\r\n",
                "http://www.sqlpassion.at/archive/2016/10/17/how-many-numa-nodes-do-i-have/\r\n",
                "For every available NUMA node SQL Server creates one dedicated Memory Node (besides Memory Node ID 64, which is always\r\n",
                "present for the Dedicated Admin Connection).\r\n",
                "*/\r\n",
                "\r\n",
                "SELECT\t@TempDbFiles = COUNT(*)\r\n",
                "FROM\ttempdb.sys.database_files\r\n",
                "WHERE\ttype = 0;\r\n",
                "\r\n",
                "SELECT\tCAST(ROUND(MAX(os.physical_memory_kb) / 1024.0 / 1024.0,0) AS INT) AS HostMemory_GB\r\n",
                "\t\t,CAST(MAX(CASE WHEN config.name = 'min server memory (MB)' THEN config.value END) AS INT) / 1024 AS MinMemory_GB\r\n",
                "\t\t,CAST(MAX(CASE WHEN config.name = 'max server memory (MB)' THEN config.value END) AS INT) / 1024 AS MaxMemory_GB\r\n",
                "\t\t,MAX(CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 THEN 'Y' ELSE 'N' END) AS MixedMode\r\n",
                "\t\t,MAX(CASE @LoginAuditing\r\n",
                "\t\t\t\tWHEN 0 THEN 'None'\r\n",
                "\t\t\t\tWHEN 1 THEN 'Successful Logins Only'\r\n",
                "\t\t\t\tWHEN 2 THEN 'Failed Logins Only'\r\n",
                "\t\t\t\tWHEN 3 THEN 'Both Failed and Successful Logins'\r\n",
                "\t\tEND) AS LoginAuditing\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'cross db ownership chaining' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS CrossDBChaining\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'remote access' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS RemoteConnections\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'remote proc trans' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS ReqDistTransaction\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'fill factor (%)' THEN config.value END) AS DefaultFillFactor\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'backup checksum default' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS ChecksumBackup\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'backup compression default' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS CompressBackup\r\n",
                "\t\t,@DefaultData AS DataPath\r\n",
                "\t\t,@DefaultLog AS LogPath\r\n",
                "\t\t,@DefaultBackup AS BackupPath\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'clr enabled' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS CLREnabled\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'clr strict security' AND config.value = 1 THEN 'Y'\r\n",
                "\t\t\t\tWHEN @Version < 14 THEN ''\r\n",
                "\t\t\t\tELSE 'N'\r\n",
                "\t\t\tEND) AS CLRStrictSecurity\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'filestream access level' THEN CASE CAST(config.value AS INT)\r\n",
                "\t\t\t\tWHEN 0 THEN 'Disabled'\r\n",
                "\t\t\t\tWHEN 1 THEN 'Transact-SQL access enabled'\r\n",
                "\t\t\t\tWHEN 2 THEN 'Full access enabled'\r\n",
                "\t\tEND END) AS FILESTREAMAccessLevel\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'server trigger recursion' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS RecursiveTriggers\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'optimize for ad hoc workloads' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS OptimizeForAdHocWorkloads\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'scan for startup procs' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS ScanForStartupProcs\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'cost threshold for parallelism' THEN config.value END) AS CostThresholdParallelism\r\n",
                "\t\t,MAX(os.cpu_count) AS HostCPUs\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'max degree of parallelism' THEN config.value END) AS MAXDOP\r\n",
                "\t\t,@TempDbFiles AS TempDbFiles\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'Database Mail XPs' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS DBMailEnabled\r\n",
                "\t\t,MAX(CASE WHEN config.name = 'remote admin connections' AND config.value = 1 THEN 'Y' ELSE 'N' END) AS DACEnabled\r\n",
                "\t\t,@FailSafeOperator as FailsafeOperator\r\n",
                "\t\t,@DatabaseMailProfile as DatabaseMailProfile\r\n",
                "\t\t,CAST(SERVERPROPERTY('Collation') AS NVARCHAR(128)) AS Collation\r\n",
                "FROM\tsys.configurations config\r\n",
                "\t\tCROSS JOIN sys.dm_os_sys_info os;\r\n",
                "\r\n",
                "---- http://blog.waynesheffield.com/wayne/archive/2017/09/registry-sql-server-startup-parameters/\r\n",
                "\r\n",
                "--DECLARE @RegHive    VARCHAR(50),\r\n",
                "--        @RegKey     VARCHAR(100);\r\n",
                " \r\n",
                "--SET @RegHive = 'HKEY_LOCAL_MACHINE';\r\n",
                "--SET @RegKey  = 'Software\\Microsoft\\MSSQLSERVER\\MSSQLServer\\Parameters';\r\n",
                " \r\n",
                "---- Get all of the arguments / parameters when starting up the service.\r\n",
                "--DECLARE @SQLArgs TABLE (\r\n",
                "--    Value   VARCHAR(50),\r\n",
                "--    Data    VARCHAR(500),\r\n",
                "--    ArgNum  AS CONVERT(INTEGER, REPLACE(Value, 'SQLArg', '')));\r\n",
                " \r\n",
                "--INSERT INTO @SQLArgs\r\n",
                "--EXECUTE master.sys.xp_instance_regenumvalues @RegHive, @RegKey;\r\n",
                " \r\n",
                "--SELECT  Value AS StartupParam\r\n",
                "--       ,Data AS StartupData\r\n",
                "--FROM    @SQLArgs;\r\n",
                "\r\n",
                "SELECT\tvalue_name AS StartupParam\r\n",
                "\t\t,value_data AS StartupData\r\n",
                "FROM\tsys.dm_server_registry  \r\n",
                "WHERE\tregistry_key LIKE N'%Parameters';  \r\n",
                "\r\n",
                "DBCC TRACESTATUS;\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "8bc81a7f-e223-4bff-b086-640d6270accf",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get DatabaseDetail and Config"
            ],
            "metadata": {
                "azdata_cell_guid": "a2a907ca-ebf3-4620-bb4b-0cc75cc85f5e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "\r\n",
                "DECLARE @tmp_ExtProperties TABLE (DatabaseName VARCHAR(128), name NVARCHAR(60), value SQL_VARIANT);\r\n",
                "\r\n",
                "INSERT INTO @tmp_ExtProperties (DatabaseName, name, value)\r\n",
                "EXEC sp_MSForEachDB 'Use [?];\r\n",
                "SELECT DB_NAME() AS DatabaseName, name, value FROM sys.extended_properties WHERE name = ''Confluence Description''';\r\n",
                "\r\n",
                "DECLARE @tmp_FileStream TABLE (DatabaseName VARCHAR(128), non_transacted_access TINYINT, directory_name NVARCHAR(255));\r\n",
                "\r\n",
                "INSERT @tmp_FileStream (DatabaseName, non_transacted_access, directory_name)\r\n",
                "EXEC sp_MSForEachDB 'Use [?];\r\n",
                "SELECT DB_NAME() AS DatabaseName, non_transacted_access, directory_name FROM sys.database_filestream_options';\r\n",
                "\r\n",
                "DECLARE @tmp_QueryStore TABLE (\r\n",
                "\tDatabaseName VARCHAR(128)\r\n",
                "\t,querystore_desired_state SMALLINT\r\n",
                "\t,querystore_actual_state SMALLINT\r\n",
                "\t,querystore_readonly_reason INT\r\n",
                "\t,querystore_current_storage_size_mb BIGINT\r\n",
                "\t,querystore_flush_interval_seconds BIGINT\r\n",
                "\t,querystore_interval_length_minutes BIGINT\r\n",
                "\t,querystore_max_storage_size_mb BIGINT\r\n",
                "\t,querystore_stale_query_threshold_days BIGINT\r\n",
                "\t,querystore_max_plans_per_query BIGINT\r\n",
                "\t,querystore_query_capture_mode SMALLINT\r\n",
                "\t,querystore_size_based_cleanup_mode SMALLINT\r\n",
                "\t,querystore_wait_stats_capture_mode SMALLINT\r\n",
                ");\r\n",
                "\r\n",
                "DECLARE @QS_SQL NVARCHAR(MAX)\r\n",
                "\t\t,@Version VARCHAR(50) = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128)),2);\r\n",
                "\r\n",
                "IF @Version > 12\r\n",
                "BEGIN\r\n",
                "SET @QS_SQL = 'Use [?];\r\n",
                "SELECT DB_NAME() AS DatabaseName\r\n",
                "\t,desired_state\r\n",
                "\t,actual_state\r\n",
                "\t,readonly_reason\r\n",
                "\t,current_storage_size_mb\r\n",
                "\t,flush_interval_seconds\r\n",
                "\t,interval_length_minutes\r\n",
                "\t,max_storage_size_mb\r\n",
                "\t,stale_query_threshold_days\r\n",
                "\t,max_plans_per_query\r\n",
                "\t,query_capture_mode\r\n",
                "\t,size_based_cleanup_mode'\r\n",
                "+ CASE WHEN @Version > 13 THEN '\t,wait_stats_capture_mode' ELSE '\t,NULL' END + '\r\n",
                "FROM sys.database_query_store_options';\r\n",
                "\r\n",
                "INSERT @tmp_QueryStore (\r\n",
                "\tDatabaseName\r\n",
                "\t,querystore_desired_state\r\n",
                "\t,querystore_actual_state\r\n",
                "\t,querystore_readonly_reason\r\n",
                "\t,querystore_current_storage_size_mb\r\n",
                "\t,querystore_flush_interval_seconds\r\n",
                "\t,querystore_interval_length_minutes\r\n",
                "\t,querystore_max_storage_size_mb\r\n",
                "\t,querystore_stale_query_threshold_days\r\n",
                "\t,querystore_max_plans_per_query\r\n",
                "\t,querystore_query_capture_mode\r\n",
                "\t,querystore_size_based_cleanup_mode\r\n",
                "\t,querystore_wait_stats_capture_mode\r\n",
                ")\r\n",
                "EXEC sp_MSForEachDB @QS_SQL;\r\n",
                "END;\r\n",
                "\r\n",
                "SELECT *\r\n",
                "INTO #databases\r\n",
                "FROM sys.databases;\r\n",
                "\r\n",
                "IF @Version < 12\r\n",
                "BEGIN\r\n",
                "\tALTER TABLE #databases ADD\r\n",
                "\t\tis_auto_create_stats_incremental_on BIT\r\n",
                "\t\t,delayed_durability INT\r\n",
                "\t\t,is_query_store_on BIT;\r\n",
                "END;\r\n",
                "\r\n",
                "IF @Version < 13\r\n",
                "BEGIN\r\n",
                "\tALTER TABLE #databases ADD\r\n",
                "\t\tis_mixed_page_allocation_on BIT;;\r\n",
                "END;\r\n",
                "\r\n",
                "CREATE TABLE #dm_hadr_database_replica_states (\r\n",
                "\tdatabase_id INT\r\n",
                "\t,group_id UNIQUEIDENTIFIER\r\n",
                "\t,is_primary_replica BIT\r\n",
                ");\r\n",
                "\r\n",
                "IF @Version > 12\r\n",
                "\tINSERT #dm_hadr_database_replica_states (database_id, group_id, is_primary_replica)\r\n",
                "\tSELECT\tc.database_id\r\n",
                "\t\t\t,b.group_id\r\n",
                "\t\t\t,sys.fn_hadr_is_primary_replica (DB_Name(c.database_id)) AS is_primary_replica\r\n",
                "\tFROM\tsys.availability_replicas AS b\r\n",
                "\t\t\tJOIN sys.dm_hadr_database_replica_states AS c ON b.replica_id = c.replica_id\r\n",
                "\tWHERE\tb.replica_server_name = @@SERVERNAME;\r\n",
                "ELSE\r\n",
                "\tINSERT #dm_hadr_database_replica_states (database_id, group_id, is_primary_replica)\r\n",
                "\tSELECT c.database_id, a.group_id\r\n",
                "\t\t,CASE a.role_desc\r\n",
                "\t\t\tWHEN 'PRIMARY' THEN 1\r\n",
                "\t\t\tWHEN 'SECONDARY' THEN 0\r\n",
                "\t\t\tWHEN 'RESOLVING' THEN 0\r\n",
                "\t\t\tELSE NULL\r\n",
                "\t\tEND AS is_primary_replica\r\n",
                "\tFROM sys.dm_hadr_availability_replica_states AS a\r\n",
                "\t\tJOIN sys.availability_replicas AS b ON b.replica_id = a.replica_id\r\n",
                "\t\tJOIN sys.dm_hadr_database_replica_states AS c ON a.replica_id = c.replica_id\r\n",
                "\tWHERE b.replica_server_name = @@SERVERNAME;\r\n",
                "\r\n",
                "SELECT ag_id, ag_name\r\n",
                "INTO #dm_hadr_name_id_map\r\n",
                "FROM sys.dm_hadr_name_id_map;\r\n",
                "\r\n",
                "SELECT sid, name\r\n",
                "INTO #server_principals\r\n",
                "FROM sys.server_principals;\r\n",
                "\r\n",
                "select distinct\r\n",
                "\t@@SERVERNAME as servername\r\n",
                "\t,sd.name as databasename\r\n",
                "\t,grp.ag_name as agname\r\n",
                "\t,hdrs.is_primary_replica\r\n",
                "\t,ep.value as description\r\n",
                "\t,ISNULL(u.name, '') as databaseowner\r\n",
                "\t,sd.collation_name as collation\r\n",
                "\t,sd.recovery_model_desc as recoverymodel\r\n",
                "\t,sd.compatibility_level as compatibilitylevel\r\n",
                "\t,sd.containment_desc as containmenttype\r\n",
                "\t,sd.is_auto_close_on\r\n",
                "\t,sd.is_auto_create_stats_incremental_on\r\n",
                "\t,sd.is_auto_create_stats_on\r\n",
                "\t,sd.is_auto_shrink_on\r\n",
                "\t,sd.is_auto_update_stats_on\r\n",
                "\t,sd.is_auto_update_stats_async_on\r\n",
                "\t,sd.is_cursor_close_on_commit_on\r\n",
                "\t,sd.is_local_cursor_default\r\n",
                "\t,sd.snapshot_isolation_state\r\n",
                "\t,sd.is_ansi_null_default_on\r\n",
                "\t,sd.is_ansi_nulls_on\r\n",
                "\t,sd.is_ansi_padding_on\r\n",
                "\t,sd.is_ansi_warnings_on\r\n",
                "\t,sd.is_arithabort_on\r\n",
                "\t,sd.is_concat_null_yields_null_on\r\n",
                "\t,sd.is_db_chaining_on\r\n",
                "\t,sd.is_date_correlation_on\r\n",
                "\t,sd.delayed_durability\r\n",
                "\t,sd.is_mixed_page_allocation_on\r\n",
                "\t,sd.is_read_committed_snapshot_on\r\n",
                "\t,sd.is_numeric_roundabort_on\r\n",
                "\t,sd.is_parameterization_forced\r\n",
                "\t,sd.is_quoted_identifier_on\r\n",
                "\t,sd.is_recursive_triggers_on\r\n",
                "\t,sd.is_nested_triggers_on\r\n",
                "\t,sd.is_trustworthy_on\r\n",
                "\t,sd.page_verify_option\r\n",
                "\t,sd.target_recovery_time_in_seconds\r\n",
                "\t,sd.is_broker_enabled\r\n",
                "\t,sd.is_honor_broker_priority_on\r\n",
                "\t,sd.service_broker_guid\r\n",
                "\t,sd.is_read_only\r\n",
                "\t,sd.state\r\n",
                "\t,sd.is_in_standby\r\n",
                "\t,sd.is_encrypted\r\n",
                "\t,sd.is_master_key_encrypted_by_server\r\n",
                "\t,sd.user_access\r\n",
                "\t,sd.is_cdc_enabled\r\n",
                "\t,sd.is_fulltext_enabled\r\n",
                "\t,fs.non_transacted_access\r\n",
                "\t,fs.directory_name\r\n",
                "\t,sd.is_query_store_on\r\n",
                "\t,qs.querystore_desired_state\r\n",
                "\t,qs.querystore_actual_state\r\n",
                "\t,qs.querystore_readonly_reason\r\n",
                "\t,qs.querystore_current_storage_size_mb\r\n",
                "\t,qs.querystore_flush_interval_seconds\r\n",
                "\t,qs.querystore_interval_length_minutes\r\n",
                "\t,qs.querystore_max_storage_size_mb\r\n",
                "\t,qs.querystore_stale_query_threshold_days\r\n",
                "\t,qs.querystore_max_plans_per_query\r\n",
                "\t,qs.querystore_query_capture_mode\r\n",
                "\t,qs.querystore_size_based_cleanup_mode\r\n",
                "\t,qs.querystore_wait_stats_capture_mode\r\n",
                "from #databases as sd\r\n",
                "    left outer join #dm_hadr_database_replica_states as hdrs on hdrs.database_id = sd.database_id\r\n",
                "    left outer join #dm_hadr_name_id_map as grp on grp.ag_id = hdrs.group_id\r\n",
                "    left outer join @tmp_ExtProperties ep on ep.DatabaseName = sd.name\r\n",
                "\tleft outer join #server_principals u on sd.owner_sid = u.sid\r\n",
                "\tleft outer join @tmp_FileStream fs on fs.DatabaseName = sd.name\r\n",
                "\tleft outer join @tmp_QueryStore qs on qs.DatabaseName = sd.name\r\n",
                "where sd.name not in ('master','msdb','model','tempdb', 'ReportServer', 'ReportServerTempDB');\r\n",
                "\r\n",
                "IF OBJECT_ID('tempdb..#databases') IS NOT NULL\r\n",
                " DROP TABLE #databases;\r\n",
                "\r\n",
                "IF OBJECT_ID('tempdb..#dm_hadr_database_replica_states') IS NOT NULL\r\n",
                " DROP TABLE #dm_hadr_database_replica_states;\r\n",
                " \r\n",
                "IF OBJECT_ID('tempdb..#dm_hadr_name_id_map') IS NOT NULL\r\n",
                " DROP TABLE #dm_hadr_name_id_map;\r\n",
                "\r\n",
                "IF OBJECT_ID('tempdb..#server_principals') IS NOT NULL\r\n",
                " DROP TABLE #server_principals;\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "21136409-968f-488c-8d3f-328e61e68544",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get Database Scoped Config"
            ],
            "metadata": {
                "azdata_cell_guid": "33a61a7a-a703-4b01-9aa0-9632075d796f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "DECLARE @Version VARCHAR(50) = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128)),2)\r\n",
                "\t\t,@SQL NVARCHAR(MAX);\r\n",
                "\t\t\r\n",
                "IF @Version = 13\r\n",
                "\tSET @SQL = N'\r\n",
                "\tSELECT\t@@SERVERNAME as servername\r\n",
                "\t\t    ,DB_NAME() as dbname\r\n",
                "\t\t\t,name\r\n",
                "\t\t\t,value\r\n",
                "\t\t\t,value_for_secondary\r\n",
                "\t\t\t,NULL AS is_value_default\r\n",
                "\tFROM\tsys.database_scoped_configurations;'\r\n",
                "ELSE IF @Version > 13\r\n",
                "\tSET @SQL = N'\r\n",
                "\tSELECT\t@@SERVERNAME as servername\r\n",
                "\t\t    ,DB_NAME() as dbname\r\n",
                "\t\t\t,name\r\n",
                "\t\t\t,value\r\n",
                "\t\t\t,value_for_secondary\r\n",
                "\t\t\t,is_value_default\r\n",
                "\tFROM\tsys.database_scoped_configurations;'\r\n",
                "\r\n",
                "EXEC (@SQL);"
            ],
            "metadata": {
                "azdata_cell_guid": "d820b517-da65-41b8-96ee-e8216154b158",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get Database File Detail"
            ],
            "metadata": {
                "azdata_cell_guid": "5d4aab16-11ce-43ce-bf6c-63f9b377315e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "\r\n",
                "declare @sql_used nvarchar(max) = N'USE [?]; INSERT #DbUsed (DatabaseId, Used, Name) SELECT DB_ID(), CONVERT(BIGINT, FILEPROPERTY(name, ''SpaceUsed'')) * 8 / 1024, name from sys.database_files'\r\n",
                "\t\t,@sql_log nvarchar(max) = N'USE [?]; INSERT #LogInfo (RecoveryUnitId, FileId, FileSize, StartOffset, FSeqNo, [Status], Parity, CreateLSN) EXEC(''DBCC LOGINFO'')'\r\n",
                "\t\t,@Version VARCHAR(50) = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128)),2);\r\n",
                "\r\n",
                "if OBJECT_ID('tempdb..#DbUsed') is not null drop table #DbUsed;\r\n",
                "create table #DbUsed (DatabaseId INT, Used BIGINT, name VARCHAR(128));\r\n",
                "\r\n",
                "if OBJECT_ID('tempdb..#LogInfo') is not null drop table #LogInfo;\r\n",
                "create table #LogInfo (DatabaseName NVARCHAR(128) default DB_NAME(), RecoveryUnitId INT, FileId INT, FileSize BIGINT, StartOffset BIGINT, FSeqNo INT, [Status] TINYINT, Parity TINYINT, CreateLSN NUMERIC(25,0));\r\n",
                "\r\n",
                "if OBJECT_ID('tempdb..#dm_hadr_database_replica_states') is not null drop table #dm_hadr_database_replica_states;\r\n",
                "CREATE TABLE #dm_hadr_database_replica_states (database_id INT, is_primary_replica BIT);\r\n",
                "\r\n",
                "exec sp_msforeachdb @sql_used;\r\n",
                "exec sp_msforeachdb @sql_log;\r\n",
                "\r\n",
                "IF @Version > 12\r\n",
                "\tINSERT #dm_hadr_database_replica_states (database_id, is_primary_replica)\r\n",
                "\tSELECT\tc.database_id\r\n",
                "\t\t\t,sys.fn_hadr_is_primary_replica (DB_Name(c.database_id)) AS is_primary_replica\r\n",
                "\tFROM\tsys.availability_replicas AS b\r\n",
                "\t\t\tJOIN sys.dm_hadr_database_replica_states AS c ON b.replica_id = c.replica_id\r\n",
                "\tWHERE\tb.replica_server_name = @@SERVERNAME;\r\n",
                "ELSE\r\n",
                "\tINSERT #dm_hadr_database_replica_states (database_id, is_primary_replica)\r\n",
                "\tSELECT c.database_id\r\n",
                "\t\t,CASE a.role_desc\r\n",
                "\t\t\tWHEN 'PRIMARY' THEN 1\r\n",
                "\t\t\tWHEN 'SECONDARY' THEN 0\r\n",
                "\t\t\tWHEN 'RESOLVING' THEN 0\r\n",
                "\t\t\tELSE NULL\r\n",
                "\t\tEND AS is_primary_replica\r\n",
                "\tFROM sys.dm_hadr_availability_replica_states AS a\r\n",
                "\t\tJOIN sys.availability_replicas AS b ON b.replica_id = a.replica_id\r\n",
                "\t\tJOIN sys.dm_hadr_database_replica_states AS c ON a.replica_id = c.replica_id\r\n",
                "\tWHERE b.replica_server_name = @@SERVERNAME;\r\n",
                "\r\n",
                "SELECT @@SERVERNAME as servername\r\n",
                "\t\t, DB_NAME(f.database_id) as databasename\r\n",
                "\t\t, UPPER(LEFT(f.physical_name, 3)) as volume\r\n",
                "\t\t, f.name\r\n",
                "\t\t, f.physical_name\r\n",
                "\t\t, CASE WHEN DB_NAME(f.database_id) = 'tempdb' THEN CONVERT(NVARCHAR(15), CONVERT(BIGINT, tmpdb.size) * 8 / 1024)\r\n",
                "\t\t\tELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, f.size) * 8 / 1024)\r\n",
                "\t\tEND as filesize_mb\r\n",
                "\t\t, tmp.Used\r\n",
                "\t\t, CASE f.max_size WHEN -1 THEN N'Unlimited' ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, f.max_size) * 8 / 1024) END as maxfilesize_mb\r\n",
                "\t\t, CASE f.is_percent_growth WHEN 1 THEN NULL ELSE (f.growth * 8 / 1024) END as filegrowth_mb\r\n",
                "\t\t, CASE f.is_percent_growth WHEN 1 THEN f.growth ELSE NULL END as filegrowth_pct\r\n",
                "\t\t, li.VLFs\r\n",
                "\t\t, rs.is_primary_replica\r\n",
                "\t\t, DEFAULT_DOMAIN() as domain\r\n",
                "FROM\tsys.master_files f\r\n",
                "\t\tLEFT OUTER JOIN #DbUsed tmp on f.database_id = tmp.DatabaseId and f.name = tmp.name\r\n",
                "\t\tLEFT OUTER JOIN (select DatabaseName, COUNT(FileId) as VLFs from #LogInfo group by DatabaseName) li on DB_NAME(f.database_id) = li.DatabaseName and f.name like '%log'\r\n",
                "\t\tLEFT OUTER JOIN #dm_hadr_database_replica_states rs on f.database_id = rs.database_id\r\n",
                "\t\tLEFT OUTER JOIN tempdb.sys.database_files tmpdb ON f.name = tmpdb.name AND DB_NAME(f.database_id) = 'tempdb';"
            ],
            "metadata": {
                "azdata_cell_guid": "98c16746-5cdc-4256-a736-ea0e07f8cd0f",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get Index Details"
            ],
            "metadata": {
                "azdata_cell_guid": "40730f11-e5ab-4dd7-8798-8da012f1d263"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "DECLARE @sql_indxinfo NVARCHAR(2000)\r\n",
                "\t\t,@sql_indxsize NVARCHAR(2000)\r\n",
                "\t\t,@Version VARCHAR(50) = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128)),2)\r\n",
                "\t\t,@IsHadrEnabled TINYINT = CONVERT(TINYINT,SERVERPROPERTY ('IsHadrEnabled'))\r\n",
                "\t\t,@RoleDesc NVARCHAR(60) = 'PRIMARY';\r\n",
                "\r\n",
                "if OBJECT_ID('tempdb..#IndxInfo') is not null drop table #IndxInfo;\r\n",
                "create table #IndxInfo (\r\n",
                "\tdatabaseid INT NOT NULL\r\n",
                "\t,objectid INT NOT NULL\r\n",
                "\t,indexid INT NOT NULL\r\n",
                "\t,databasename VARCHAR(128) NOT NULL\r\n",
                "\t,schemaname VARCHAR(128) NOT NULL\r\n",
                "\t,tablename VARCHAR(128) NOT NULL\r\n",
                "\t,indexname VARCHAR(128) NULL\r\n",
                "\t,indextype VARCHAR(60) NOT NULL\r\n",
                "\t,columns VARCHAR(2000) NULL\r\n",
                "\t,included_columns VARCHAR(2000) NULL\r\n",
                "\t,is_unique BIT NOT NULL\r\n",
                "\t,is_primary_key BIT NOT NULL\r\n",
                "\t,is_unique_constraint BIT NOT NULL\r\n",
                "\t,is_disabled BIT NOT NULL\r\n",
                "\t,has_filter BIT NOT NULL\r\n",
                "\t,filter_definition VARCHAR(2000) NULL\r\n",
                "\t,auto_created BIT NULL\r\n",
                "\t,fill_factor TINYINT NOT NULL\r\n",
                "\t,is_padded BIT NOT NULL\r\n",
                "\t,last_user_seek DATETIME NULL\r\n",
                "\t,last_user_scan DATETIME NULL\r\n",
                "\t,last_user_lookup DATETIME NULL\r\n",
                "\t,last_user_update DATETIME NULL\r\n",
                "\t,user_seeks BIGINT NULL\r\n",
                "\t,user_scans BIGINT NULL\r\n",
                "\t,user_lookups BIGINT NULL\r\n",
                "\t,user_updates BIGINT NULL\r\n",
                ");\r\n",
                "\r\n",
                "if OBJECT_ID('tempdb..#IndxSize') is not null drop table #IndxSize;\r\n",
                "create table #IndxSize (\r\n",
                "\tdatabaseid INT NOT NULL\r\n",
                "\t,objectid INT NOT NULL\r\n",
                "\t,indexid INT NOT NULL\r\n",
                "\t,partitions INT NOT NULL\r\n",
                "\t,rows BIGINT NOT NULL\r\n",
                "\t,total_pages BIGINT NOT NULL\r\n",
                "\t,used_pages BIGINT NOT NULL\r\n",
                "\t,in_row_data_pages BIGINT NOT NULL\r\n",
                "\t,lob_data_pages BIGINT NOT NULL\r\n",
                "\t,row_overflow_data_pages BIGINT NOT NULL\r\n",
                "\t,data_compression TINYINT NOT NULL\r\n",
                ");\r\n",
                "\r\n",
                "SET @sql_indxinfo = N'USE [?];\r\n",
                "IF DB_ID() > 4\r\n",
                "INSERT #IndxInfo\r\n",
                "SELECT\tDB_ID()\r\n",
                "\t\t,i.object_id\r\n",
                "\t\t,i.index_id\r\n",
                "\t\t,DB_NAME()\r\n",
                "\t\t,SCHEMA_NAME(o.schema_id)\r\n",
                "\t\t,o.name\r\n",
                "\t\t,i.name\r\n",
                "\t\t,i.type_desc\r\n",
                "       ,STUFF((\r\n",
                "            SELECT\tCASE WHEN ic.is_descending_key = 1 THEN '', '' + c.name + ''(-)'' ELSE '', '' + c.name END\r\n",
                "            FROM\tsys.index_columns ic\r\n",
                "\t\t\t\t\tJOIN sys.columns c\r\n",
                "\t\t\t\t\t\tON  c.object_id = ic.object_id\r\n",
                "\t\t\t\t\t\tAND c.column_id = ic.column_id\r\n",
                "            WHERE\tic.object_id = i.object_id\r\n",
                "            AND\t\tic.index_id = i.index_id\r\n",
                "            AND\t\tic.is_included_column = 0\r\n",
                "            ORDER BY ic.key_ordinal\r\n",
                "            FOR XML PATH('''')\r\n",
                "        ), 1, 2, '''')\r\n",
                "       ,STUFF((\r\n",
                "            SELECT\tCASE WHEN ic.is_descending_key = 1 THEN '', '' + c.name + ''(-)'' ELSE '', '' + c.name END\r\n",
                "            FROM\tsys.index_columns ic\r\n",
                "\t\t\t\t\tJOIN sys.columns c\r\n",
                "\t\t\t\t\t\tON  c.object_id = ic.object_id\r\n",
                "\t\t\t\t\t\tAND c.column_id = ic.column_id\r\n",
                "            WHERE\tic.object_id = i.object_id\r\n",
                "            AND\t\tic.index_id = i.index_id\r\n",
                "            AND\t\tic.is_included_column = 1\r\n",
                "            ORDER BY ic.key_ordinal\r\n",
                "            FOR XML PATH('''')\r\n",
                "        ), 1, 2, '''')\r\n",
                "\t\t,i.is_unique\r\n",
                "\t\t,i.is_primary_key\r\n",
                "\t\t,i.is_unique_constraint\r\n",
                "\t\t,i.is_disabled\r\n",
                "\t\t,i.has_filter\r\n",
                "\t\t,i.filter_definition'\r\n",
                "+ CASE WHEN @Version > 13 THEN '\t\t,i.auto_created' ELSE '\t\t,NULL' END +\r\n",
                "'\t\t,i.fill_factor\r\n",
                "\t\t,i.is_padded\r\n",
                "\t\t,u.last_user_seek\r\n",
                "\t\t,u.last_user_scan\r\n",
                "\t\t,u.last_user_lookup\r\n",
                "\t\t,u.last_user_update\r\n",
                "\t\t,u.user_seeks\r\n",
                "\t\t,u.user_scans\r\n",
                "\t\t,u.user_lookups\r\n",
                "\t\t,u.user_updates\r\n",
                "FROM\tsys.objects o\r\n",
                "\t\tJOIN sys.indexes i ON o.object_id = i.object_id\r\n",
                "\t\tLEFT JOIN sys.dm_db_index_usage_stats u ON i.object_id = u.object_id and i.index_id = u.index_id and u.database_id = DB_ID()\r\n",
                "WHERE\tOBJECTPROPERTY(o.object_id,''IsUserTable'') = 1;'\r\n",
                "\r\n",
                "SET @sql_indxsize = N'USE [?];\r\n",
                "IF DB_ID() > 4\r\n",
                "INSERT #IndxSize\r\n",
                "SELECT\tDB_ID()\r\n",
                "\t\t,i.object_id\r\n",
                "\t\t,i.index_id\r\n",
                "\t\t,COUNT(p.partition_id)\r\n",
                "\t\t,SUM(p.rows)\r\n",
                "\t\t,SUM(a.used_pages)\r\n",
                "\t\t,SUM(a.total_pages)\r\n",
                "\t\t,SUM(a.in_row)\r\n",
                "\t\t,SUM(a.lob)\r\n",
                "\t\t,SUM(a.row_overflow)\r\n",
                "\t\t,p.data_compression\r\n",
                "FROM\tsys.indexes i\r\n",
                "\t\tJOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id\r\n",
                "\t\tJOIN (\r\n",
                "\t\t\tSELECT\tcontainer_id\r\n",
                "\t\t\t\t\t,SUM(used_pages) as used_pages\r\n",
                "\t\t\t\t\t,SUM(total_pages) as total_pages\r\n",
                "\t\t\t\t\t,SUM(CASE WHEN type = 1 THEN data_pages ELSE 0 END) as in_row\r\n",
                "\t\t\t\t\t,SUM(CASE WHEN type = 2 THEN data_pages ELSE 0 END) as lob\r\n",
                "\t\t\t\t\t,SUM(CASE WHEN type = 3 THEN data_pages ELSE 0 END) as row_overflow\r\n",
                "\t\t\tFROM\tsys.allocation_units\r\n",
                "\t\t\tGROUP BY container_id\r\n",
                "\t\t) a ON p.partition_id = a.container_id\r\n",
                "WHERE\tOBJECTPROPERTY(i.object_id,''IsUserTable'') = 1\r\n",
                "GROUP BY i.object_id\r\n",
                "\t\t,i.index_id\r\n",
                "\t\t,p.data_compression;'\r\n",
                "\t\t\r\n",
                "IF @IsHadrEnabled = 0\r\n",
                "OR (\r\n",
                "\t@IsHadrEnabled = 1\r\n",
                "\tAND EXISTS (\r\n",
                "\t\tSELECT 1\r\n",
                "\t\tFROM sys.dm_hadr_availability_replica_states AS a\r\n",
                "\t\t\tJOIN sys.availability_replicas AS b\r\n",
                "\t\tON b.replica_id = a.replica_id\r\n",
                "\t\tWHERE b.replica_server_name = @@SERVERNAME\r\n",
                "\t\tAND\ta.role_desc = @RoleDesc\r\n",
                "\t)\r\n",
                ")\r\n",
                "BEGIN\r\n",
                "\tEXEC sp_msforeachdb @sql_indxinfo;\r\n",
                "\tEXEC sp_msforeachdb @sql_indxsize;\r\n",
                "END;\r\n",
                "\r\n",
                "SELECT\t@@SERVERNAME AS servername\r\n",
                "\t\t,i.databasename\r\n",
                "\t\t,i.schemaname\r\n",
                "\t\t,i.tablename\r\n",
                "\t\t,i.indexname\r\n",
                "\t\t,i.indextype\r\n",
                "\t\t,i.columns\r\n",
                "\t\t,i.included_columns\r\n",
                "\t\t,i.is_unique\r\n",
                "\t\t,i.is_primary_key\r\n",
                "\t\t,i.is_unique_constraint\r\n",
                "\t\t,i.is_disabled\r\n",
                "\t\t,i.has_filter\r\n",
                "\t\t,i.filter_definition\r\n",
                "\t\t,i.auto_created\r\n",
                "\t\t,i.fill_factor\r\n",
                "\t\t,i.is_padded\r\n",
                "\t\t,s.partitions\r\n",
                "\t\t,s.rows\r\n",
                "\t\t,s.total_pages\r\n",
                "\t\t,s.used_pages\r\n",
                "\t\t,s.in_row_data_pages\r\n",
                "\t\t,s.lob_data_pages\r\n",
                "\t\t,s.row_overflow_data_pages\r\n",
                "\t\t,s.data_compression\r\n",
                "\t\t,i.last_user_seek\r\n",
                "\t\t,i.last_user_scan\r\n",
                "\t\t,i.last_user_lookup\r\n",
                "\t\t,i.last_user_update\r\n",
                "\t\t,i.user_seeks\r\n",
                "\t\t,i.user_scans\r\n",
                "\t\t,i.user_lookups\r\n",
                "\t\t,i.user_updates\r\n",
                "FROM\t#IndxInfo i\r\n",
                "\t\tJOIN #IndxSize s\r\n",
                "\t\t\tON s.databaseid = i.databaseid\r\n",
                "\t\t\tAND s.objectid = i.objectid\r\n",
                "\t\t\tAND s.indexid = i.indexid\r\n",
                "WHERE\ti.databaseid > 4;\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "542f272c-ed86-4d5c-87a4-09b84ec8b7d3",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get SQL Agent Alerts"
            ],
            "metadata": {
                "azdata_cell_guid": "78639b0a-1a3a-4336-8bbc-aec8b5a7a9ae"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "SELECT @@SERVERNAME AS servername \r\n",
                "\t,name \r\n",
                "\t,message_id \r\n",
                "\t,severity \r\n",
                "\t,enabled \r\n",
                "\t,delay_between_responses \r\n",
                "\t,include_event_description \r\n",
                "\t,has_notification\r\n",
                "FROM msdb.dbo.sysalerts;\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "89c63639-041f-4978-9360-c8f530fa1491",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get SQL Agent Jobs"
            ],
            "metadata": {
                "azdata_cell_guid": "6ce0d59f-1540-459b-a693-0041839e6b14"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "-- Query to display schedules of jobs.  \r\n",
                "--      copied from http://www.sqlservercentral.com/scripts/Maintenance+and+Management/62222/\r\n",
                "--               posted there by \"Cowboy DBA\"\r\n",
                "--    ----------\r\n",
                "\r\n",
                "DECLARE @LastModDate DATETIME --= '11/1/2016'\r\n",
                "\t\t, @Enabled BIT --= 1; -- NULL returns both Enabled & Disabled jobs\r\n",
                "\r\n",
                "SELECT\tSERVERPROPERTY ('ServerName') as [Server]\r\n",
                "\t\t, msdb.dbo.sysjobs.name as [Job]\r\n",
                "\t\t, [JobSteps] = stepcount\r\n",
                "\t\t, [Enabled] = CASE msdb.dbo.sysjobs.[enabled] WHEN 1 THEN 'Y' ELSE 'N' END \r\n",
                "\t\t, [StartStepId] = msdb.dbo.sysjobs.start_step_id\r\n",
                "\t\t, [Schedule] =\r\n",
                "\t\t\tCASE WHEN msdb.dbo.sysjobs.[enabled] = 0\r\n",
                "\t\t\t\t\tTHEN 'Disabled'\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysjobs.job_id IS NULL\r\n",
                "\t\t\t\t\tTHEN 'Unscheduled'\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysschedules.freq_type = 0x1 -- OneTime\r\n",
                "\t\t\t\t\tTHEN 'Once on ' + CONVERT(CHAR(10), CAST(CAST(msdb.dbo.sysschedules.active_start_date AS VARCHAR)AS DATETIME), 101)\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysschedules.freq_type = 0x4 -- Daily\r\n",
                "\t\t\t\t\tTHEN 'Daily'\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysschedules.freq_type = 0x8 -- weekly\r\n",
                "\t\t\t\t\tTHEN CASE WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1\r\n",
                "\t\t\t\t\t\t\t\tTHEN 'Wkly on '\r\n",
                "\t\t\t\t\t\t\tWHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1\r\n",
                "\t\t\t\t\t\t\t\tTHEN 'Every ' + CAST(msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR) + ' wks on '\r\n",
                "\t\t\t\t\t\tEND\r\n",
                "\t\t\t\t\t\t+ LEFT(\r\n",
                "\t\t\t\t\t\t\tCASE WHEN msdb.dbo.sysschedules.freq_interval &  1 =  1 THEN 'Su, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval &  2 =  2 THEN 'M, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval &  4 =  4 THEN 'Tu, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval &  8 =  8 THEN 'W, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Th, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'F, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Sa, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t, LEN(\r\n",
                "\t\t\t\t\t\t\t\tCASE WHEN msdb.dbo.sysschedules.freq_interval &  1 =  1 THEN 'Su, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval &  2 =  2 THEN 'M, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval &  4 =  4 THEN 'Tu, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval &  8 =  8 THEN 'W, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Th, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'F, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Sa, ' ELSE '' END\r\n",
                "\t\t\t\t\t\t\t)  - 1  -- LEN() ignores trailing spaces\r\n",
                "\t\t\t\t\t\t)\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysschedules.freq_type = 0x10 -- monthly\r\n",
                "\t\t\t\t\tTHEN CASE WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1\r\n",
                "\t\t\t\t\t\t\t\t\tTHEN 'Mthly on the '\r\n",
                "\t\t\t\t\t\t\t\tWHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1\r\n",
                "\t\t\t\t\t\t\t\t   THEN 'Every ' + CAST(msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR) + ' mo on the '\r\n",
                "\t\t\t\t\t\t\tEND\r\n",
                "\t\t\t\t\t\t+ CAST(msdb.dbo.sysschedules.freq_interval AS VARCHAR)\r\n",
                "\t\t\t\t\t\t+ CASE WHEN msdb.dbo.sysschedules.freq_interval IN (1, 21, 31)\r\n",
                "\t\t\t\t\t\t\t\t\tTHEN 'st'\r\n",
                "\t\t\t\t\t\t\t\tWHEN msdb.dbo.sysschedules.freq_interval IN (2, 22)\r\n",
                "\t\t\t\t\t\t\t\t\tTHEN 'nd'\r\n",
                "\t\t\t\t\t\t\t\tWHEN msdb.dbo.sysschedules.freq_interval IN (3, 23)\r\n",
                "\t\t\t\t\t\t\t\t\tTHEN 'rd'\r\n",
                "\t\t\t\t\t\t\t\tELSE 'th'\r\n",
                "\t\t\t\t\t\t\tEND\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysschedules.freq_type = 0x20 -- monthly relative\r\n",
                "\t\t\t\t\t\tTHEN CASE WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1\r\n",
                "\t\t\t\t\t\t\t\t\t\tTHEN 'Mthly on the '\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1\r\n",
                "\t\t\t\t\t\t\t\t\t\tTHEN 'Every '+ CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR )+ ' mths on the '\r\n",
                "\t\t\t\t\t\t\t\tEND\r\n",
                "\t\t\t\t\t\t+ CASE msdb.dbo.sysschedules.freq_relative_interval\r\n",
                "\t\t\t\t\t\t\t\tWHEN 0x01 THEN 'first '\r\n",
                "\t\t\t\t\t\t\t\tWHEN 0x02 THEN 'second '\r\n",
                "\t\t\t\t\t\t\t\tWHEN 0x04 THEN 'third '\r\n",
                "\t\t\t\t\t\t\t\tWHEN 0x08 THEN 'fourth '\r\n",
                "\t\t\t\t\t\t\t\tWHEN 0x10 THEN 'last '\r\n",
                "\t\t\t\t\t\t\tEND\r\n",
                "\t\t\t\t\t\t+ CASE msdb.dbo.sysschedules.freq_interval\r\n",
                "\t\t\t\t\t\t\t\tWHEN  1 THEN 'Su'\r\n",
                "\t\t\t\t\t\t\t\tWHEN  2 THEN 'M'\r\n",
                "\t\t\t\t\t\t\t\tWHEN  3 THEN 'Tu'\r\n",
                "\t\t\t\t\t\t\t\tWHEN  4 THEN 'W'\r\n",
                "\t\t\t\t\t\t\t\tWHEN  5 THEN 'Th'\r\n",
                "\t\t\t\t\t\t\t\tWHEN  6 THEN 'F'\r\n",
                "\t\t\t\t\t\t\t\tWHEN  7 THEN 'Sa'\r\n",
                "\t\t\t\t\t\t\t\tWHEN  8 THEN 'day'\r\n",
                "\t\t\t\t\t\t\t\tWHEN  9 THEN 'wk day'\r\n",
                "\t\t\t\t\t\t\t\tWHEN 10 THEN 'wknd day'\r\n",
                "\t\t\t\t\t\t\tEND\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysschedules.freq_type = 0x40\r\n",
                "\t\t\t\t   THEN 'Auto start when SQLServerAgent starts'\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysschedules.freq_type = 0x80\r\n",
                "\t\t\t\t   THEN 'Starts whenever CPUs become idle'\r\n",
                "\t\t\t\tELSE ''\r\n",
                "\t\t\tEND\r\n",
                "\t\t+ CASE WHEN msdb.dbo.sysjobs.[enabled] = 0\r\n",
                "\t\t\t\t\tTHEN ''\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysjobs.job_id IS NULL\r\n",
                "\t\t\t\t\tTHEN ''\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysschedules.freq_subday_type = 0x1\r\n",
                "\t\t\t\tOR msdb.dbo.sysschedules.freq_type = 0x1\r\n",
                "\t\t\t\t\tTHEN ' at '\r\n",
                "\t\t\t\t\t\t+ CASE -- Depends on time being integer to drop right-side digits\r\n",
                "\t\t\t\t\t\t\tWHEN(msdb.dbo.sysschedules.active_start_time % 1000000)/10000 = 0\r\n",
                "\t\t\t\t\t\t\t\tTHEN '12'\r\n",
                "\t\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)))\r\n",
                "\t\t\t\t\t\t\t\t\t+ CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ' AM'\r\n",
                "\t\t\t\t\t\t\tWHEN (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 10\r\n",
                "\t\t\t\t\t\t\t\tTHEN CONVERT(CHAR(1),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t\t+ CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ' AM'\r\n",
                "\t\t\t\t\t\t\tWHEN (msdb.dbo.sysschedules.active_start_time % 1000000)/10000 < 12\r\n",
                "\t\t\t\t\t\t\t\tTHEN CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t\t+ CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ' AM'\r\n",
                "\t\t\t\t\t\t\tWHEN (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 22\r\n",
                "\t\t\t\t\t\t\t\tTHEN CONVERT(CHAR(1),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t\t+ CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ' PM'\r\n",
                "\t\t\t\t\t\t\tELSE CONVERT(CHAR(2),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12)\r\n",
                "\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t+ CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) \r\n",
                "\t\t\t\t\t\t\t\t+ ' PM'\r\n",
                "\t\t\t\t\t\tEND\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysschedules.freq_subday_type IN (0x2, 0x4, 0x8)\r\n",
                "\t\t\t\t\tTHEN ' every '\r\n",
                "\t\t\t\t\t\t+ CAST(msdb.dbo.sysschedules.freq_subday_interval AS VARCHAR)\r\n",
                "\t\t\t\t\t\t+ CASE freq_subday_type\r\n",
                "\t\t\t\t\t\t\tWHEN 0x2\r\n",
                "\t\t\t\t\t\t\t\tTHEN ' sec'\r\n",
                "\t\t\t\t\t\t\tWHEN 0x4\r\n",
                "\t\t\t\t\t\t\t\tTHEN ' min'\r\n",
                "\t\t\t\t\t\t\tWHEN 0x8\r\n",
                "\t\t\t\t\t\t\t\tTHEN ' hr'\r\n",
                "\t\t\t\t\t\tEND\r\n",
                "\t\t\t\t\t+ CASE\r\n",
                "\t\t\t\t\t\tWHEN msdb.dbo.sysschedules.freq_subday_interval > 1\r\n",
                "\t\t\t\t\t\t\tTHEN 's'\r\n",
                "\t\t\t\t\t\tELSE '' -- Added default 3/21/08; John Arnott\r\n",
                "\t\t\t\t\tEND\r\n",
                "\t\t\t\tELSE ''\r\n",
                "\t\tEND\r\n",
                "\t\t+ CASE WHEN msdb.dbo.sysjobs.[enabled] = 0\r\n",
                "\t\t\t\t\tTHEN ''\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysjobs.job_id IS NULL\r\n",
                "\t\t\t\t\tTHEN ''\r\n",
                "\t\t\t\tWHEN msdb.dbo.sysschedules.freq_subday_type IN (0x2, 0x4, 0x8)\r\n",
                "\t\t\t\t\tTHEN ' btw '\r\n",
                "\t\t\t\t\t\t+ CASE -- Depends on time being integer to drop right-side digits\r\n",
                "\t\t\t\t\t\t\tWHEN(msdb.dbo.sysschedules.active_start_time % 1000000)/10000 = 0\r\n",
                "\t\t\t\t\t\t\t\tTHEN '12'\r\n",
                "\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)))\r\n",
                "\t\t\t\t\t\t\t\t+ RTRIM(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))\r\n",
                "\t\t\t\t\t\t\t\t+ ' AM'\r\n",
                "\t\t\t\t\t\t\tWHEN (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 10\r\n",
                "\t\t\t\t\t\t\t\tTHEN CONVERT(CHAR(1),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t\t+ RTRIM(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))\r\n",
                "\t\t\t\t\t\t\t\t\t+ ' AM'\r\n",
                "\t\t\t\t\t\t\tWHEN (msdb.dbo.sysschedules.active_start_time % 1000000)/10000 < 12\r\n",
                "\t\t\t\t\t\t\t\tTHEN CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t\t+ RTRIM(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ' AM'\r\n",
                "\t\t\t\t\t\t\tWHEN (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 22\r\n",
                "\t\t\t\t\t\t\t\tTHEN CONVERT(CHAR(1),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t\t+ RTRIM(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) \r\n",
                "\t\t\t\t\t\t\t\t\t+ ' PM'\r\n",
                "\t\t\t\t\t\t\tELSE CONVERT(CHAR(2),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12)\r\n",
                "\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t+ RTRIM(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))\r\n",
                "\t\t\t\t\t\t\t\t+ ' PM'\r\n",
                "\t\t\t\t\t\tEND\r\n",
                "\t\t\t\t\t\t+ ' and '\r\n",
                "\t\t\t\t\t\t+ CASE -- Depends on time being integer to drop right-side digits\r\n",
                "\t\t\t\t\t\t\tWHEN (msdb.dbo.sysschedules.active_end_time % 1000000)/10000 = 0\r\n",
                "\t\t\t\t\t\t\t\tTHEN '12'\r\n",
                "\t\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)))\r\n",
                "\t\t\t\t\t\t\t\t\t+ RTRIM(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))\r\n",
                "\t\t\t\t\t\t\t\t\t+ ' AM'\r\n",
                "\t\t\t\t\t\t\tWHEN (msdb.dbo.sysschedules.active_end_time % 1000000)/10000< 10\r\n",
                "\t\t\t\t\t\t\t\tTHEN CONVERT(CHAR(1),(msdb.dbo.sysschedules.active_end_time % 1000000)/10000) \r\n",
                "\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t+ RTRIM(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))\r\n",
                "\t\t\t\t\t\t\t\t+ ' AM'\r\n",
                "\t\t\t\t\t\t\tWHEN (msdb.dbo.sysschedules.active_end_time % 1000000)/10000 < 12\r\n",
                "\t\t\t\t\t\t\t\tTHEN CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 1000000)/10000) \r\n",
                "\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t+ RTRIM(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))\r\n",
                "\t\t\t\t\t\t\t\t+ ' AM'\r\n",
                "\t\t\t\t\t\t\tWHEN (msdb.dbo.sysschedules.active_end_time % 1000000)/10000< 22\r\n",
                "\t\t\t\t\t\t\t\tTHEN CONVERT(CHAR(1),((msdb.dbo.sysschedules.active_end_time % 1000000)/10000) - 12)\r\n",
                "\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t+ RTRIM(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) \r\n",
                "\t\t\t\t\t\t\t\t+ ' PM'\r\n",
                "\t\t\t\t\t\t\tELSE CONVERT(CHAR(2),((msdb.dbo.sysschedules.active_end_time % 1000000)/10000) - 12)\r\n",
                "\t\t\t\t\t\t\t\t+ ':'  \r\n",
                "\t\t\t\t\t\t\t\t+ REPLICATE('0',2 - LEN(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) \r\n",
                "\t\t\t\t\t\t\t\t+ RTRIM(CONVERT(CHAR(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) \r\n",
                "\t\t\t\t\t\t\t\t+ ' PM'\r\n",
                "\t\t\t\t\t\tEND\r\n",
                "\t\t\t\tELSE ''\r\n",
                "\t\tEND\r\n",
                "\t\t, [Notify_Level_Eventlog] =\r\n",
                "\t\tCASE msdb.dbo.sysjobs.notify_level_eventlog\r\n",
                "\t\t\tWHEN 0 THEN 'Never'\r\n",
                "\t\t\tWHEN 1 THEN 'When the job succeeds'\r\n",
                "\t\t\tWHEN 2 THEN 'When the job fails'\r\n",
                "\t\t\tWHEN 3 THEN 'When the job completes'\r\n",
                "\t\tEND \r\n",
                "\t\t, [Notify_Level_Email] =\r\n",
                "\t\tCASE msdb.dbo.sysjobs.notify_level_email \r\n",
                "\t\t\tWHEN 0 THEN 'Never'\r\n",
                "\t\t\tWHEN 1 THEN 'When the job succeeds'\r\n",
                "\t\t\tWHEN 2 THEN 'When the job fails'\r\n",
                "\t\t\tWHEN 3 THEN 'When the job completes'\r\n",
                "\t\tEND \r\n",
                "\t\t, [OnFailureNotify] = CASE WHEN msdb.dbo.sysjobs.notify_level_email = 2 THEN msdb.dbo.sysoperators.name ELSE '' END\r\n",
                "\t\t, [OperatorEmail] = msdb.dbo.sysoperators.email_address\r\n",
                "\t\t, [Description] = msdb.dbo.sysjobs.[description]\r\n",
                "\t\t, [OutputFile] = CASE WHEN l.LogCount != 1 THEN 'Check Logs'\r\n",
                "\t\t\t\t\t\t\t\tELSE l.LogPath\r\n",
                "\t\t\t\t\t\t\tEND\r\n",
                "FROM\tmsdb.dbo.sysjobs\r\n",
                "\t\tLEFT OUTER JOIN msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id\r\n",
                "\t\tLEFT OUTER JOIN  msdb.dbo.sysschedules\r\n",
                "\t\t\tON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id\r\n",
                "\t\t\tAND msdb.dbo.sysschedules.[enabled] = ISNULL(@Enabled,msdb.dbo.sysschedules.[enabled])\r\n",
                "\t\tLEFT OUTER JOIN msdb.dbo.sysoperators ON msdb.dbo.sysjobs.notify_email_operator_id = msdb.dbo.sysoperators.id\r\n",
                "\t\tLEFT OUTER JOIN (\r\n",
                "\t\t\tSELECT  job_id\r\n",
                "\t\t\t\t\t,StepCount = COUNT(*)\r\n",
                "\t\t\t\t\t,LogCount = COUNT(DISTINCT output_file_name)\r\n",
                "\t\t\t\t\t,LogPath = MAX(output_file_name)\r\n",
                "\t\t\tFROM    msdb.dbo.sysjobsteps\r\n",
                "\t\t\t--WHERE\tsubsystem = 'TSQL'\r\n",
                "\t\t\tGROUP BY job_id\r\n",
                "\t\t) l ON l.job_id = sysjobs.job_id\r\n",
                "WHERE\tmsdb.dbo.sysjobs.date_modified >= ISNULL(@LastModDate,msdb.dbo.sysjobs.date_modified)\r\n",
                "ORDER BY msdb.dbo.sysjobs.name\r\n",
                "\t\t, msdb.dbo.sysschedules.freq_type\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "9694fbf4-e1da-44c6-abc7-ae325f616fe3",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get SQL Agent Job Steps"
            ],
            "metadata": {
                "azdata_cell_guid": "76191076-f671-4734-b137-743a080a230e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "SELECT @@SERVERNAME AS servername \r\n",
                "\t,j.name AS jobname \r\n",
                "\t,js.step_id \r\n",
                "\t,js.step_name \r\n",
                "\t,js.subsystem \r\n",
                "\t,js.command \r\n",
                "\t,js.on_success_action \r\n",
                "\t,js.on_success_step_id \r\n",
                "\t,js.on_fail_action \r\n",
                "\t,js.on_fail_step_id \r\n",
                "\t,js.database_name \r\n",
                "\t,js.database_user_name \r\n",
                "\t,js.retry_attempts \r\n",
                "\t,js.retry_interval \r\n",
                "\t,js.output_file_name\r\n",
                "\t,CASE WHEN j.start_step_id = js.step_id THEN 1 ELSE 0 END AS start_step_id\r\n",
                "FROM msdb..sysjobs j\r\n",
                "\tINNER JOIN msdb..sysjobsteps js ON j.job_id = js.job_id;\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "0509a5b5-33c1-4da2-93d4-3c92c6ab7378",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get SQL Agent Job Schedules"
            ],
            "metadata": {
                "azdata_cell_guid": "0b07c00f-4f99-4f1e-b52b-ca3500e31697"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "SELECT @@SERVERNAME AS servername \r\n",
                "\t,j.name AS jobname \r\n",
                "\t,s.name AS schedulename \r\n",
                "\t,s.enabled \r\n",
                "\t,s.freq_type \r\n",
                "\t,s.freq_interval \r\n",
                "\t,s.freq_subday_type \r\n",
                "\t,s.freq_subday_interval \r\n",
                "\t,s.freq_relative_interval \r\n",
                "\t,s.freq_recurrence_factor \r\n",
                "\t,s.active_start_date \r\n",
                "\t,s.active_start_time \r\n",
                "\t,s.active_end_time\r\n",
                "FROM msdb..sysjobs j\r\n",
                "\tINNER JOIN msdb..sysjobschedules js ON j.job_id = js.job_id\r\n",
                "\tINNER JOIN msdb..sysschedules s ON js.schedule_id = s.schedule_id;\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "60d041d7-fcf0-414a-9668-03af3492a2af",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "GET SQL Agent Job History"
            ],
            "metadata": {
                "azdata_cell_guid": "a9d04bab-caeb-4d98-a8a8-06668d4cbe72"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "DECLARE @ShowDisabledJobs BIT = 0\r\n",
                "       ,@ShowLastNumHours INT = NULL\r\n",
                "       ,@ExecDateCutOff   DATETIME;\r\n",
                "\r\n",
                "--SET @ShowLastNumHours = 24;  /* Pull just the last 24 hours */\r\n",
                "\r\n",
                "IF @ShowLastNumHours IS NOT NULL\r\n",
                "    SET @ExecDateCutOff = DATEADD(HOUR, -1 * @ShowLastNumHours, GETDATE());\r\n",
                "\r\n",
                "DECLARE @jobHistory TABLE (\r\n",
                "    job_id               UNIQUEIDENTIFIER\r\n",
                "   ,executions           INT\r\n",
                "   ,success              INT\r\n",
                "   ,cancel               INT\r\n",
                "   ,fail                 INT\r\n",
                "   ,retry                INT\r\n",
                "   ,last_execution_id    INT\r\n",
                "   ,last_duration        CHAR(8)\r\n",
                "   ,last_execution_start DATETIME\r\n",
                "   ,avg_duration         CHAR(8)\r\n",
                ");\r\n",
                "\r\n",
                "WITH lastExecution AS (\r\n",
                "    SELECT  job_id\r\n",
                "           ,MAX(instance_id) AS last_instance_id\r\n",
                "    FROM    msdb.dbo.sysjobhistory\r\n",
                "    WHERE   step_id = 0\r\n",
                "    GROUP BY job_id\r\n",
                ")\r\n",
                "\r\n",
                "INSERT INTO @jobHistory (\r\n",
                "    job_id\r\n",
                "    ,executions\r\n",
                "    ,success\r\n",
                "    ,cancel\r\n",
                "    ,fail\r\n",
                "    ,retry\r\n",
                "    ,last_execution_id\r\n",
                "    ,last_duration\r\n",
                "    ,last_execution_start\r\n",
                "    ,avg_duration\r\n",
                ")\r\n",
                "SELECT  sjh.job_id\r\n",
                "       ,SUM(CASE WHEN sjh.step_id = 0 THEN 1 ELSE 0 END)                        AS executions\r\n",
                "       ,SUM(CASE WHEN sjh.run_status = 1 AND sjh.step_id = 0 THEN 1 ELSE 0 END) AS success\r\n",
                "       ,SUM(CASE WHEN sjh.run_status = 3 AND sjh.step_id = 0 THEN 1 ELSE 0 END) AS cancel\r\n",
                "       ,SUM(CASE WHEN sjh.run_status = 0 AND sjh.step_id = 0 THEN 1 ELSE 0 END) AS fail\r\n",
                "       ,SUM(CASE WHEN sjh.run_status = 2 THEN 1 ELSE 0 END)                     AS retry\r\n",
                "       ,MAX(CASE WHEN sjh.step_id = 0 THEN sjh.instance_id ELSE NULL END)       AS last_execution_id\r\n",
                "       ,SUBSTRING(\r\n",
                "                     CAST(MAX(CASE WHEN le.job_id IS NOT NULL THEN sjh.run_duration ELSE NULL END) + 1000000 AS VARCHAR(7))\r\n",
                "                    ,2, 2\r\n",
                "                 ) + ':'\r\n",
                "        + SUBSTRING(\r\n",
                "                       CAST(MAX(CASE WHEN le.job_id IS NOT NULL THEN sjh.run_duration ELSE NULL END) + 1000000 AS VARCHAR(7))\r\n",
                "                      ,4, 2\r\n",
                "                   ) + ':'\r\n",
                "        + SUBSTRING(\r\n",
                "                       CAST(MAX(CASE WHEN le.job_id IS NOT NULL THEN sjh.run_duration ELSE NULL END) + 1000000 AS VARCHAR(7))\r\n",
                "                      ,6, 2\r\n",
                "                   )                                                            AS last_duration\r\n",
                "       ,MAX(   CASE WHEN le.last_instance_id IS NOT NULL THEN\r\n",
                "                        CONVERT(DATETIME, RTRIM(sjh.run_date))\r\n",
                "                        + ((sjh.run_time / 10000 * 3600) + ((sjh.run_time % 10000) / 100 * 60) + (sjh.run_time % 10000)\r\n",
                "                           % 100\r\n",
                "                          ) / (86399.9964)\r\n",
                "                    ELSE '1900-01-01'\r\n",
                "               END\r\n",
                "           )                                                                    AS last_execution_start\r\n",
                "       ,SUBSTRING(CAST(AVG(sjh.run_duration) + 1000000 AS VARCHAR(7)), 2, 2) + ':'\r\n",
                "        + SUBSTRING(CAST(AVG(sjh.run_duration) + 1000000 AS VARCHAR(7)), 4, 2) + ':'\r\n",
                "        + SUBSTRING(CAST(AVG(sjh.run_duration) + 1000000 AS VARCHAR(7)), 6, 2)  AS avg_duration\r\n",
                "FROM    msdb.dbo.sysjobhistory  AS sjh\r\n",
                "        LEFT JOIN lastExecution AS le\r\n",
                "            ON  sjh.job_id = le.job_id\r\n",
                "            AND sjh.instance_id = le.last_instance_id\r\n",
                "GROUP BY sjh.job_id;\r\n",
                " \r\n",
                "/* We need to parse the schedule into something we can understand */\r\n",
                "DECLARE @weekDay TABLE (mask INT, maskValue VARCHAR(32));\r\n",
                " \r\n",
                "INSERT INTO @weekDay\r\n",
                "SELECT  1,'Su'\r\n",
                "UNION ALL\r\n",
                "SELECT  2,'M'\r\n",
                "UNION ALL\r\n",
                "SELECT  4,'Tu'\r\n",
                "UNION ALL\r\n",
                "SELECT  8,'W'\r\n",
                "UNION ALL\r\n",
                "SELECT  16,'Th'\r\n",
                "UNION ALL\r\n",
                "SELECT  32,'F'\r\n",
                "UNION ALL\r\n",
                "SELECT  64,'Sa';\r\n",
                " \r\n",
                " \r\n",
                "/* Now let's get our schedule information */\r\n",
                "WITH myCTE AS (\r\n",
                "    SELECT  sched.name                                                                                     AS scheduleName\r\n",
                "           ,sched.schedule_id\r\n",
                "           ,jobsched.job_id\r\n",
                "           ,CASE WHEN sched.freq_type = 1 THEN 'Once'\r\n",
                "                 WHEN sched.freq_type = 4\r\n",
                "                 AND  sched.freq_interval = 1 THEN 'Daily'\r\n",
                "                 WHEN sched.freq_type = 4 THEN 'Every ' + CAST(sched.freq_interval AS VARCHAR(5)) + ' days'\r\n",
                "                 WHEN sched.freq_type = 8 THEN\r\n",
                "                     REPLACE(\r\n",
                "                                REPLACE(REPLACE((\r\n",
                "                                                    SELECT      x.maskValue\r\n",
                "                                                    FROM    @weekDay AS x\r\n",
                "                                                    WHERE sched.freq_interval & x.mask <> 0\r\n",
                "                                                    ORDER BY mask\r\n",
                "                                                    FOR XML RAW\r\n",
                "                                                ), '\"/><row maskValue=\"', ', '\r\n",
                "                                               ), '<row maskValue=\"', ''\r\n",
                "                                       ), '\"/>', ''\r\n",
                "                            )\r\n",
                "                     + CASE WHEN  sched.freq_recurrence_factor <> 0\r\n",
                "                            AND   sched.freq_recurrence_factor = 1 THEN '; wkly'\r\n",
                "                            WHEN sched.freq_recurrence_factor <> 0 THEN\r\n",
                "                                '; every ' + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' wks'\r\n",
                "                       END\r\n",
                "                 WHEN sched.freq_type = 16 THEN\r\n",
                "                     'On day ' + CAST(sched.freq_interval AS VARCHAR(10)) + ' of every '\r\n",
                "                     + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months'\r\n",
                "                 WHEN sched.freq_type = 32 THEN\r\n",
                "                     CASE WHEN sched.freq_relative_interval = 1 THEN '1st'\r\n",
                "                          WHEN sched.freq_relative_interval = 2 THEN '2nd'\r\n",
                "                          WHEN sched.freq_relative_interval = 4 THEN '3rd'\r\n",
                "                          WHEN sched.freq_relative_interval = 8 THEN '4th'\r\n",
                "                          WHEN sched.freq_relative_interval = 16 THEN 'Last'\r\n",
                "                     END + CASE WHEN sched.freq_interval = 1 THEN ' Su'\r\n",
                "                                WHEN sched.freq_interval = 2 THEN ' M'\r\n",
                "                                WHEN sched.freq_interval = 3 THEN ' Tu'\r\n",
                "                                WHEN sched.freq_interval = 4 THEN ' W'\r\n",
                "                                WHEN sched.freq_interval = 5 THEN ' Th'\r\n",
                "                                WHEN sched.freq_interval = 6 THEN ' F'\r\n",
                "                                WHEN sched.freq_interval = 7 THEN ' Sa'\r\n",
                "                                WHEN sched.freq_interval = 8 THEN ' Day'\r\n",
                "                                WHEN sched.freq_interval = 9 THEN ' Wkday'\r\n",
                "                                WHEN sched.freq_interval = 10 THEN ' Wkend'\r\n",
                "                           END\r\n",
                "                     + CASE WHEN sched.freq_recurrence_factor <> 0\r\n",
                "                            AND  sched.freq_recurrence_factor = 1 THEN '; monthly'\r\n",
                "                            WHEN sched.freq_recurrence_factor <> 0 THEN\r\n",
                "                                '; every ' + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months'\r\n",
                "                       END\r\n",
                "                 WHEN sched.freq_type = 64 THEN 'StartUp'\r\n",
                "                 WHEN sched.freq_type = 128 THEN 'Idle'\r\n",
                "            END                                                                                            AS frequency\r\n",
                "           ,ISNULL(\r\n",
                "                      'Every ' + CAST(sched.freq_subday_interval AS VARCHAR(10))\r\n",
                "                      + CASE WHEN sched.freq_subday_type = 2 THEN ' secs'\r\n",
                "                             WHEN sched.freq_subday_type = 4 THEN ' mins'\r\n",
                "                             WHEN sched.freq_subday_type = 8 THEN ' hrs'\r\n",
                "                        END, 'Once'\r\n",
                "                  )                                                                                        AS subFrequency\r\n",
                "           ,REPLICATE('0', 6 - LEN(sched.active_start_time)) + CAST(sched.active_start_time AS VARCHAR(6)) AS startTime\r\n",
                "           ,REPLICATE('0', 6 - LEN(sched.active_end_time)) + CAST(sched.active_end_time AS VARCHAR(6))     AS endTime\r\n",
                "           ,REPLICATE('0', 6 - LEN(jobsched.next_run_time)) + CAST(jobsched.next_run_time AS VARCHAR(6))   AS nextRunTime\r\n",
                "           ,CAST(jobsched.next_run_date AS CHAR(8))                                                        AS nextRunDate\r\n",
                "    FROM    msdb.dbo.sysschedules         AS sched\r\n",
                "            JOIN msdb.dbo.sysjobschedules AS jobsched\r\n",
                "                ON sched.schedule_id = jobsched.schedule_id\r\n",
                "    WHERE   sched.enabled = 1\r\n",
                ")\r\n",
                "\r\n",
                "/* Finally, let's look at our actual jobs and tie it all together */\r\n",
                "SELECT  CONVERT(NVARCHAR(128), SERVERPROPERTY('Servername'))                                               AS serverName\r\n",
                "       ,job.job_id                                                                                         AS jobID\r\n",
                "       ,job.name                                                                                           AS jobName\r\n",
                "       ,CASE WHEN job.enabled = 1 THEN 'Enabled'\r\n",
                "             ELSE 'Disabled'\r\n",
                "        END                                                                                                AS jobStatus\r\n",
                "       ,COALESCE(sched.scheduleName, '(unscheduled)')                                                      AS scheduleName\r\n",
                "       ,COALESCE(sched.frequency, '')                                                                      AS frequency\r\n",
                "       ,COALESCE(sched.subFrequency, '')                                                                   AS subFrequency\r\n",
                "       ,COALESCE(\r\n",
                "                    SUBSTRING(sched.startTime, 1, 2) + ':' + SUBSTRING(sched.startTime, 3, 2) + ' - '\r\n",
                "                    + SUBSTRING(sched.endTime, 1, 2) + ':' + SUBSTRING(sched.endTime, 3, 2), ''\r\n",
                "                )                                                                                          AS scheduleTime -- HH:MM\r\n",
                "       ,COALESCE(\r\n",
                "                    SUBSTRING(sched.nextRunDate, 1, 4) + '/' + SUBSTRING(sched.nextRunDate, 5, 2) + '/'\r\n",
                "                    + SUBSTRING(sched.nextRunDate, 7, 2) + ' ' + SUBSTRING(sched.nextRunTime, 1, 2) + ':'\r\n",
                "                    + SUBSTRING(sched.nextRunTime, 3, 2), ''\r\n",
                "                )                                                                                          AS nextRunDate\r\n",
                "       /* Note: the sysjobschedules table refreshes every 20 min, so nextRunDate may be out of date */\r\n",
                "       ,COALESCE(jh.executions, 0)                                                                         AS executions\r\n",
                "       ,COALESCE(jh.success, 0)                                                                            AS success\r\n",
                "       ,COALESCE(jh.cancel, 0)                                                                             AS cancel\r\n",
                "       ,COALESCE(jh.fail, 0)                                                                               AS fail\r\n",
                "       ,COALESCE(jh.retry, 0)                                                                              AS retry\r\n",
                "       ,COALESCE(jh.last_execution_id, 0)                                                                  AS lastExecutionID\r\n",
                "       ,jh.last_execution_start                                                                            AS lastExecutionStart\r\n",
                "       ,COALESCE(jh.last_duration, '00:00:01')                                                             AS lastDuration\r\n",
                "       ,COALESCE(jh.avg_duration, '00:00:01')                                                              AS avgDuration\r\n",
                "       ,'EXECUTE msdb.dbo.sp_update_job @job_id = ''' + CAST(job.job_id AS CHAR(36)) + ''', @enabled = 0;' AS disableSQLScript\r\n",
                "FROM    msdb.dbo.sysjobs      AS job\r\n",
                "        LEFT JOIN myCTE       AS sched\r\n",
                "            ON job.job_id = sched.job_id\r\n",
                "        LEFT JOIN @jobHistory AS jh\r\n",
                "            ON job.job_id = jh.job_id\r\n",
                "WHERE   (\r\n",
                "            CASE WHEN @ShowDisabledJobs = 0 THEN 1\r\n",
                "            END = job.enabled\r\n",
                "      OR    @ShowDisabledJobs = 1\r\n",
                "        )\r\n",
                "AND     (jh.last_execution_start >= @ExecDateCutOff OR  @ExecDateCutOff IS NULL)\r\n",
                "ORDER BY job.name\r\n",
                "        ,jh.last_execution_start;"
            ],
            "metadata": {
                "azdata_cell_guid": "2629fd73-d562-4ffb-8d29-1de0513c647c",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get Availability Group Detail"
            ],
            "metadata": {
                "azdata_cell_guid": "194133dd-3b2c-44b9-ade5-3d23c51d2fe0"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON;\r\n",
                "\r\n",
                "-- RUN ON PRIMARY --\r\n",
                "\r\n",
                "DECLARE @IsHadrEnabled TINYINT = CONVERT(TINYINT,SERVERPROPERTY ('IsHadrEnabled'))\r\n",
                "\t\t,@ServerName NVARCHAR(256) = @@SERVERNAME \r\n",
                "\t\t,@RoleDesc NVARCHAR(60) = 'PRIMARY'\r\n",
                "\t\t,@AG_SQL NVARCHAR(MAX)\r\n",
                "\t\t,@Version VARCHAR(50) = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128)),2);\r\n",
                "\r\n",
                "DECLARE @AGs TABLE (\r\n",
                "\tdomain VARCHAR(128) NOT NULL\r\n",
                "\t,availabilitygroupname VARCHAR(128) NOT NULL\r\n",
                "\t,replicaservername VARCHAR(128) NOT NULL\r\n",
                "\t,endpoint_url VARCHAR(128) NULL\r\n",
                "\t,availability_mode TINYINT NOT NULL\r\n",
                "\t,failover_mode TINYINT NOT NULL\r\n",
                "\t,session_timeout INT NOT NULL\r\n",
                "\t,primary_role_allow_connections TINYINT NOT NULL\r\n",
                "\t,secondary_role_allow_connections TINYINT NOT NULL\r\n",
                "\t,backup_priority INT NOT NULL\r\n",
                "\t,read_only_routing_url VARCHAR(256) NULL\r\n",
                "\t,seeding_mode TINYINT NULL\r\n",
                "\t,read_only_routing_lists VARCHAR(1000) NULL\r\n",
                ");\r\n",
                "\r\n",
                "IF (@IsHadrEnabled = 1)\r\n",
                "AND EXISTS (\r\n",
                "\tSELECT 1\r\n",
                "\tFROM sys.dm_hadr_availability_replica_states AS a\r\n",
                "\t\tJOIN sys.availability_replicas AS b\r\n",
                "\tON b.replica_id = a.replica_id\r\n",
                "\tWHERE b.replica_server_name = @ServerName\r\n",
                "\tAND\ta.role_desc = @RoleDesc\r\n",
                ")\r\n",
                "BEGIN\r\n",
                "\tSELECT *\r\n",
                "\tINTO #availability_read_only_routing_lists\r\n",
                "\tFROM sys.availability_read_only_routing_lists;\r\n",
                "\r\n",
                "\tSELECT *\r\n",
                "\tINTO #availability_replicas\r\n",
                "\tFROM sys.availability_replicas;\r\n",
                "\t\r\n",
                "\tSELECT group_id, name\r\n",
                "\tINTO #availability_groups\r\n",
                "\tFROM sys.availability_groups;\r\n",
                "\r\n",
                "\tSET @AG_SQL = '\r\n",
                "\tWITH read_only_routing_lists AS (\r\n",
                "\t\r\n",
                "\t\tSELECT\tr.replica_id\r\n",
                "\t\t\t\t,STUFF((\r\n",
                "\t\t\t\t\tSELECT '','' + ror.replica_server_name\r\n",
                "\t\t\t\t\tFROM #availability_read_only_routing_lists l\r\n",
                "\t\t\t\t\t\tJOIN #availability_replicas ror ON l.read_only_replica_id = ror.replica_id\r\n",
                "\t\t\t\t\tWHERE r.replica_id = l.replica_id\r\n",
                "\t\t\t\t\tORDER BY l.routing_priority\r\n",
                "\t\t\t\t\tFOR XML PATH(''''),TYPE).value(''.'',''VARCHAR(MAX)''),1,1,'''') AS read_only_replicas\r\n",
                "\t\tFROM\t#availability_replicas r\r\n",
                "\t)\r\n",
                "\r\n",
                "SELECT\tDEFAULT_DOMAIN() as domain\r\n",
                "\t\t,ag.name\r\n",
                "\t\t,r.replica_server_name\r\n",
                "\t\t,r.endpoint_url\r\n",
                "\t\t,r.availability_mode\r\n",
                "\t\t,r.failover_mode\r\n",
                "\t\t,r.session_timeout\r\n",
                "\t\t,r.primary_role_allow_connections\r\n",
                "\t\t,r.secondary_role_allow_connections\r\n",
                "\t\t,r.backup_priority\r\n",
                "\t\t,r.read_only_routing_url'\r\n",
                "\t\t+ CASE WHEN @Version > 11 THEN '\t,r.seeding_mode' ELSE '\t,NULL' END + '\r\n",
                "\t\t,ro.read_only_replicas\r\n",
                "FROM\t#availability_groups ag\r\n",
                "\t\tJOIN #availability_replicas r on ag.group_id = r.group_id\r\n",
                "\t\tLEFT JOIN read_only_routing_lists ro ON r.replica_id = ro.replica_id'\r\n",
                "\r\n",
                "\tINSERT @AGs (\r\n",
                "\t\tdomain\r\n",
                "\t\t,availabilitygroupname\r\n",
                "\t\t,replicaservername\r\n",
                "\t\t,endpoint_url\r\n",
                "\t\t,availability_mode\r\n",
                "\t\t,failover_mode\r\n",
                "\t\t,session_timeout\r\n",
                "\t\t,primary_role_allow_connections\r\n",
                "\t\t,secondary_role_allow_connections\r\n",
                "\t\t,backup_priority\r\n",
                "\t\t,read_only_routing_url\r\n",
                "\t\t,seeding_mode\r\n",
                "\t\t,read_only_routing_lists\r\n",
                "\t)\r\n",
                "\tEXEC sp_executesql @AG_SQL;\r\n",
                "END;\r\n",
                "\r\n",
                "SELECT * FROM @AGs;\r\n",
                "\r\n",
                "IF OBJECT_ID('tempdb..#availability_read_only_routing_lists') IS NOT NULL\r\n",
                " DROP TABLE #availability_read_only_routing_lists;\r\n",
                "\r\n",
                "IF OBJECT_ID('tempdb..#availability_replicas') IS NOT NULL\r\n",
                " DROP TABLE #availability_replicas;\r\n",
                " \r\n",
                "IF OBJECT_ID('tempdb..#availability_groups') IS NOT NULL\r\n",
                " DROP TABLE #availability_groups;\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "7f887ed8-eeaf-4521-8792-192876f0a481",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Get Availability Group Replica Detail"
            ],
            "metadata": {
                "azdata_cell_guid": "ff95e4ca-fa22-478d-9540-66d4d907be89"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON; \r\n",
                "\r\n",
                "-- RUN ON PRIMARY --\r\n",
                "\r\n",
                "DECLARE @IsHadrEnabled TINYINT = CONVERT(TINYINT,SERVERPROPERTY ('IsHadrEnabled'))\r\n",
                "\t\t,@ServerName NVARCHAR(256) = @@SERVERNAME \r\n",
                "\t\t,@RoleDesc NVARCHAR(60) = 'PRIMARY'\r\n",
                "\t\t,@AG_SQL NVARCHAR(MAX)\r\n",
                "\t\t,@Version VARCHAR(50) = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128)),2);\r\n",
                "\r\n",
                "DECLARE @AGs TABLE (\r\n",
                "\tdomain VARCHAR(128) NOT NULL\r\n",
                "\t,primaryreplicaservername VARCHAR(128) NOT NULL\r\n",
                "\t,availabilitygroupname VARCHAR(128) NOT NULL\r\n",
                "\t,failure_condition_level INT NOT NULL\r\n",
                "\t,health_check_timeout INT NOT NULL\r\n",
                "\t,automated_backup_preference TINYINT NOT NULL\r\n",
                "\t,listener_name VARCHAR(63) NULL\r\n",
                "\t,listener_port INT NULL\r\n",
                "\t,listener_ip VARCHAR(200) NULL\r\n",
                "\t,dtc_support BIT NULL\r\n",
                "\t,db_failover BIT NULL\r\n",
                ");\r\n",
                "\r\n",
                "IF (@IsHadrEnabled = 1)\r\n",
                "AND EXISTS (\r\n",
                "\tSELECT 1\r\n",
                "\tFROM sys.dm_hadr_availability_replica_states AS a\r\n",
                "\t\tJOIN sys.availability_replicas AS b\r\n",
                "\tON b.replica_id = a.replica_id\r\n",
                "\tWHERE b.replica_server_name = @ServerName\r\n",
                "\tAND\ta.role_desc = @RoleDesc\r\n",
                ")\r\n",
                "BEGIN\r\n",
                "\tSELECT *\r\n",
                "\tINTO #availability_groups\r\n",
                "\tFROM sys.availability_groups;\r\n",
                "\r\n",
                "\tSELECT group_id, dns_name, port, ip_configuration_string_from_cluster\r\n",
                "\tINTO #availability_group_listeners\r\n",
                "\tFROM sys.availability_group_listeners;\r\n",
                "\r\n",
                "\tSET @AG_SQL = '\r\n",
                "\tSELECT\tDEFAULT_DOMAIN() as domain\r\n",
                "\t\t\t,''' + @ServerName + ''' as primaryreplicaservername\r\n",
                "\t\t\t,ag.name\r\n",
                "\t\t\t,ag.failure_condition_level\r\n",
                "\t\t\t,ag.health_check_timeout\r\n",
                "\t\t\t,ag.automated_backup_preference\r\n",
                "\t\t\t,l.dns_name\r\n",
                "\t\t\t,l.port\r\n",
                "\t\t\t,l.ip_configuration_string_from_cluster'\r\n",
                "\t+ CASE WHEN @Version > 11 THEN '\t,ag.dtc_support' ELSE '\t,NULL' END\r\n",
                "\t+ CASE WHEN @Version > 11 THEN '\t,ag.db_failover' ELSE '\t,NULL' END + '\r\n",
                "\tFROM\t#availability_groups ag\r\n",
                "\t\t\tJOIN #availability_group_listeners l ON ag.group_id = l.group_id'\r\n",
                "\r\n",
                "\tINSERT @AGs (\r\n",
                "\t\tdomain\r\n",
                "\t\t,primaryreplicaservername\r\n",
                "\t\t,availabilitygroupname\r\n",
                "\t\t,failure_condition_level\r\n",
                "\t\t,health_check_timeout\r\n",
                "\t\t,automated_backup_preference\r\n",
                "\t\t,listener_name\r\n",
                "\t\t,listener_port\r\n",
                "\t\t,listener_ip\r\n",
                "\t\t,dtc_support\r\n",
                "\t\t,db_failover\r\n",
                "\t)\r\n",
                "\tEXEC sp_executesql @AG_SQL;\r\n",
                "END;\r\n",
                "\r\n",
                "SELECT * FROM @AGs;\r\n",
                "\r\n",
                "IF OBJECT_ID('tempdb..#availability_groups') IS NOT NULL\r\n",
                " DROP TABLE #availability_groups;\r\n",
                "\r\n",
                "IF OBJECT_ID('tempdb..#availability_group_listeners') IS NOT NULL\r\n",
                " DROP TABLE #availability_group_listeners;\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f38da4ac-fd5e-467b-902c-926928ccc7d7",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}