How to Collect Cluster Information Using TSQL

Sometime back, I was involved in a project that consisted in collecting information for the servers we are supporting – creating an inventory of servers. Logging into each server and collecting information is a bit tedious when you have hundreds of servers in your environment.  I have spent sometime creating a script that does this work for me; however, I still need to connect to the server – but using SSMS/Query Window – and execute the script. And then the script will provide me the details I need.  Please remember this script is enabling xp_cmdshell and disabling it back, before you run this script if this is acceptable in your environment.

This inventory should have details like below for cluster and stand alone instances:

Server Name

OS Name

OS Edition

OS Patch Level

SQL Server IP

Is Clustered

Node1_Name

Node1_IP

Node2_Name

Node2_IP

SQL Server Edition

SQL Server Patch Level

Server Time Zone

SQL Server Version

SQL Server Platform

Processor Core

Physical Memory

Service Account Name

Domain

Looks good ?  Below is the version 1 of this script:



IP address portion using : https://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx
REMOVE sp_configure parameters if you are executing this script on SQL Server 2000
Created By    :    Hemantgiri S. Goswami | https://www.sql-server-citation.com
Date        :    24th March 2013
Version        :    1.0
Tested ON:
Windows Server     >>    2003, 2008, 2008 R2
SQL Server        >>    2000, 2005, 2008, 2008 R2, 2012
*/
sp_configure ‘show advanced options’,1
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘xp_cmdshell’,1
RECONFIGURE WITH OVERRIDE
GO
DECLARE    @TimeZone            NVARCHAR(100)
,@ProductVersion    SYSNAME
,@PlatForm            SYSNAME
,@Windows_Version    SYSNAME
,@Processors        SYSNAME
,@PhysicalMemory    SYSNAME
,@ServiceAccount    SYSNAME
,@IPAddress            SYSNAME
,@DOMAIN            SYSNAME
,@MachineType        SYSNAME
,@SQLServerIP        VARCHAR(255)
,@CMD                VARCHAR(100)
,@Node1                VARCHAR(100)
,@Node2                VARCHAR(100)
,@Node1IP            VARCHAR(100)
,@Node2IP            VARCHAR(100)
,@OSEdition            VARCHAR(100)
,@OSVersion            VARCHAR(100)
,@OSName            VARCHAR(100)
,@OSPatchLevel        VARCHAR(100)
CREATE TABLE #TempTable
(
[Index] VARCHAR(2000),
[Name] VARCHAR(2000),
[Internal_Value] VARCHAR(2000),
[Character_Value] VARCHAR(2000)
) ;
INSERT INTO #TempTable
EXEC xp_msver;
— Replace @Value_Name to N’TimeZoneKeyName’ when running on Windows 2008
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SYSTEM\CurrentControlSet\Control\TimeZoneInformation’,
@value_name   = N’StandardName’,
@value        = @TimeZone output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SYSTEM\CurrentControlSet\Services\MSSQLServer’,
@value_name   = N’ObjectName’,
@value        = @ServiceAccount output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SYSTEM\CurrentControlSet\Control\ProductOptions’,
@value_name   = N’ProductType’,
@value        = @MachineType output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SYSTEM\CurrentControlSet\Services\Tcpip\Parameters’,
@value_name   = N’Domain’,
@value        = @DOMAIN output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’CLUSTER\NODES\1′,
@value_name   = N’NodeName’,
@value        = @Node1 output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’CLUSTER\NODES\2′,
@value_name   = N’NodeName’,
@value        = @Node2 output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SOFTWARE\Microsoft\Windows NT\CurrentVersion’,
@value_name   = N’ProductName’,
@value        = @OSName output
create table #OSEdition (VALUe varchar(255),OSEdition varchar(255), data varchar(100))
insert into #OSEdition
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SYSTEM\CurrentControlSet\Control\ProductOptions’,
@value_name   = N’ProductSuite’
SET @OSEdition = (SELECT TOP 1 OSedition  FROM #OsEdition)
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SOFTWARE\Microsoft\Windows NT\CurrentVersion’,
@value_name   = N’CSDVersion’,
@value        = @OSPatchLevel output
set @cmd = ‘ping ‘ + @Node1
create table #Node1IP (grabfield varchar(255))
insert into #Node1IP exec master.dbo.xp_cmdshell @cmd
set @cmd = ‘ping ‘ + @Node2
create table #Node2IP (grabfield varchar(255))
insert into #Node2IP exec master.dbo.xp_cmdshell @cmd
set @cmd = ‘ping ‘ + @@servername
create table #SQLServerIP (grabfield varchar(255))
insert into #SQLServerIP exec master.dbo.xp_cmdshell @cmd
SET        @SQLServerIP    =    (
SELECT substring(grabfield,  charindex(‘[‘,grabfield)+1, charindex(‘]’,grabfield)-charindex(‘[‘,grabfield)-1)
from #SQLServerIP  where left(grabfield,7) = ‘Pinging’
)
SET        @Node1IP            =    (
SELECT substring(grabfield,  charindex(‘[‘,grabfield)+1, charindex(‘]’,grabfield)-charindex(‘[‘,grabfield)-1)
from #Node1IP  where left(grabfield,7) = ‘Pinging’
)
SET        @Node2IP            =    (
SELECT substring(grabfield,  charindex(‘[‘,grabfield)+1, charindex(‘]’,grabfield)-charindex(‘[‘,grabfield)-1)
from #Node2IP  where left(grabfield,7) = ‘Pinging’
)
SET        @ProductVersion =    (SELECT Character_Value from #TempTable where [INDEX]=2)
SET        @Platform        =    (SELECT Character_Value from #TempTable where [INDEX]=4)
SET        @Windows_Version=    (SELECT Character_Value from #TempTable where [INDEX]=15)
SET        @Processors        =    (SELECT Character_Value from #TempTable where [INDEX]=16)
SET        @PhysicalMemory    =    (SELECT Character_Value from #TempTable where [INDEX]=19)
SELECT
ServerName            =    @@SERVERNAME
,OSName                =    @OSName
,OSEdition            =    @OSEdition
,OSPatchLevel        =    @OSPatchLevel
,SQLServerIP        =    @SQLServerIP
,IsClustered        =    SERVERPROPERTY(‘IsClustered’)
,Node1_Name            =    @Node1
,Node1_IP            =    @Node1IP
,Node2_Name            =    @Node2
,Node2_IP            =    @Node2IP
,SQLServerEdition    =    SERVERPROPERTY(‘Edition’)
,SQLServerLevel        =    SERVERPROPERTY(‘ProductLevel’)
,ServerTimeZone        =    @TimeZone
,SQLServerVersion    =    @ProductVersion
,SQLServerPlatform    =    @PlatForm
,ProcessorCore        =    @Processors
,PhysicalMemory        =    @PhysicalMemory
,ServiceAccountName    =    @ServiceAccount
,WKS_Server            =    @MachineType
,Domain                =    @DOMAIN
GO
DROP TABLE #Node1IP
DROP TABLE #NODE2IP
DROP TABLE #SQLServerIP
DROP TABLE #TempTable
DROP TABLE #OSEdition
GO
sp_configure ‘xp_cmdshell’,0
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘show advanced options’,0
RECONFIGURE WITH OVERRIDE
GO