How do I create a report with the total number of assets

[searerg]

How do I create a report with the total number of assets. I want to create a report that shows VMWare Hosts names with the number of guest each host has. I don't want the name of the vmguest just the total per host.

EX:
Host Name No Of Guest
ESX001 10
ESX002 15

I would also like to define the Host based on if they are Development servers or Production servers. I can sort them based on Asset Groups but cant get the group name to show on the report.

Final goal is to make a chart for management to see.

This is what I am currently using but need to total guests

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblVmwareInfo.Version,
tblVmwareGuest.Name As Guest,
tblVmwareGuest.Memory,
tblVmwareGuest.IsRunning As State,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblVmwareGuest.lastchanged,
tblAssetGroups.AssetGroup
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetGroups.AssetGroup = N'JTAX\VMware Servers'
Order By tblAssets.AssetName,
Guest

Thanks

[searerg]

Thank you for your reply. I see try it now.

[Susan_A · Accepted solution]

I've included a sample report below that counts the number of guest machines per VMware host.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  Count(tblVmwareGuest.GuestID) As GuestCount
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Where tsysAssetTypes.AssetTypename = 'vmware server' And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10,
  tsysAssetTypes.AssetTypename,
  tblAssetCustom.State
Order By tblAssets.AssetName