cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Teebs88
Engaged Sweeper II
I have this report built out so far, but I must admit I know very little about SQL and regrettably I do not currently have the cycles to bring myself up to speed. How would I go about showing a count of Windows-based guests in the following report? I've tried a few different things with tblVmwareGuest.GuestFullName in the report builder but I can't figure out the proper syntax.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.NrProcessors As [Socket Count],
tblVmwareInfo.numCpuCores As [Core Count],
tblAssets.Processor As [CPU Model],
tblVmwareInfo.BiosVersion,
tblAssets.Memory As RAM,
tblVmwareInfo.Version As [ESXi Version],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Warrantydate As [Warranty Expiration]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%ESXi%Server%'
5 REPLIES 5
Teebs88
Engaged Sweeper II
Forgot to post an update - this is the final version we've been using for a while

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.NrProcessors As [Socket Count],
tblVmwareInfo.numCpuCores As [Core Count],
Count.count As [Win Guest Count],
tblAssets.Processor As [CPU Model],
tblVmwareInfo.BiosVersion,
tblAssets.Memory As RAM,
tblVmwareInfo.Version As [ESXi Version],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Warrantydate As [Warranty Expiration]
From tblAssets
Left Join (Select Distinct Top 1000000 Count(tblAssets.AssetID) As count,
tblAssets1.AssetName As Host,
tblVmwareGuest.GuestFullName
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblVmwareInfo On tblVmwareGuest.HostID =
tblVmwareInfo.VmwareID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblVmwareInfo.AssetID
Left Join tblVmwareVcenters On
tblAssets.AssetID = tblVmwareVcenters.AssetID And tblAssets1.AssetID =
tblVmwareVcenters.AssetID
Where tblVmwareGuest.GuestFullName Like '%indows%'
Group By tblAssets1.AssetName,
tblVmwareGuest.GuestFullName) As Count On
Count.Host = tblAssets.AssetName
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%ESXi%Server%'
Order By [Win Guest Count] Desc
Teebs88
Engaged Sweeper II
Gotcha. Played with it for a while and used tblVmwareGuest.GuestFullName instead and here's what I ended up with

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.NrProcessors As [Socket Count],
tblVmwareInfo.numCpuCores As [Core Count],
Count.count As [Win Guest Count],
tblAssets.Processor As [CPU Model],
tblVmwareInfo.BiosVersion,
tblAssets.Memory As RAM,
tblVmwareInfo.Version As [ESXi Version],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Warrantydate As [Warranty Expiration]
From tblAssets
Left Join (Select Distinct Top 1000000 Count(tblAssets.AssetID) As count,
tblAssets1.AssetName As Host,
tblVmwareGuest.GuestFullName
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblVmwareInfo On tblVmwareGuest.HostID =
tblVmwareInfo.VmwareID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblVmwareInfo.AssetID
Left Join tblVmwareVcenters On
tblAssets.AssetID = tblVmwareVcenters.AssetID And tblAssets1.AssetID =
tblVmwareVcenters.AssetID
Where tblVmwareGuest.GuestFullName Like '%indows%'
Group By tblAssets1.AssetName,
tblVmwareGuest.GuestFullName) As Count On
Count.Host = tblAssets.AssetName
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%ESXi%Server%'


Still trying to figure out how to get your original subquery in there as a second column but this is the information I needed for now.

Thanks!!
Esben_D
Lansweeper Employee
Lansweeper Employee
You'll have to edit the subquery. Basically I've added a report in the report to do the count of the guests. In order to change the count you'll have to edit that.

Left Join (Select Distinct Top 1000000 Count(tblAssets.AssetID) As count,
tblAssets1.AssetName As Host
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblVmwareInfo On tblVmwareGuest.HostID =
tblVmwareInfo.VmwareID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblVmwareInfo.AssetID
Left Join tblVmwareVcenters On
tblAssets.AssetID = tblVmwareVcenters.AssetID And tblAssets1.AssetID =
tblVmwareVcenters.AssetID
Group By tblAssets1.AssetName
) As Count On
Count.Host = tblAssets.AssetName


As you can see its just a normal small report. What you can do is create a new report and paste the subquery in. You can edit it so the count includes what you want and then put it back in the full report.

To limit it to Windows server, you'll have to add the table "tsysOS" to the subquery.
And the condition: Where tsysOS.OSname = 'Win 2016'
Esben_D
Lansweeper Employee
Lansweeper Employee
Ty the query below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.NrProcessors As [Socket Count],
tblVmwareInfo.numCpuCores As [Core Count],
tblAssets.Processor As [CPU Model],
tblVmwareInfo.BiosVersion,
tblAssets.Memory As RAM,
tblVmwareInfo.Version As [ESXi Version],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Warrantydate As [Warranty Expiration],
Count.count
From tblAssets
Left Join (Select Distinct Top 1000000 Count(tblAssets.AssetID) As count,
tblAssets1.AssetName As Host
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblVmwareInfo On tblVmwareGuest.HostID =
tblVmwareInfo.VmwareID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblVmwareInfo.AssetID
Left Join tblVmwareVcenters On
tblAssets.AssetID = tblVmwareVcenters.AssetID And tblAssets1.AssetID =
tblVmwareVcenters.AssetID
Group By tblAssets1.AssetName) As Count On
Count.Host = tblAssets.AssetName
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%ESXi%Server%'
Teebs88
Engaged Sweeper II
Excellent, thank you! This is like 98% of the way to where I need it to be, I'm seeing a total guest count column now. Where would I insert a "like %Windows%" query to limit the count to guests with the Full Name of Windows Server 20**? Or even have two columns for total guest count and Windows guest count?



Charles.X wrote:
Ty the query below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.NrProcessors As [Socket Count],
tblVmwareInfo.numCpuCores As [Core Count],
tblAssets.Processor As [CPU Model],
tblVmwareInfo.BiosVersion,
tblAssets.Memory As RAM,
tblVmwareInfo.Version As [ESXi Version],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Warrantydate As [Warranty Expiration],
Count.count
From tblAssets
Left Join (Select Distinct Top 1000000 Count(tblAssets.AssetID) As count,
tblAssets1.AssetName As Host
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblVmwareInfo On tblVmwareGuest.HostID =
tblVmwareInfo.VmwareID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblVmwareInfo.AssetID
Left Join tblVmwareVcenters On
tblAssets.AssetID = tblVmwareVcenters.AssetID And tblAssets1.AssetID =
tblVmwareVcenters.AssetID
Group By tblAssets1.AssetName) As Count On
Count.Host = tblAssets.AssetName
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%ESXi%Server%'