→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
searerg
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
searerg
Engaged Sweeper II
Thank you for your reply. I see try it now.
Susan_A
Lansweeper Alumni
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