Archive for October, 2013

How to query the OS System Info?

If you are working on documenting your company’s SQL Server environment or simply just making assessment on the Operating System specifications and you have wondered since you have over 100 hundred SQL Server instances, how in the world do you have the time for the manual effort to logon each of the Window Servers to find their OS level information? Is there an easy way to automate this process with a TSQL script? Yes! My friends, there is. Talk is cheap. Let’s cut to the chase. Let’s review what you need to be aware of before running it and then you can just copy and paste the script into your multiple server query window after you had registered all your SQL Servers that you wish to run the script on to gather the system info.

What do you need to be aware of?

  • The script will need to turn the advanced options on in order to turn the xp_cmdshell option on.
  • If the xp_cmdshell was off, it will be turned on to load the system info data into a temp table.
  • After the system info data is extracted, the xp_cmdshell will get to turn back to off if it was off.
  • The advanced options will also get returned to its original setting.

This is what it will look like after you had run the script against one instance (click on the following image to magnify the size of the image):
System Info

Here’s the script. Enjoy!

/*
Name: SystemInfo.sql
Description: TSQL script to query the host name, domain name, time zone, OS, system type, total physical memory, and system boot time
Developer: JP Chen
Date Created: October 30, 2013 10:00 PM
*/
-- variables delcarations
declare @HostName nvarchar(125)
	, @DomainName nvarchar(125)
	, @TimeZone nvarchar(125)
	, @OS nvarchar(125)
	, @SystemType nvarchar(125)
	, @Processor nvarchar(125)
	, @TotalPhysicalMemory nvarchar(125)
	, @SystemBootTime nvarchar(125)
	, @xp_cmdshell bit
	, @isshowadvancedoptions bit

if  @@microsoftversion / power(2, 24) >= 9
begin
	-- check if enabled 'show advanced options', if no enable it
	select @isshowadvancedoptions = convert(int, isnull(value, value_in_use))
	from  sys.configurations
	where  name = 'show advanced options'	

	if @isshowadvancedoptions = 0
	begin
		exec sp_configure 'show advanced options', 1;
		reconfigure;
	end

	-- check if enabled 'xp_cmdshell', if not then enable it
	select @xp_cmdshell = convert(int, isnull(value, value_in_use)) 
	from  sys.configurations
	where  name = 'xp_cmdshell';
	
	if @xp_cmdshell = 0
	begin
		exec sp_configure 'xp_cmdshell', 1;
		reconfigure;
	end
	
	-- drop the #systeminfo if it exists
	if object_id('tempdb..#systeminfo') is not null
	drop table #systeminfo;
	
	-- create the #systeminfo table
	create table #systeminfo 	
	(data nvarchar(255))
	
	-- load the systeminfo into the #systeminfo table
	insert into #systeminfo 
	exec master..xp_cmdshell 'systeminfo'
	
	-- host name
	select @HostName = ltrim(replace(data, 'Host Name:',''))
								   from #systeminfo
								   where data like 'Host Name%'
	-- domain name
	select @DomainName = ltrim(replace(data, 'Domain:',''))
								   from #systeminfo
								   where data like 'Domain%'

	-- time zone
	select @TimeZone = ltrim(replace(data, 'Time Zone:',''))
								   from #systeminfo
								   where data like 'Time Zone%'

	-- os		   							   
	select @OS = (select ltrim(replace(data, 'os name:','')) 
				   from #systeminfo 
				   where data like 'os name%') 
				  + right(@@version, len(@@version)+3- charindex ('build', @@version))

	-- system type		   							   
	select @SystemType = ltrim(replace(data, 'System Type:','')) 
				   from #systeminfo 
				   where data like 'System Type%'
		      
	-- processor		   							   
	select @Processor = ltrim(replace(data, 'Processor(s):','')) 
				   from #systeminfo 
				   where data like 'Processor(s)%'
				   
	-- system type		   							   
	select @SystemType = ltrim(replace(data, 'System Type:','')) 
				   from #systeminfo 
				   where data like 'System Type%'				   

	-- total physical memory		
	select @TotalPhysicalMemory = ltrim(replace(data, 'Total Physical Memory:',''))
								   from #systeminfo
								   where data like 'Total Physical Memory%'

	-- system boot time		
	select @SystemBootTime = ltrim(replace(data, 'System Boot Time:',''))
								   from #systeminfo
								   where data like 'System Boot Time%'
	-- output
	select @HostName as [Host Name]
			, @DomainName [Domain Name]
			, @TimeZone [Time Zone]
			, @OS [OS]
			, @Processor [Processor(s)]
			, @SystemType [System Type]
			, @TotalPhysicalMemory [Total Physical Memory]							   
			, @SystemBootTime [System Boot Time]

	-- revert the option on show advanced options
	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'xp_cmdshell') <> @xp_cmdshell
	begin
		exec sp_configure 'xp_cmdshell', @xp_cmdshell;
		reconfigure;
	end

	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'show advanced options') <> @isshowadvancedoptions
	begin	
		exec sp_configure 'show advanced options', @isshowadvancedoptions
		reconfigure with override
	end			
	
end