Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 16, 2004

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

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM