→ 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: 
cbtg2006
Engaged Sweeper
I'm looking to create a report that report illustrates the physical/virtual ratio in our estate. I've created the following query, however, it will not work as a chart due to the fact it returns a single row:

Select Distinct Top 1000000  SUM(Case tblAssetCustom.Model
When 'VMware Virtual Platform' Then 1 When 'Virtual Machine' Then 1 Else 0 End) As 'Virtual Servers',
SUM(Case tblAssetCustom.Model
When 'VMware Virtual Platform' Then 0 When 'Virtual Machine' Then 0 Else 1 End) As 'Physical Servers'
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1


Is anyone able to help me translate this into a chart friendly query?

Thanks,

Chris
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Something like this?

SELECT DISTINCT TOP 1000000 CASE tblAssetCustom.Model WHEN 'VMware Virtual Platform' THEN 'Virtual' WHEN 'Virtual Machine' THEN 'Virtual' ELSE 'Physical' END
AS Server, COUNT(*) AS Total
FROM tblComputersystem INNER JOIN
tblAssets ON tblComputersystem.AssetID = tblAssets.AssetID INNER JOIN
tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
WHERE (tblComputersystem.Domainrole > 1) AND (tblAssetCustom.State = 1)
GROUP BY CASE tblAssetCustom.Model WHEN 'VMware Virtual Platform' THEN 'Virtual' WHEN 'Virtual Machine' THEN 'Virtual' ELSE 'Physical' END

View solution in original post

2 REPLIES 2
Hemoco
Lansweeper Alumni
Something like this?

SELECT DISTINCT TOP 1000000 CASE tblAssetCustom.Model WHEN 'VMware Virtual Platform' THEN 'Virtual' WHEN 'Virtual Machine' THEN 'Virtual' ELSE 'Physical' END
AS Server, COUNT(*) AS Total
FROM tblComputersystem INNER JOIN
tblAssets ON tblComputersystem.AssetID = tblAssets.AssetID INNER JOIN
tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
WHERE (tblComputersystem.Domainrole > 1) AND (tblAssetCustom.State = 1)
GROUP BY CASE tblAssetCustom.Model WHEN 'VMware Virtual Platform' THEN 'Virtual' WHEN 'Virtual Machine' THEN 'Virtual' ELSE 'Physical' END
Lansweeper wrote:
Something like this?

SELECT DISTINCT TOP 1000000 CASE tblAssetCustom.Model WHEN 'VMware Virtual Platform' THEN 'Virtual' WHEN 'Virtual Machine' THEN 'Virtual' ELSE 'Physical' END
AS Server, COUNT(*) AS Total
FROM tblComputersystem INNER JOIN
tblAssets ON tblComputersystem.AssetID = tblAssets.AssetID INNER JOIN
tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
WHERE (tblComputersystem.Domainrole > 1) AND (tblAssetCustom.State = 1)
GROUP BY CASE tblAssetCustom.Model WHEN 'VMware Virtual Platform' THEN 'Virtual' WHEN 'Virtual Machine' THEN 'Virtual' ELSE 'Physical' END


Perfect, thank you!