Number of CPU cores and one result per server

[jaekob]

Hi!

For licensing reasons we need a report that lists SQL servers along with number of total CPU cores.
I've modified a default SQL report to show total number of cores. This works fine but when a server has more than one processor the same server will be listed several times.
How can I modify this report to get one result per SQL server along with grouped number of cores, regardless of the number of processors?

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tsysOS.Image As icon,
  tblSqlServers.displayVersion As Edition,
  tblSqlServers.skuName As License,
  tblSqlServers.spLevel As SP,
  tblSqlServers.serviceName As Service,
  tblAssets.NrProcessors As Processors,
  tblProcessor.NumberOfCores * tblAssets.NrProcessors As TotalCores,
  tblSqlServers.lastChanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tblAssets.Domain In tblSqlServers.skuName Not Like '%xpress%' And tblSqlServers.skuName Not Like
  '%Windows internal%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  Edition

[jaekob]

Works great, thanks!

[AdmJLovejoy · Accepted solution]

SELECT DISTINCT Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tblSqlServers.displayVersion As Edition,
  tblSqlServers.skuName As License,
  tblSqlServers.spLevel As SP,
  tblSqlServers.serviceName As Service,
  tblAssets.NrProcessors As Processors,
  tblProcessor.NumberOfCores * tblAssets.NrProcessors As TotalCores,
  tblSqlServers.lastChanged,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tblSqlServers.skuName Not Like '%xpress%' And 
      tblSqlServers.skuName Not Like '%Windows internal%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName, Edition