Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Does anyone know of a way to get the machine's processor name from T-SQL? Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 7:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 1:50 PM
Points: 1,093, Visits: 180
I'd like to retrieve the processor name (the same that name that shows up in the system properties control panel applet)

I was able to get this code to work but I'd rather not use xp_regread if there is a better way:

DECLARE @Key_Value nvarchar(4000), @RC int

EXEC @RC = master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'HARDWARE\DESCRIPTION\System\CentralProcessor\0'
, 'ProcessorNameString'
, @Key_Value OUT
, N'no output'

Returns:
Intel(R) Xeon(R) CPU E7330 @ 2.40GHz



Post #1597288
Posted Tuesday, July 29, 2014 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,205, Visits: 12,687
JunkIt (7/29/2014)
I'd like to retrieve the processor name (the same that name that shows up in the system properties control panel applet)

I was able to get this code to work but I'd rather not use xp_regread if there is a better way:

DECLARE @Key_Value nvarchar(4000), @RC int

EXEC @RC = master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'HARDWARE\DESCRIPTION\System\CentralProcessor\0'
, 'ProcessorNameString'
, @Key_Value OUT
, N'no output'

Returns:
Intel(R) Xeon(R) CPU E7330 @ 2.40GHz


Why do you want to do this in sql? Why not just store the name in a table. It isn't like that value is going to change all the time.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1597306
Posted Tuesday, July 29, 2014 8:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 1:50 PM
Points: 1,093, Visits: 180
I see I posted this under the wrong forum -- the solution only need apply to SQL Server 2008 R2 or higher.

As for the "why", this is a data collection/troubleshooting stored procedure that will run on our clients' servers. Since I don't have access to their hardware I want the lookup to be dynamic.



Post #1597312
Posted Tuesday, July 29, 2014 8:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,205, Visits: 12,687
JunkIt (7/29/2014)
I see I posted this under the wrong forum -- the solution only need apply to SQL Server 2008 R2 or higher.

As for the "why", this is a data collection/troubleshooting stored procedure that will run on our clients' servers. Since I don't have access to their hardware I want the lookup to be dynamic.


Gotcha. I don't know of any other way to do that. Reading that type of information is well outside of the "standard" querying implemented by sql server. If this is going to be run repeatedly it still might make sense to do this once and put the value in a table.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1597314
Posted Tuesday, July 29, 2014 9:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 934, Visits: 2,673
You can utilize two things: PowerShell and xp_cmdshell 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:


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1597374
Posted Tuesday, July 29, 2014 7:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:36 PM
Points: 35,546, Visits: 32,131
Shawn Melton (7/29/2014)
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).


Well said. I wish that everyone understood that. BTW, turning it off does nothing for security except cause a hacker's attack software a 3ms burp to turn it on once they've broken in with "SA" privs. If they can't break in with "SA" privs, they can't use xp_CmdShell even if it's enabled.

And, I agree... PowerShell and xp_CmdShell sure do make life easy especially when it comes to the likes of WMI.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1597543
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse