Instance Stacking Custom Report

[aachaemenes]

DBADASH dont show if a server has multiple instances. This would be really beneficial when that is the case.

[EsQueEl-Fella]

Hi,

DBADash does show the instances. You just need to add EACH Instance to the DBADash separately.

[aachaemenes]

I meant under configuration. To list all the instances on that box.

…

On Mon, Feb 10, 2025, 3:54 AM EsQueEl-Fella ***@***.***> wrote: Hi, DBADash does show the instances. You just need to add EACH Instance to the DBADash separately. — Reply to this email directly, view it on GitHub <#1217 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BGN5RX2OVD4UFTO3MGK5APT2PCHPVAVCNFSM6AAAAABWW5LIACVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNBXG43DSMBSGU> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[DavidWiseman]

This can be implemented as a custom report.

/*
	Instance Stacking
	

	Custom report for DBA Dash.
	http://dbadash.com
	Generated: 2025-02-10 21:43:08 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER   PROC [UserReport].[InstanceStacking](
	@InstanceIDs IDs READONLY,
	@InstanceID INT
)
AS
CREATE TABLE #Hosts(
	Host NVARCHAR(128) PRIMARY KEY
)
INSERT INTO #Hosts(Host)
SELECT DISTINCT ComputerNamePhysicalNetBIOS 
FROM dbo.Instances I
WHERE EXISTS( SELECT 1 
			FROM @InstanceIDs T 
			WHERE T.ID = I.InstanceID
			)
AND (I.InstanceID = @InstanceID OR @InstanceID IS NULL)
AND ComputerNamePhysicalNetBIOS IS NOT NULL
AND I.IsActive=1

SELECT H.Host,
	STUFF((SELECT ', ' + I2.InstanceDisplayName
	FROM dbo.Instances I2
	WHERE I2.ComputerNamePhysicalNetBIOS = H.Host
	AND I2.IsActive =1
	FOR XML PATH(''),TYPE
	).value('.','NVARCHAR(MAX)'),1,2,'') AS Instances,
	COUNT(*) AS InstanceCount
FROM #Hosts H 
JOIN dbo.Instances I1 ON H.Host = I1.ComputerNamePhysicalNetBIOS
WHERE I1.IsActive=1
GROUP BY H.Host
HAVING COUNT(*)>1
ORDER BY InstanceCount DESC


GO

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

INSERT INTO dbo.CustomReport(SchemaName,ProcedureName,MetaData)
VALUES('UserReport','InstanceStacking','{
  "ReportVisibilityRole": "public",
  "ReportName": "Instance Stacking",
  "TriggerCollectionTypes": [],
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {
        "InstanceCount": "Instance Count"
      },
      "CellFormatString": {},
      "CellNullValue": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [],
      "ResultName": "Result1",
      "LinkColumns": {},
      "CellHighlightingRules": {}
    }
  }
}')

[DavidWiseman]

Alternative option:

/*
	Stacked Instances
	

	Custom report for DBA Dash.
	http://dbadash.com
	Generated: 2025-09-15 15:55:56 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER   PROC [UserReport].[StackedInstances](
	@InstanceIDs IDs READONLY
)
AS
WITH Stacked AS (
	SELECT	I.InstanceID,
			I.ComputerNamePhysicalNetBIOS AS ComputerName,
			I.ConnectionID,
			I.InstanceDisplayName,
			COUNT(*) OVER(PARTITION BY I.ComputerNamePhysicalNetBIOS) AS StackCount
	FROM dbo.Instances I
	WHERE EXISTS(SELECT 1 
				FROM dbo.Instances I2
				JOIN @InstanceIDS T ON I2.InstanceID = T.ID
				WHERE I2.ComputerNamePhysicalNetBIOS = I.ComputerNamePhysicalNetBIOS
				AND I2.IsActive=1
				)
	AND I.IsActive = 1
)
SELECT  InstanceID,
		ComputerName,
		ConnectionID,
		InstanceDisplayName,
		StackCount
FROM Stacked 
WHERE StackCount>1
ORDER BY ComputerName

GO

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

INSERT INTO dbo.CustomReport(SchemaName,ProcedureName,MetaData)
VALUES('UserReport','StackedInstances','{
  "SchemaName": "UserReport",
  "ReportVisibilityRole": "public",
  "ReportName": "Stacked Instances",
  "TriggerCollectionTypes": [],
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {
        "ComputerName": "Computer Name",
        "ConnectionID": "Connection ID",
        "InstanceDisplayName": "Instance",
        "StackCount": "Stack Count"
      },
      "CellFormatString": {},
      "CellNullValue": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [
        {
          "Key": "InstanceID",
          "Value": {
            "Width": 107
          }
        },
        {
          "Key": "ComputerName",
          "Value": {
            "Width": 201,
            "Visible": true,
            "DisplayIndex": 1
          }
        },
        {
          "Key": "ConnectionID",
          "Value": {
            "Width": 242,
            "Visible": true,
            "DisplayIndex": 2
          }
        },
        {
          "Key": "InstanceDisplayName",
          "Value": {
            "Width": 253,
            "Visible": true,
            "DisplayIndex": 3
          }
        },
        {
          "Key": "StackCount",
          "Value": {
            "Width": 77,
            "Visible": true,
            "DisplayIndex": 4
          }
        }
      ],
      "ResultName": "Result1",
      "LinkColumns": {
        "InstanceDisplayName": {
          "$type": "NavigateTreeLinkColumnInfo",
          "InstanceColumn": "InstanceID",
          "DatabaseColumn": ""
        }
      },
      "CellHighlightingRules": {}
    }
  }
}')