‎12-15-2013 12:09 PM
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
Solved! Go to Solution.
‎12-15-2013 03:15 PM
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
‎12-15-2013 03:15 PM
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
‎12-15-2013 03:56 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now