Archive for the ‘Operating System Level Information’ Category

How to query and display the Operating System level information?

While considered as informational, the Operating System level information is critical to knowing the limitation for upgrades and SQL Server configurations. In this blog, we will query and display the following details and send the report to our email addresses:

  • Host name
  • Domain name
  • Time zone
  • Operating System version, edition, and service pack
  • System type
  • Total RAM in MB
  • System Boot Time
  • # of Logical CPUs
  • Virtual Machine Type

These are the same type of information you can find by running the systeminfo.exe in the command prompt. To get these information displayed through TSQL, you will need to enable xp_cmdshell.

Here’s the code:

-- Operating System Level Information
-- OS Version and Edition, Service Pack, # of CPUs, Total Physical Memory, Clustering, Virtual Machine
/* Note: Change the recipients in line #128 */

set nocount on
-- variables delcarations
declare @HostName nvarchar(125)
	, @DomainName nvarchar(125)
	, @TimeZone nvarchar(125)
	, @OS nvarchar(125)
	, @SystemType nvarchar(125)
	, @TotalPhysicalMemory nvarchar(125)
	, @SystemBootTime nvarchar(125)
	, @xp_cmdshell bit
	, @isshowadvancedoptions bit
	, @CPUs tinyint
	, @VirtualMachineType nvarchar(25)

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%'
		      
	-- 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%'

	-- 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 	

-- CPU count, isClustered, CurrentNode, and Virtual Machine Type
SELECT @CPUs = cpu_count
, @VirtualMachineType = virtual_machine_type_desc
FROM sys.dm_os_sys_info


-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)

SET @Recipients = 'email@companyname.com' -- specify your recipients here, separate with ;

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
	BEGIN
		SELECT @TableHTML = 
		'<html>
		<div style="font-family:Verdana; font-size:14pt;"><div style="font-weight:bold; width:auto; float:left;">'+@@servername+'</div><div style=" width:auto; float:left; padding-left:5px; "> Operating System Level Information:</div><br></div></div><br>
		<table style="border: 1px solid #000000; border-collapse:collapse; table-layout:fixed; font-family:Arial; font-size:11px;" cellpadding=0 cellspacing=0 border=0>' +
		'<tr style="border: 1px solid #000000; font-family:Verdana; font-size:12px; background-color:#3B8D24; color:white; height:30px;">
			<th style="border: 1px solid #000000;" align=center>Host Name</th>
			<th style="border: 1px solid #000000;" align=center>Domain Name</th>
			<th style="border: 1px solid #000000;" align=center>Time Zone</th>
			<th style="border: 1px solid #000000;" align=center>Operating System</th>
			<th style="border: 1px solid #000000;" align=center>System Type</th>
			<th style="border: 1px solid #000000;" align=center>Total RAM [MB]</th>
			<th style="border: 1px solid #000000;" align=center>System Boot Time</th>
			<th style="border: 1px solid #000000;" align=center># of Logical CPUs</th>
			<th style="border: 1px solid #000000;" align=center>Virtual Machine Type</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td style="border: 1px solid #000000;">' + @HostName + '</td>' +
			'<td style="border: 1px solid #000000;">' + @DomainName+ '</td>' +
			'<td style="border: 1px solid #000000;">' + @TimeZone + '</td>' +
			'<td style="border: 1px solid #000000;">' + @OS + '</td>' +
			'<td style="border: 1px solid #000000;">' + @SystemType + '</td>' +
			'<td style="border: 1px solid #000000;">' + @TotalPhysicalMemory + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(VARCHAR, @SystemBootTime, 13) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(VARCHAR, @CPUs) + '</td>' +
			'<td style="border: 1px solid #000000;">' + @VirtualMachineType + '</td></tr></table></html>'
		
		-- Send email notification
		SELECT @MailSubject = CONVERT(VARCHAR(50),@@servername) + ' OS Level Info'
		
		-- Find the database mail profile name
		create table #dbmail_profile
		(
		profileid int null,
		profilename varchar(125) null,
		accountid int null,
		accountname varchar(125) null,
		sequencenumber int null
		)
		insert #dbmail_profile
		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

		select @MailProfile = profilename
		from #dbmail_profile
		where sequencenumber = 1

		drop table #dbmail_profile

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @MailProfile,
			@recipients = @Recipients,
			@subject = @MailSubject,
			@body_format = 'HTML',
			@body = @TableHTML;
	END
set nocount off	

This is what it looks like from the email report:
OS Level Info