Inventorying hardware and OS information on all SQL Servers

Wednesday Jun 16th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

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. Learn how to collect such information using VB Script and WMI.

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:\Inventory\CollectInventory.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:\Inventory\Serverlist.txt"
Outputfile="c:\Inventory\Inventorylist_" + 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& "\root\cimv2")
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 & "\root\cimv2")
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 & "\root\cimv2")
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"

Step3

Execute the VB script "CollectInventory.vbs". This will create the file "Inventorylist_m_dd.csv," with the message shown below.

Step4

When you open the file "Inventorylist_m_dd.csv," you will find all of the information that you need. It collects information such as 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 and WindowsDirectory

Step5:

You can import the .csv file using SQL Server Import (DTS) and store it in a database table. Use the script below to create the database and corresponding tables.

Use master
Go
Create database Inventory
Go
CREATE TABLE [Inventorylist] (
	[ServerName] [varchar] (200) ,
	[CurrentTimeZone] [varchar] (200) ,
	[DaylightInEffect] [varchar] (200) ,
	[Description] [varchar] (200) ,
	[Domain] [varchar] (200) ,
	[InstallDate] [varchar] (200) ,
	[Manufacturer] [varchar] (200) ,
	[Model] [varchar] (200) ,
	[Name] [varchar] (200) ,
	[NetworkServerModeEnabled] [varchar] (200) ,
	[NumberOfProcessors] [varchar] (200) ,
	[Status] [varchar] (200) ,
	[SystemStartupDelay] [varchar] (200) ,
	[SystemStartupSetting] [varchar] (200) ,
	[SystemType] [varchar] (200) ,
	[TotalPhysicalMemory] [varchar] (200) ,
	[UserName] [varchar] (200) ,
	[AvailableVirtualMemory] [varchar] (200) ,
	[TotalPageFileSpace] [varchar] (200) ,
	[TotalPhysicalMemoryx] [varchar] (200) ,
	[TotalVirtualMemory] [varchar] (200) ,
	[osName] [varchar] (200) ,
	[SerialNumber] [varchar] (200) ,
	[ServicePackMajorVersion] [varchar] (200) ,
	[ServicePackMinorVersion] [varchar] (200) ,
	[SystemDevice] [varchar] (200) ,
	[SystemDirectory] [varchar] (200) ,
	[Version] [varchar] (200) ,
	[WindowsDirectory] [varchar] (200) 
)

GO

Sample data

Use Inventory
go
Select Domain, InstallDate, Manufacturer, Model, Name, NetworkServerModeEnabled
from Inventorylist

Domain

InstallDate

Manufacturer

Model

Name

NetworkServerModeEnabled

AMERICAS

TOSHIBA

M500D

SQL

TRUE

AMERICAS

IBM

-[848093X]-

YUKON

TRUE

AMERICAS

TOSHIBA

M500D

ETL

TRUE

Use Inventory
go
select Name,WindowsDirectory,osName,Version,ServicePackMajorVersion as SP,TotalPhysicalMemory
as memory  from Inventorylist

Name

WindowsDirectory

osName

Version

SP

Memory

SQL

C:\WINNT

Microsoft Windows 2000 Server

5.0.2195

4

335003648

YUKON

C:\WINNT

Microsoft Windows 2000 Server

5.0.2195

4

267763712

ETL

C:\WINNT

Microsoft Windows 2000 Server

5.0.2195

4

335003648

Conclusion

As mentioned, the intent of this article is to collect all inventory information of all of the SQL Servers and store it in a .csv file or on a database table. The defined method can also be used for collecting inventory information on all of the servers on the network.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved