Report for VMWare Server Listings with a twist

[jonathancw]

I currently have a report that gives me most of the information I need, however I am having an issue showing only VMs with a Netbackup Agent installed on them. Could someone possibly look at my code and tell me how I can improve it to list the details I'm needing:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblOperatingsystem.Caption,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  Case When tblAssetCustom.Manufacturer Like '%vmware%' Or
    tblAssetCustom.Model Like '%virtual%' Or
    tblAssetCustom.Serialnumber Like '%virtual%' Then 'Virtual' Else 'Physical'
  End As Type,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblVmwareGuestNetwork On tblVmwareGuestNetwork.MacAddress =
    tblAssets.Mac
  Left Join tblVmwareGuest On tblVmwareGuestNetwork.GuestID =
    tblVmwareGuest.GuestID
  Left Join tblAssets tVMwareHost On tVMwareHost.AssetID =
    tblVmwareGuest.AssetID
  Left Join tblVmwareInfo On tblVmwareInfo.AssetID = tVMwareHost.AssetID
  Left Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Left Join (Select tblProcessor.AssetID,
    Sum(tblProcessor.NumberOfLogicalProcessors) As [Count logical CPUs],
    Sum(tblProcessor.NumberOfCores) As [Count CPU cores],
    Count(tblProcessor.Caption) As [Count physical CPUs]
  From tblProcessor
  Group By tblProcessor.AssetID) CPUCount On tblAssets.AssetID =
    CPUCount.AssetID
  Left Join (Select tblVmwareInfo.AssetID,
    tblVmwareInfo.numCpuThreads As [Count logical CPUs],
    tblVmwareInfo.numCpuCores As [Count CPU cores],
    tblVmwareInfo.numCpuPkgs As [Count physical CPUs]
  From tblVmwareInfo) CPUCountVMware On tVMwareHost.AssetID =
    CPUCountVMware.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
Order By tblAssets.AssetName

I only need to see VMs with Netbackup agent installed.

[Daniel_B]

Apologies, there was a mistake. I changed my last post. Could you use the updated report instead.

[Daniel_B · Accepted solution]

In case the agent you would like to look for is listed under Control Panel - Programs and Features on the client machines, use a subquery which lists assets having the software installed. Please find an example below.
If the agent is not listed as installed software, you might need to submit the path of its executable on client computers for Custom File Scanning. After rescanning your network, create a report which lists assets having this file present. File properties scanned by Custom file scanning are stored in tblFileVersions.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblOperatingsystem.Caption,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  Case When tblAssetCustom.Manufacturer Like '%vmware%' Or
    tblAssetCustom.Model Like '%virtual%' Or
    tblAssetCustom.Serialnumber Like '%virtual%' Then 'Virtual' Else 'Physical'
  End As Type,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblVmwareGuestNetwork On tblVmwareGuestNetwork.MacAddress =
    tblAssets.Mac
  Left Join tblVmwareGuest On tblVmwareGuestNetwork.GuestID =
    tblVmwareGuest.GuestID
  Left Join tblAssets tVMwareHost On tVMwareHost.AssetID =
    tblVmwareGuest.AssetID
  Left Join tblVmwareInfo On tblVmwareInfo.AssetID = tVMwareHost.AssetID
  Left Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Left Join (Select tblProcessor.AssetID,
    Sum(tblProcessor.NumberOfLogicalProcessors) As [Count logical CPUs],
    Sum(tblProcessor.NumberOfCores) As [Count CPU cores],
    Count(tblProcessor.Caption) As [Count physical CPUs]
  From tblProcessor
  Group By tblProcessor.AssetID) CPUCount On tblAssets.AssetID =
    CPUCount.AssetID
  Left Join (Select tblVmwareInfo.AssetID,
    tblVmwareInfo.numCpuThreads As [Count logical CPUs],
    tblVmwareInfo.numCpuCores As [Count CPU cores],
    tblVmwareInfo.numCpuPkgs As [Count physical CPUs]
  From tblVmwareInfo) CPUCountVMware On tVMwareHost.AssetID =
    CPUCountVMware.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1 And
  tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
      tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like 'Example software name %')
Order By tblAssets.AssetName

[jonathancw]

Thanks Daniel for the reply. When I put the application name as it appears in Add/ Remove, it gives me no results when I run the report.

Daniel.B wrote:
In case the agent you would like to look for is listed under Control Panel - Programs and Features on the client machines, use a subquery which lists assets having the software installed. Please find an example below.
If the agent is not listed as installed software, you might need to submit the path of its executable on client computers for Custom File Scanning. After rescanning your network, create a report which lists assets having this file present. File properties scanned by Custom file scanning are stored in tblFileVersions.

[jonathancw]

Anyone have any thoughts on this?