Notification

Icon
Error

VMware report: # of Windows Guests

Posted: Wednesday, January 9, 2019 9:33:42 PM(UTC)
Teebs88

Teebs88

Member Original PosterPosts: 9
0
Like
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.

Quote:
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%'
Esben.D
#1Esben.D Member Administration Posts: 1,683  
posted: 1/14/2019 11:55:18 AM(UTC)
Ty the query below:

Code:
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
#2Teebs88 Member Original PosterPosts: 9  
posted: 1/14/2019 3:36:04 PM(UTC)
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?



Originally Posted by: Charles.X Go to Quoted Post
Ty the query below:

Code:
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%'
Esben.D
#3Esben.D Member Administration Posts: 1,683  
posted: 1/14/2019 6:14:24 PM(UTC)
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.

Quote:
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'
Teebs88
#4Teebs88 Member Original PosterPosts: 9  
posted: 1/15/2019 12:26:43 AM(UTC)
Gotcha. Played with it for a while and used tblVmwareGuest.GuestFullName instead and here's what I ended up with

Code:
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!!
Teebs88
#5Teebs88 Member Original PosterPosts: 9  
posted: 3/18/2019 8:32:47 PM(UTC)
Forgot to post an update - this is the final version we've been using for a while

Code:
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

Active Discussions

Lansweeper Changing/Editing tblComputerSystem.DomainRole
by  Esben.D   Go to last post Go to first unread
Last post: Today at 2:41:17 PM(UTC)
Lansweeper Version Check Page
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 2:37:43 PM(UTC)
Lansweeper warranty information missing
by  Esben.D   Go to last post Go to first unread
Last post: Today at 2:33:03 PM(UTC)
Lansweeper Dell Switch Servicetag
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 2:12:22 PM(UTC)
Lansweeper Office 365 ProPlus not detected in a few PCs
by  Esben.D   Go to last post Go to first unread
Last post: Today at 2:10:44 PM(UTC)
Lansweeper Imageright Installation
by  CyberCitizen  
Go to last post Go to first unread
Last post: Today at 1:01:30 AM(UTC)
Lansweeper Is the conditional step for OS arch broken?
by  CyberCitizen   Go to last post Go to first unread
Last post: Today at 1:00:54 AM(UTC)
Lansweeper http to https complicated?
by  Lewis_Spokane  
Go to last post Go to first unread
Last post: 6/22/2019 12:33:31 AM(UTC)