Sometime back I was involved in a project which was to collect information for the servers we are supporting – creating inventory of servers. Logging into each server and collecting information is bit tedious when you have hundreds of server in your environment. I have spent sometime and created 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 needed.
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 : http://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 | http://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
Please do post back your feed back for this script, I will try my best to update and post back new version for this script.
- Hemantgiri S. Goswami (Cross posting from http://www.sql-server-citation.com/)
