→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
zberkshier91
Engaged Sweeper II
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?
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
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

View solution in original post

2 REPLIES 2
zberkshier91
Engaged Sweeper II
Sweet! That did it. Thanks for the help!
Nick_VDB
Champion Sweeper III
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