→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

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

View solution in original post

4 REPLIES 4
Daniel_B
Lansweeper Alumni
Apologies, there was a mistake. I changed my last post. Could you use the updated report instead.
Daniel_B
Lansweeper Alumni
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
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
Engaged Sweeper
Anyone have any thoughts on this?