The information about processors, cores and logical processors is a bit confusing. However, since it may be required for Windows and SQL server licensing [1], below some queries and links.
Get details about SQL Server installations:
select distinct fcm.Name as [Machine Name], arp.DisplayName0, sys.Active0 , WS.LastHWScan as [Last Inventory],
sys.CPUType0 as [CPU Type], p.Name0 as [Name],
(Select count(SystemName0) from v_GS_PROCESSOR where Systemname0 = fcm.Name group by SystemName0) as [# Processors (Sockets)],
p.NumberOfCores0 as [# Cores],p.NumberOfLogicalProcessors0 as [# Logical Processors]
from v_GS_PROCESSOR P ,v_FullCollectionMembership fcm
left join v_GS_OPERATING_SYSTEM as OS on OS.ResourceID = fcm.ResourceID
left join v_R_System as sys on sys.ResourceID = fcm.ResourceID
left join v_GS_COMPUTER_SYSTEM as cs on cs.ResourceID = fcm.ResourceID
left join v_GS_WORKSTATION_STATUS as WS on WS.ResourceID = fcm.ResourceID
left join v_Add_Remove_Programs as arp on arp.ResourceID = fcm.ResourceID
where (fcm.ResourceID=p.ResourceID
and fcm.CollectionID='SMSDM003'
and (arp.DisplayName0 like 'Microsoft SQL Server 2008' or
arp.DisplayName0 like 'Microsoft SQL Server 2012' or
arp.DisplayName0 like 'Microsoft SQL Server 2012 (64-bit)' or
arp.DisplayName0 like 'Microsoft SQL Server 2014' or
arp.DisplayName0 like 'Microsoft SQL Server 2014 (64-bit)' or
arp.DisplayName0 like 'Microsoft SQL Server 2016 (64-bit)' or
arp.DisplayName0 like 'Microsoft SQL Server 2017 (64-bit)' or
arp.DisplayName0 like 'Microsoft SQL Server 2019 (64-bit)' or
arp.DisplayName0 like 'Microsoft SQL Server 2022 (64-bit)')
)
order by fcm.name
You can download the same query as report here.
Mind that you can’t query the SQL server edition (Express, Standard, Enterprise) from ConfigMgr, therefore you might want to use PowerShell [2] or run select * from @@version from SSMS.
To interpret the result, open msinfo32.exe:
For the server marked yellow, you see that it has 2 processors with 4 cores each. If you open resource monitor, you see CPU 0 to CPU 7.
Below is a similar query showing OS Information combined with processors, cores and logical processors.
select distinct fcm.Name as [Machine Name], OS.Caption0 as [OS], OS.Version0 as [OS Version], sys.Active0 , WS.LastHWScan as [Last Inventory],
sys.CPUType0 as [CPU Type], p.Name0 as [Name],(Select count(SystemName0)
from v_GS_PROCESSOR where Systemname0 = fcm.Name group by SystemName0) as [# Processors (Sockets)],p.NumberOfCores0 as [# Cores],
p.NumberOfLogicalProcessors0 as [# Logical Processors]from v_GS_PROCESSOR P ,v_FullCollectionMembership fcm
left join v_GS_OPERATING_SYSTEM as OS on OS.ResourceID = fcm.ResourceID
left join v_R_System as sys on sys.ResourceID = fcm.ResourceID
left join v_GS_COMPUTER_SYSTEM as cs on cs.ResourceID = fcm.ResourceID
left join v_GS_WORKSTATION_STATUS as WS on WS.ResourceID = fcm.ResourceID
left join v_Add_Remove_Programs as arp on arp.ResourceID = fcm.ResourceID
where (fcm.ResourceID=p.ResourceID
and fcm.CollectionID='SMSDM003')
If you are as confused as I was by all the terms that appear in context with CPUs (sockets, processors, cores, processor threads, logical processors), read the article from XTIVIA [3].
Links:
[1] About SQL Server
https://www.microsoft.com/en-us/licensing/product-licensing/sql-server?rtc=1&activetab=sql-server-pivot:primaryr2
[2] Script to get CPU and Cores for SQL Server Licensing
https://www.mssqltips.com/sqlservertip/3045/script-to-get-cpu-and-cores-for-sql-server-licensing/
[3] Sockets, Cores, and Threads, oh my!
Sockets, Cores, and Threads, oh my! (virtual-dba.com)