Report for PCs with selected software installed

[zberkshier91]

Hello,

I'm trying to create a report that pulls information on computers with Carbon Black and Bit9 Agent installed. Here is the query I've tried creating

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where tblSoftwareUni.softwareName Like 'Carbon%') And
  tblSoftwareUni.softwareName Like 'Bit9%'
Order By tblAssets.AssetName

Anyone have a second set of eyes to let me know what I'm doing wrong?

[zberkshier91]

Sweet! That did it. Thanks for the help!

[Nick_VDB · Accepted solution]

You can use the report below to check for assets that have both Carbon Black and Bit9 installed. Follow the steps found here to add the report to Lansweeper.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID In (Select Top 1000000 tblAssets.AssetID
  From tblAssets Inner Join tblAssetCustom On tblAssets.AssetID =
      tblAssetCustom.AssetID Inner Join tsysAssetTypes
      On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tblSoftware
      On tblAssets.AssetID = tblSoftware.AssetID Inner Join tblSoftwareUni
      On tblSoftwareUni.SoftID = tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Carbon%') And
  tblAssets.AssetID In (Select Top 1000000 tblAssets.AssetID
  From tblAssets Inner Join tblAssetCustom On tblAssets.AssetID =
      tblAssetCustom.AssetID Inner Join tsysAssetTypes
      On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tblSoftware
      On tblAssets.AssetID = tblSoftware.AssetID Inner Join tblSoftwareUni
      On tblSoftwareUni.SoftID = tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Bit9%') And
  tblAssetCustom.State = 1