Inventorying hardware and OS information on all SQL Servers

Whenever database administrators begin new jobs or to administer and manage new SQL servers,
the first step is to
take inventory of the server. This includes information like Operating
system, Service packs, physical memory, virtual memory etc. In this article, I explain
how to collect such information using VB Script and WMI.

Pre-requisite

Microsoft
Windows operating system which supports WMI and the user-id that is used to
login to the machine where you will run the script must have the necessary permissions
to all of the servers listed in the serverlist.txt.

Step1

Create
a folder, c:Inventory, and under that folder create the file "Serverlist.txt,"
as shown below. Serverlist.txt contains a list of server names about which you
would like to collect information.

SQL

YUKON

ETL

Note: Change the server names according to
your environment

Step2

Create the VB
Script file, c:InventoryCollectInventory.vbs, and copy and paste the below
code into it. (Download CollectInventory.vbs)

‘Objective: Collect Inventory and write to a .csv file

‘Author: MAK

‘Contact: mak_999@yahoo.com

‘Date: Apr 2, 2002

‘on error resume next

Set iFSO = CreateObject(“Scripting.FilesyStemObject”)

Set oFSO = CreateObject(“Scripting.FilesyStemObject”)

InputFile=”c:InventoryServerlist.txt”

Outputfile=”c:InventoryInventorylist_” + cstr(Month(now()))+”_”+cstr(day(now()))+”.csv”

Set ofile = ofso.createTextFile(OutputFile, True)

Set ifile = iFSO.OpenTextFile(inputfile)

ofile.writeline “ServerName,CurrentTimeZone,DaylightInEffect,Description,Domain,InstallDate,

	Manufacturer,Model,Name,NetworkServerModeEnabled,NumberOfProcessors,Status,

	SystemStartupDelay,SystemStartupSetting,SystemType,TotalPhysicalMemory,UserName,

	AvailableVirtualMemory,TotalPageFileSpace,TotalPhysicalMemoryx,TotalVirtualMemory,

	osName,SerialNumber,ServicePackMajorVersion,ServicePackMinorVersion,SystemDevice,

	SystemDirectory,Version,WindowsDirectory”

Do until ifile.AtEndOfLine

Server= ifile.ReadLine

Set objWMIService = GetObject(“winmgmts:\” & server& “rootcimv2”)

Set colItems = objWMIService.ExecQuery(“Select * from Win32_ComputerSystem”,,48)

For Each objItem in colItems

if isnull(server)=true then A=”” else A= server  end if

‘if isnull(objItem.Caption)=true then B=”” else B=objItem.Caption  end if

if isnull(objItem.CurrentTimeZone)=true then C=”” else C=cstr(objItem.CurrentTimeZone)  end if

if isnull(objItem.DaylightInEffect)=true then D=”” else D= cstr(objItem.DaylightInEffect)  end if

if isnull(objItem.Description)=true then E=”” else E=objItem.Description  end if

if isnull(objItem.Domain)=true then F=”” else F=objItem.Domain  end if

if isnull(objItem.InstallDate)=true then G=”” else G=cstr(objItem.InstallDate)  end if

if isnull(objItem.Manufacturer)=true then H=”” else H=objItem.Manufacturer  end if

if isnull(objItem.Model)=true then I=”” else I=objItem.Model  end if

if isnull(objItem.Name)=true then J=”” else J=objItem.Name  end if

if isnull(objItem.NetworkServerModeEnabled )=true then K=””

	else K=objItem.NetworkServerModeEnabled  end if

if isnull(objItem.NumberOfProcessors)=true then L=””

	else L=cstr(objItem.NumberOfProcessors)  end if

if isnull(objItem.Status)=true then M=”” else M=objItem.Status  end if

if isnull(objItem.SystemStartupDelay )=true then N=””

	else N=cstr(objItem.SystemStartupDelay)  end if

if isnull(objItem.SystemStartupSetting)=true then P=””

	else P=objItem.SystemStartupSetting  end if

if isnull(objItem.SystemType)=true then Q=”” else Q=objItem.SystemType  end if

if isnull(objItem.TotalPhysicalMemory)=true then R=””

	else R=cstr(objItem.TotalPhysicalMemory)  end if

if isnull(objItem.UserName)=true then S=”” else S=objItem.UserName end if

Next

Set objWMIService =NOTHING

Set colItems =NOTHING

Set objWMIService = GetObject(“winmgmts:\” & server & “rootcimv2”)

Set colItems = objWMIService.ExecQuery(“Select * from Win32_LogicalMemoryConfiguration”,,48)

For Each objItem in colItems

if isnull(objItem.AvailableVirtualMemory)=true then T=””

	else T=cstr(objItem.AvailableVirtualMemory) end if

if isnull(objItem.TotalPageFileSpace)=true then U=””

	else U=cstr(objItem.TotalPageFileSpace) end if

if isnull(objItem.TotalPhysicalMemory)=true then V=””

	else V=cstr(objItem.TotalPhysicalMemory) end if

if isnull(objItem.TotalVirtualMemory)=true then W=””

	else W=cstr(objItem.TotalVirtualMemory) end if

Next

Set objWMIService =NOTHING

Set colItems =NOTHING

Set objWMIService = GetObject(“winmgmts:\” & server & “rootcimv2”)

Set colItems = objWMIService.ExecQuery(“Select * from Win32_OperatingSystem”,,48)

For Each objItem in colItems

if isnull(objItem.Name)=true then X=”” else X=objItem.Name end if

if isnull(objItem.SerialNumber)=true then Y=”” else Y=cstr(objItem.SerialNumber) end if

if isnull(objItem.ServicePackMajorVersion)=true then Z=””

	else Z=CSTR(objItem.ServicePackMajorVersion) end if

if isnull(objItem.ServicePackMinorVersion)=true

	then AA=”” else AA=CSTR(objItem.ServicePackMinorVersion) end if

if isnull(objItem.SystemDevice)=true then AB=”” else AB=cstr(objItem.SystemDevice) end if

if isnull(objItem.SystemDirectory)=true then AC=”” else AC=objItem.SystemDirectory end if

if isnull(objItem.Version)=true then AD=”” else AD=CSTR(objItem.Version) end if

if isnull(objItem.WindowsDirectory)=true then AE=”” else AE=objItem.WindowsDirectory end if

Next

ofile.writeline A & “,” & C & “,” & D & “,” & E & “,” & F & “,” & G & “,

	” & H & “,” & I & “,” & J & “,” & K & “,” & L & “,” & M & “,

	” & N & “,” & P & “,” & Q & “,” & R & “,” & S & “,” & T & “,

	” & U & “,” & V & “,” & W & “,” & X & “,” & Y & “,” & Z & “,

	” & AA & “,” & AB & “,” & AC & “,” & AD & “,” & AE

Loop

msgbox “Inventory COmpleted”