Inventorying hardware and OS information on all SQL Servers - Page 2

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

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

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