This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject

→ 🚀Are you a Lansweeper Champion?! Join our Contributor Program Sign up here!

Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

VMware report: # of Windows Guests

Teebs88
Engaged Sweeper II

‎01-09-2019 09:33 PM

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%'
Labels:
0 Kudos
5 REPLIES 5
Teebs88
Engaged Sweeper II

‎03-18-2019 08:32 PM

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
0 Kudos
Teebs88
Engaged Sweeper II

‎01-15-2019 12:26 AM

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!!
0 Kudos
Esben_D
Lansweeper Employee
Lansweeper Employee

‎01-14-2019 06:14 PM

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'
0 Kudos
Esben_D
Lansweeper Employee
Lansweeper Employee

‎01-14-2019 11:55 AM

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%'
0 Kudos
Teebs88
Engaged Sweeper II
In response to Esben_D

‎01-14-2019 03:36 PM

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%'
0 Kudos

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now