Where is "No of CPUs" stored in the database?

Hi,

I am afraid this information is not stored in database directly but it is computed from table CPUMultiLoad_Detail. There are columns NodeID and CPUIndex - so number of distinct CPUIndex values for one NodeID is equal to number of CPUs on given node.

Of course in order to get best performance it is good approach to get this information from last polled values for given NodeID.

I was afraid that is how it was being done.

Would it be possible for you to provide me the query that is being used to generate this?

Hi,

this query get number of CPUs for node with NodeID = 4:

SELECT COUNT(DISTINCT CPUIndex) FROM CPUMultiLoad_Detail WITH(NOLOCK)
WHERE
 NodeID = 4 AND TimeStampUTC = (SELECT MAX(TimeStampUTC) FROM CPUMultiLoad_Detail WITH(NOLOCK) WHERE NodeID = 4)

It tries to get this information from last polled values on node. For common nodes number of CPUs is always constant so it should be normally no issue. If there were node with dynamic number of CPUs it is up to you how to compute, anyway last polled values should be considered to be valid. My query use CPUMultiLoad_Detail table which should be good enough. In theory correct query should use CPUMultiLoad view - but such query will be little bit slower - so again it is up to you. My version won't work only in situation when all data for given node from table CPUMultiLoad_Detail were removed.

This query returns number of CPUs for all nodes. It doesnt use last polled values but it goes through all data (it is usually much more faster).
 
SELECT NodeID, COUNT(DISTINCT CPUIndex) FROM CPUMultiLoad_Detail WITH(NOLOCK)
GROUP BY
 NodeID

This is awesome, thank you so much!

Hi Byron-

Were you able to modify this to get a complete list of Nodes with their CPU count?  I cannot figure out how to inner join these with an existing report.

Any help is appreciated! 

Thanks,

Mike

Here is what I was able to cobble together, let me know how this works out...

SELECT N.Caption, COUNT(DISTINCT CPUIndex)

FROM CPUMultiLoad_Detail CPU WITH(NOLOCK)

JOIN Nodes N ON CPU.NodeID = N.NodeID

GROUP BY

N.Caption

Order By

N.Caption

DISCLAIMER: I am not a SQL Guru, use this at your own risk. 

You ROCK!  Works great, thanks Byron.