CREATE PROCEDURE [dbo].[p_get_all_clusters]
AS
BEGIN
    SET NOCOUNT ON
   
    DECLARE @cmd varchar(1000), @TotalCount int, @TempRow nvarchar(300)
    DECLARE @ClusterName nvarchar(100), @InstanceName nvarchar(150), @Envir nvarchar(20)
    DECLARE @NODE nvarchar(100), @GROUP nvarchar(100), @STATUS nvarchar(100)

    – Temp tables
    CREATE TABLE #tmp(item nvarchar(1000))
    CREATE TABLE #allClusters
    (ID int IDENTITY, ClusterName nvarchar(100))
    CREATE TABLE #clusterNodes
    (ID int IDENTITY, ClusterNode nvarchar(100))
    CREATE TABLE #clusterGroups
    (ID int IDENTITY, ClusterGroup nvarchar(100))

    – Clear out old data for the refresh
    TRUNCATE TABLE clusters
    TRUNCATE TABLE clusternodes
    TRUNCATE TABLE clustergroups

    – Give us all clusters in our environment
    SET @cmd=‘cluster /list’
    INSERT INTO #allClusters
    EXEC XP_CMDSHELL @cmd

    – Get the rows from ‘cluster /list’ san the garbage
    SELECT @TotalCount=count(*) FROM #allClusters

    – Kill the newline characters
    INSERT INTO clusters
    SELECT REPLACE(REPLACE(REPLACE(ClusterName, CHAR(10), ”), CHAR(13), ”), CHAR(9), ”), NULL, NULL
    FROM #allClusters
    WHERE ID > 2 AND ID < (@TotalCount-1)

    – Prep our loop
    DECLARE ClusterCursor CURSOR FOR
    SELECT * FROM clusters
   
    – Loop through all clusters and pull everything we can
    OPEN ClusterCursor
    FETCH NEXT FROM ClusterCursor INTO @ClusterName, @InstanceName, @Envir
    WHILE @@FETCH_STATUS = 0
    BEGIN   
        – Get cluster Node info
        SET @cmd=‘cluster /cluster:’+@ClusterName+‘ NODE’
        INSERT INTO #clusterNodes
        EXEC XP_CMDSHELL @cmd
        – Parse out the data we want
        SELECT @TotalCount=count(*) FROM #clusterNodes
        DECLARE NodeTemp CURSOR FOR
        SELECT ClusterNode FROM #clusterNodes
        WHERE ID > 4 AND ID < (@TotalCount)
        OPEN NodeTemp
        FETCH NEXT FROM NodeTemp INTO @TempRow
        WHILE @@FETCH_STATUS = 0
        BEGIN   
            INSERT INTO #tmp           
            SELECT item FROM fnSplit((REPLACE(REPLACE(REPLACE(@TempRow, CHAR(10), ”), CHAR(13), ”), CHAR(9), ”)),‘ ‘)
            SELECT top(1) @NODE=item FROM #tmp
            DELETE top(2) FROM #tmp
            SELECT top(1) @STATUS=item FROM #tmp
            TRUNCATE TABLE #tmp
            FETCH NEXT FROM NodeTemp INTO @TempRow
            – Save results
            INSERT INTO clusternodes VALUES(@ClusterName, @NODE, @STATUS)
        END

        – Get cluster Group info
        SET @cmd=‘cluster /cluster:’+@ClusterName+‘ GROUP’
        INSERT INTO #clusterGroups
        EXEC XP_CMDSHELL @cmd
        SELECT @TotalCount=count(*) FROM #clusterGroups
        DECLARE GroupTemp CURSOR FOR
        SELECT ClusterGroup FROM #clusterGroups
        WHERE ID > 4 AND ID < (@TotalCount) AND ClusterGroup NOT LIKE ‘Cluster Group%’
        OPEN GroupTemp
        FETCH NEXT FROM GroupTemp INTO @TempRow
        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO #tmp
            –REPLACE(REPLACE(REPLACE(REPLACE(@TempRow, ‘Group 0′, ‘Group_0′), CHAR(10), ”), CHAR(13), ”), CHAR(9), ”)
            SELECT item FROM fnSplit((REPLACE(REPLACE(REPLACE(REPLACE(@TempRow, ‘Group 0′, ‘Group_0′), CHAR(10), ”), CHAR(13), ”), CHAR(9), ”)),‘ ‘)
            SELECT top(1) @GROUP=item FROM #tmp
            DELETE top(1) FROM #tmp
            SELECT top(1) @NODE=item FROM #tmp
            DELETE top(1) FROM #tmp
            SELECT top(1) @STATUS=item FROM #tmp
            TRUNCATE TABLE #tmp
            FETCH NEXT FROM GroupTemp INTO @TempRow
            – Save results
            INSERT INTO clustergroups VALUES(@ClusterName, @GROUP, @NODE, @STATUS)
           
            – Associate the instance name with our cluster now that we know it
            UPDATE clusters
            SET InstanceName = (
                            SELECT top 1 actual_server FROM admin..v_sql_servers_in_dc WHERE host=@NODE
                            ),
            Environment = (
                            SELECT top 1 [type] FROM admin..v_sql_servers_in_dc WHERE host=@NODE
                            )
            WHERE ClusterName = @ClusterName
        END

        – Prep for next loop
        TRUNCATE TABLE #clusterNodes
        TRUNCATE TABLE #clusterGroups
        CLOSE NodeTemp
        DEALLOCATE NodeTemp
        CLOSE GroupTemp
        DEALLOCATE GroupTemp
        FETCH NEXT FROM ClusterCursor INTO @ClusterName, @InstanceName, @Envir
    END

    – Cleanup
    DROP TABLE #tmp
    DROP TABLE #clusterGroups
    DROP TABLE #clusterNodes
    DROP TABLE #allClusters
    CLOSE ClusterCursor
    DEALLOCATE ClusterCursor  
END