You can utilize two things: PowerShell and xp_cmdshell[/url] and this will open your world into a new wealth of information to pull or capture for a server. 😀

Now, xp_cmdshell is not bad and don't let anyone tell you it is. I used to think it was since supporting military as a contractor they frowned upon using it. However, only sysadmin folks can execute it and you can additionally add into your procedure to verify if it is on, if not enable it, and then when done turn it back off (I do this on client machines where I find it not in use).

Now for folks that come across this in the future, this solution only works if PowerShell exist on the SQL Server itself...

I use this bit of code to pull in OS information, disk information, and CPU information. I actually similar code to this running in production for a client to help me monitor disk space usage.DECLARE @XML_string varchar(max)

DECLARE @DriveSpace XML

DECLARE @XML TABLE (XMLitself varchar(2000), orderIt int IDENTITY(1,1) PRIMARY KEY);

INSERT INTO @XML(XMLitself)

EXEC xp_cmdshell 'powershell.exe -noprofile -command "Get-WmiObject -Class Win32_Processor -ErrorAction ''Continue'' -ErrorVariable eWmiData | Select-Object Name, NumberOfCores, NumberOfLogicalProcessors, AddressWidth, MaxClockSpeed | ConvertTo-XML -As string"'

SELECT @XML_string = COALESCE(@XML_string,'') + XMLitself

FROM @XML

WHERE XMLitself IS NOT NULL

SELECT @DriveSpace = @XML_string

SELECT

MAX(CASE WHEN attribute='Name' THEN VALUE ELSE '' END) AS ProcModel,

CAST(MAX(CASE WHEN attribute='NumberOfCores' THEN VALUE ELSE '' END) AS int) AS NumCores,

CAST(MAX(CASE WHEN attribute='NumberOfLogicalProcessors' THEN VALUE ELSE '' END) AS int) AS NumLogicalProcessors,

MAX(CASE WHEN attribute='AddressWidth' THEN VALUE ELSE '' END) AS Architecture,

CAST(MAX(CASE WHEN attribute='MaxClockSpeed' THEN VALUE ELSE '' END) AS int) AS MaxClockSpeed

FROM (

SELECT [property].value('(./text())[1]','varchar(80)') AS [value],

[property].value('@Name','varchar(20)') AS [attribute],

DENSE_RANK() OVER (ORDER BY [object]) AS unique_object

FROM @DriveSpace.nodes('Objects/Object') AS b([object])

CROSS APPLY b.object.nodes('./Property') AS c(property)

) psData

GROUP BY unique_object;

This will give you output like this: