Query for Processor

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Query for Processor

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Query for Processor

Page: [1]

Message

<< Older Topic Newer Topic >>

Query for Processor - 2/5/2008 10:28:39 AM

jray

Posts: 209
Score: 1
Joined: 3/29/2004
Status: offline

I am in desperate need of some help with a query, I have tried in vain to get one to work like I need it to. I am hoping someone can help me. Here is the info I need:

Processor type/name
System name
Is the processor Mutli Core
How many total processors

I can get some of this info with my query but the data does not match, meaning somehow the tables I am joining do not correlate with each other. I am desperate for any help you guys can provide. I need to get this information out very soon, please let me know if you need more info from me, I appreciate any help you can provide.

Post #: 1

RE: Query for Processor - 2/5/2008 10:34:45 AM

cnackers

Posts: 435
Score: 15
Joined: 9/22/2006
From: Madison, WI
Status: offline

Can you post your query this far and what you've got...?

_____________________________

My Blog:
http://myitforum.com/cs2/blogs/cnackers/default.aspx

(in reply to jray)

Post #: 2

RE: Query for Processor - 2/5/2008 10:55:12 AM

jray

Posts: 209
Score: 1
Joined: 3/29/2004
Status: offline

This is one I wrote very quickly, I wrote another one and lost it when my machine rebooted a few days ago what much prettier and gave me better information but I am having trouble re-creating it…

select distinct pr.systemname0, cs.Model0, pr.ismulticore0, cs.numberofprocessors0
from dbo.v_GS_PROCESSOR as pr
right outer join dbo.v_GS_COMPUTER_SYSTEM as CS on PR.ResourceID=PR.ResourceID
left outer join V_fullcollectionmembership as FC on PR.ResourceID=FC.ResourceID
where fc.collectionid = 'SMS000DS'
order by pr.systemname0

Here is another one I have, but as you can see it does not have the number of processors or if they are multicore.

SELECT DISTINCT cs.name0 'Computer Name', cs.username0 'User Name', cs.model0 'Model', pr.name0 'Description', pr.maxclockspeed0 'Processor', x.TotalPageFileSpace0 'Page File', x.TotalPhysicalMemory0 'Physical Memory',x.TotalVirtualMemory0 'Virtual Memory'
FROM v_gs_computer_system cs
left outer join v_GS_PROCESSOR as pr on cs.ResourceID = pr.ResourceID
left outer join v_GS_X86_PC_MEMORY as x on x.ResourceID = pr.ResourceID
left outer join v_FullCollectionMembership as fc on pr.ResourceID = fc.ResourceID
where fc.collectionID = 'sms000DS'
order by cs.name0

(in reply to cnackers)

Post #: 3

RE: Query for Processor - 2/5/2008 11:40:18 AM

jnelson993

Posts: 1003
Score: 142
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

I'd do it this way, include a join to V_R_System so you can remove obsolete, decommissioned and include only valid clients,
then I'd use INNER JOINs instead of left/right outer joins, unless you also want to include records where there isn't a computersystem record or processor record, but to me, you should have other exception reports to check up on that stuff (just my opinion).   Also, you only need to pull the first CPU record (deviceID0 = 'CPU0') to get the multi-core flag, this cuts down on duplicate records.
SELECT cs.Name0,
      cs.Model0,
      pr.ismulticore0,
      cs.numberofprocessors0
FROM dbo.v_R_System as sys
      INNER JOIN dbo.v_GS_Computer_System as cs
        ON sys.resourceID = cs.resourceID
      INNER JOIN dbo.v_GS_Processor as pr
        ON sys.resourceID = pr.resourceID
       AND pr.DeviceID0 = 'CPU0'
      INNER JOIN dbo.v_FullCollectionMembership as fc
        ON sys.resourceID = fc.resourceID
WHERE fc.collectionID = 'SMS000DS'
AND sys.decommissioned0 = 0
AND sys.obsolete0 = 0
AND sys.client0 = 1

Lemme know if this doesn't make sense or if you need more explanation.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jray)

Post #: 4

RE: Query for Processor - 2/5/2008 12:55:51 PM

cnackers

Posts: 435
Score: 15
Joined: 9/22/2006
From: Madison, WI
Status: offline

What service pack level are your running? Just curious.. i don't have the ismulticore0 column in my database...

_____________________________

My Blog:
http://myitforum.com/cs2/blogs/cnackers/default.aspx

(in reply to jnelson993)

Post #: 5

RE: Query for Processor - 2/5/2008 2:16:31 PM

cnackers

Posts: 435
Score: 15
Joined: 9/22/2006
From: Madison, WI
Status: offline

I would probably add v_gs_processor.name0 as well so you can see "Intel Xeon 2.80ghz" or whatever, but that's just me...

so for jnelson's query, just add a pr.name0 to the selects

_____________________________

My Blog:
http://myitforum.com/cs2/blogs/cnackers/default.aspx

(in reply to cnackers)

Post #: 6

RE: Query for Processor - 2/5/2008 2:40:20 PM

jnelson993

Posts: 1003
Score: 142
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

I've got SP3

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to cnackers)

Post #: 7

RE: Query for Processor - 2/5/2008 2:55:05 PM

cnackers

Posts: 435
Score: 15
Joined: 9/22/2006
From: Madison, WI
Status: offline

Ah, must be part of the new mof/extensions, I'm running SP2 still

_____________________________

My Blog:
http://myitforum.com/cs2/blogs/cnackers/default.aspx

(in reply to jnelson993)

Post #: 8

RE: Query for Processor - 2/5/2008 3:14:43 PM

evalasek

Posts: 22
Score: 0
Joined: 11/16/2007
Status: offline

My 2 cents worth, but I ran into the same issue. I figured it out with a report.
We had duplicated machine images to various new systems and they were duo core but Windows would only show one proc.

Here is my code that gets me the info I need:

SELECT DISTINCT SYS.Netbios_Name0, Processor.Name0, Processor.MaxClockSpeed0,Comp.Model0,
count(*) as "Num of Procs"
FROM v_R_System SYS
JOIN v_GS_PROCESSOR Processor on SYS.ResourceID = Processor.ResourceID
JOIN v_GS_COMPUTER_SYSTEM Comp on Comp.ResourceID = SYS.ResourceID
GROUP By SYS.Netbios_Name0 , Processor.Name0, Processor.MaxClockSpeed0, Comp.Model0
ORDER BY SYS.Netbios_Name0

Here is an example of the report. Notice that a GX620 show up as 1 proc when it should show up with 2.

_____________________________

Remember!
No matter where you go.
There you are!
Work Blog

(in reply to cnackers)

Post #: 9

RE: Query for Processor - 2/5/2008 3:36:26 PM

evalasek

Posts: 22
Score: 0
Joined: 11/16/2007
Status: offline

Let me amend the last line.
The Windows system reports 1 processor when we used a Ghost image built on a uniproc system.
When done from scratch or replace the 3 files with multi-proc info then it will report multi processor.

_____________________________

Remember!
No matter where you go.
There you are!
Work Blog

(in reply to evalasek)

Post #: 10

RE: Query for Processor - 2/6/2008 11:50:16 AM

jnelson993

Posts: 1003
Score: 142
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

That should work much of the time, however, something to consider...what if the machine is hyperthreaded? It shows up as 2 processors per CPU. Do you care? It's technically not "dual core"... I have a server with 4 single-core CPUs with multithreading turned on...your query shows 8, and there's no way to tell if they're real CPUs or virtual except perhaps by looking at the socket designation. The real CPUs have socket designation 1-4, but the multi-threaded virtual CPUs all seem to have socket 1. (this may or may not be turned on in your MOF)
SocketDesignation
CPU 1
CPU 2
CPU 3
CPU 4
CPU 1
CPU 1
CPU 1
CPU 1

...also, what if the machine has multiple CPUs that are non-hyperthreaded, non-dual-core? In your query it will show the number of real processors, but not show if any are dual / multi-core.

Just some things to think of...might not need to change a thing though.

I like the query overall except that the DISTINCT is unnecessary since the GROUP BY filters out the duplicates for you.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to evalasek)

Post #: 11