→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jaekob
Engaged Sweeper
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
1 ACCEPTED SOLUTION
AdmJLovejoy
Champion Sweeper
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
Thanks, Jim Lovejoy __________________________________________________________________________________________________ James W. Lovejoy | IBM - Cloud Managed Services Delivery | Infrastructure Architect (Windows Server ...

View solution in original post

2 REPLIES 2
jaekob
Engaged Sweeper
Works great, thanks!
AdmJLovejoy
Champion Sweeper
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
Thanks, Jim Lovejoy __________________________________________________________________________________________________ James W. Lovejoy | IBM - Cloud Managed Services Delivery | Infrastructure Architect (Windows Server ...