sp_Blitz custom collection and reporting

[DavidWiseman]

Introduction

sp_Blitz a popular community health check script for your SQL instances. There is some overlap with checks provided by DBA Dash, but sp_Blitz has additional checks. sp_Blitz provides it’s information in a single report with links that provide advice on each check. DBA Dash is more suited to support your daily DBA checks and sp_Blitz is ideal to run when setting up a new server, working on a SQL instance for the first time and to run occasionally for SQL instances.

Prerequisites

• Configure a custom collection for sp_Blitz. The default options will work. Set retention to 0 if you just want to keep the last sp_Blitz captured for each instance. Or you can maintain previous snapshots and have DBA Dash automatically clear out old ones after a specified number of days.

• The scripts assume the collection name is sp_Blitz

See here for more info

Security

sp_Blitz requires sysadmin permissions. If you want to run the DBA Dash service with minimal permissions you will need to grant access via certificates.

CREATE CERTIFICATE sp_Blitz_cert
ENCRYPTION BY PASSWORD = '<Password Here>'
WITH SUBJECT = 'Certificate for sp_Blitz',
START_DATE = '20130711', EXPIRY_DATE = '21000101';
GO
CREATE LOGIN sp_Blitz_login FROM CERTIFICATE sp_Blitz_cert;
GO
CREATE USER sp_Blitz_login FROM CERTIFICATE sp_Blitz_cert;
GO
GRANT EXECUTE ON dbo.sp_Blitz TO sp_Blitz_login;
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [sp_Blitz_login]
GO
GO
ADD SIGNATURE TO sp_Blitz BY CERTIFICATE sp_Blitz_cert
WITH PASSWORD = '<Password Here>';
GO
GRANT EXECUTE ON dbo.sp_Blitz TO [<YourDBADashServiceUser>];
GO

See How to Grant Permissions to Non-DBAs for more info.

Custom report

The default reports are OK, but we can create a summary report with drill down and customize the detail report with links.

Remove the existing reports:

IF EXISTS (SELECT * FROM sys.procedures WHERE Name = N'sp_Blitz Example' AND schema_id = SCHEMA_ID('UserReport'))
BEGIN
    DROP PROC [UserReport].[sp_Blitz Example]
END
IF EXISTS (SELECT * FROM sys.procedures WHERE Name = N'sp_Blitz Snapshots' AND schema_id = SCHEMA_ID('UserReport'))
BEGIN
    DROP PROC [UserReport].[sp_Blitz Snapshots]
END

Create the new reports:

/*
	sp_Blitz Detail
	

	Custom report for DBA Dash.
	http://dbadash.com
	Generated: 2023-11-16 21:16:16 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER     PROC [UserReport].[sp_Blitz Detail]
(
	@InstanceIDs IDs READONLY,
	@CheckID INT=NULL,
	@FindingsGroup VARCHAR(50)=NULL,
	@InstanceID INT=NULL,
	@Finding VARCHAR(200)=NULL,
	@SnapshotDate DATETIME2=NULL
)
AS
WITH T AS (
	SELECT	I.InstanceID,
			I.InstanceDisplayName AS Instance,
			UD.SnapshotDate,
			UD.[Priority],
			UD.[FindingsGroup],
			UD.[Finding],
			UD.[DatabaseName],
			UD.[URL],
			UD.[Details],
			UD.[QueryPlan],
			UD.[QueryPlanFiltered],
			UD.[CheckID],
			/* Get the last snapshot for each instance (RNK=1) */
			RANK() OVER(PARTITION BY UD.InstanceID ORDER BY UD.SnapshotDate DESC) RNK
	FROM UserData.sp_Blitz UD
	JOIN dbo.Instances I ON I.InstanceID = UD.InstanceID
	WHERE EXISTS(SELECT 1
				FROM @InstanceIDs T
				WHERE T.ID = UD.InstanceID
				)
	AND (UD.InstanceID = @InstanceID OR @InstanceID IS NULL)
	AND (UD.SnapshotDate = @SnapshotDate OR @SnapshotDate IS NULL)
)
SELECT T.InstanceID,
	T.Instance,
	T.SnapshotDate,
	T.[Priority],
	T.[FindingsGroup],
	T.[Finding],
	T.[DatabaseName],
	T.[URL],
	T.[Details],
	T.[QueryPlan],
	T.[QueryPlanFiltered],
	T.[CheckID]
FROM T
WHERE T.RNK = 1
AND (T.CheckID = @CheckID OR @CheckID IS NULL)
AND (T.FindingsGroup = @FindingsGroup OR @FindingsGroup IS NULL)
AND (T.Finding = @Finding OR @Finding IS NULL)
OPTION(RECOMPILE)


GO

/* Report customizations in GUI */
DELETE dbo.CustomReport
WHERE SchemaName = 'UserReport'
AND ProcedureName = 'sp_Blitz Detail'

INSERT INTO dbo.CustomReport(SchemaName,ProcedureName,MetaData)
VALUES('UserReport','sp_Blitz Detail','{
  "ReportName": "sp_Blitz Detail",
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {},
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [
        {
          "Key": "InstanceID",
          "Value": {
            "Width": 107,
            "DisplayIndex": -1
          }
        },
        {
          "Key": "Instance",
          "Value": {
            "Width": 198,
            "Visible": true,
            "DisplayIndex": 1
          }
        },
        {
          "Key": "SnapshotDate",
          "Value": {
            "Width": 144,
            "Visible": true,
            "DisplayIndex": 2
          }
        },
        {
          "Key": "Priority",
          "Value": {
            "Width": 85,
            "Visible": true,
            "DisplayIndex": 3
          }
        },
        {
          "Key": "FindingsGroup",
          "Value": {
            "Width": 211,
            "Visible": true,
            "DisplayIndex": 4
          }
        },
        {
          "Key": "Finding",
          "Value": {
            "Width": 307,
            "Visible": true,
            "DisplayIndex": 5
          }
        },
        {
          "Key": "DatabaseName",
          "Value": {
            "Width": 166,
            "Visible": true,
            "DisplayIndex": 6
          }
        },
        {
          "Key": "URL",
          "Value": {
            "Width": 336,
            "Visible": true,
            "DisplayIndex": 7
          }
        },
        {
          "Key": "Details",
          "Value": {
            "Width": 400,
            "Visible": true,
            "DisplayIndex": 8
          }
        },
        {
          "Key": "QueryPlan",
          "Value": {
            "Width": 105,
            "Visible": true,
            "DisplayIndex": 9
          }
        },
        {
          "Key": "QueryPlanFiltered",
          "Value": {
            "Width": 155,
            "Visible": true,
            "DisplayIndex": 10
          }
        },
        {
          "Key": "CheckID",
          "Value": {
            "Width": 92,
            "Visible": true,
            "DisplayIndex": 11
          }
        }
      ],
      "ResultName": "Result0",
      "LinkColumns": {
        "URL": {
          "$type": "UrlLinkColumnInfo",
          "TargetColumn": "URL"
        },
        "Details": {
          "$type": "TextLinkColumnInfo",
          "TargetColumn": "Details",
          "TextHandling": "None"
        },
        "FindingsGroup": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_Blitz Detail",
          "ColumnToParameterMap": {
            "@FindingsGroup": "FindingsGroup"
          }
        },
        "CheckID": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_Blitz Detail",
          "ColumnToParameterMap": {
            "@CheckID": "CheckID"
          }
        },
        "Finding": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_Blitz Detail",
          "ColumnToParameterMap": {
            "@CheckID": "CheckID"
          }
        }
      }
    }
  }
}')
GO
/*
	sp_Blitz
	sp_Blitz Summary report

	Custom report for DBA Dash.
	http://dbadash.com
	Generated: 2023-11-16 21:16:27 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER         PROC [UserReport].[sp_Blitz](
	@InstanceIDs IDs READONLY,
	@CheckID INT=NULL
)
AS
WITH LatestSnapshot AS (
	SELECT	UD.Priority,
			UD.FindingsGroup,
			UD.Finding,	
			UD.URL,	
			UD.CheckID,
			UD.InstanceID,
			/* Get the last snapshot for each instance (RNK=1) */
			RANK() OVER(PARTITION BY UD.InstanceID ORDER BY UD.SnapshotDate DESC) RNK
	FROM UserData.sp_Blitz UD
	WHERE EXISTS(SELECT 1
				FROM @InstanceIDs T
				WHERE T.ID = UD.InstanceID
				)
	AND CheckID NOT IN(-1,156) /* Exclude RunDate & SQL Server First Responder Kit message */
)
SELECT	Priority,
		FindingsGroup,
		Finding,
		CheckID,
		URL,
		COUNT(*) Total,
		COUNT(DISTINCT InstanceID) AS AffectedInstances
FROM LatestSnapshot
WHERE RNK=1
GROUP BY	Priority,
			FindingsGroup,
			Finding,
			CheckID,
			URL
ORDER BY Priority,CheckID


GO

/* Report customizations in GUI */
DELETE dbo.CustomReport
WHERE SchemaName = 'UserReport'
AND ProcedureName = 'sp_Blitz'

INSERT INTO dbo.CustomReport(SchemaName,ProcedureName,MetaData)
VALUES('UserReport','sp_Blitz','{
  "ReportName": "sp_Blitz",
  "Description": "sp_Blitz Summary report",
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {
        "FindingsGroup": "Findings Group",
        "CheckID": "Check ID",
        "AffectedInstances": "Affected Instances"
      },
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [
        {
          "Key": "Priority",
          "Value": {
            "Width": 56,
            "Visible": true,
            "DisplayIndex": -1
          }
        },
        {
          "Key": "FindingsGroup",
          "Value": {
            "Width": 237,
            "Visible": true,
            "DisplayIndex": 1
          }
        },
        {
          "Key": "Finding",
          "Value": {
            "Width": 350,
            "Visible": true,
            "DisplayIndex": 2
          }
        },
        {
          "Key": "CheckID",
          "Value": {
            "Width": 64,
            "Visible": true,
            "DisplayIndex": 4
          }
        },
        {
          "Key": "URL",
          "Value": {
            "Width": 426,
            "Visible": true,
            "DisplayIndex": 3
          }
        },
        {
          "Key": "Total",
          "Value": {
            "Width": 67,
            "Visible": true,
            "DisplayIndex": 5
          }
        },
        {
          "Key": "AffectedInstances",
          "Value": {
            "Width": 78,
            "Visible": true,
            "DisplayIndex": 6
          }
        }
      ],
      "ResultName": "Summary",
      "LinkColumns": {
        "CheckID": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_Blitz Detail",
          "ColumnToParameterMap": {
            "@CheckID": "CheckID"
          }
        },
        "FindingsGroup": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_Blitz Detail",
          "ColumnToParameterMap": {
            "@FindingsGroup": "FindingsGroup"
          }
        },
        "Total": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_Blitz Detail",
          "ColumnToParameterMap": {
            "@CheckID": "CheckID",
            "@FindingsGroup": "FindingsGroup",
            "@Finding": "Finding"
          }
        },
        "Finding": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_Blitz Detail",
          "ColumnToParameterMap": {
            "@Finding": "Finding"
          }
        },
        "AffectedInstances": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_Blitz Detail",
          "ColumnToParameterMap": {
            "@CheckID": "CheckID"
          }
        },
        "URL": {
          "$type": "UrlLinkColumnInfo",
          "TargetColumn": "URL"
        }
      }
    },
    "1": {
      "ColumnAlias": {},
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [],
      "ResultName": "Detail",
      "LinkColumns": {}
    }
  }
}')
GO
/*
	sp_Blitz Snapshots
	

	Custom report for DBA Dash.
	http://dbadash.com
	Generated: 2023-11-16 21:16:46 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	Custom report example. Lists available snapshots within the specified time period.
	Report is available at instance level.
*/
CREATE OR ALTER PROC [UserReport].[sp_Blitz Snapshots]
(
	/* InstanceID associated with the current instance in DBA Dash (dbo.Instances table) */
	@InstanceID INT,
	/* Show snapshots between @FromDate and @ToDate (UTC). DBA Dash will supply these based on selected date range. */
	@FromDate DATETIME2,
	@ToDate DATETIME2
)
AS
SELECT	UD.SnapshotDate,
			COUNT(*) AS [Row Count]
FROM UserData.[sp_Blitz] UD
WHERE UD.InstanceID = @InstanceID
AND UD.SnapshotDate >= @FromDate
AND UD.SnapshotDate < @ToDate
GROUP BY UD.SnapshotDate
ORDER BY UD.SnapshotDate DESC

GO

/* Report customizations in GUI */
DELETE dbo.CustomReport
WHERE SchemaName = 'UserReport'
AND ProcedureName = 'sp_Blitz Snapshots'

INSERT INTO dbo.CustomReport(SchemaName,ProcedureName,MetaData)
VALUES('UserReport','sp_Blitz Snapshots','{
  "ReportName": "sp_Blitz Snapshots",
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {},
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [],
      "ResultName": "Result0",
      "LinkColumns": {
        "SnapshotDate": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_Blitz Detail",
          "ColumnToParameterMap": {
            "@SnapshotDate": "SnapshotDate"
          }
        }
      }
    }
  }
}')