Report filtering on sotware

[voncollr]

I need to run a report like this on but add AVG and Atempo, so the computers displayed must have both pieces of software.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblSoftwareUni.softwareName Like '%Avg%'
Order By tblAssets.AssetName

[Nick_VDB · Accepted solution]

Instructions for adding this report to your Lansweeper installation can be found here. This report will require you use SQL Server as it is uses sub-queries.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
Atempo.Atempo,
Atempo.[Atempo Version],
AVG.AVG,
AVG.[AVG Version]
From tblAssets
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join (Select Top 1000000 tblSoftware.AssetID,
tblSoftwareUni.softwareName As Atempo,
tblSoftware.softwareVersion As [Atempo Version]
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Atempo%') As Atempo
On Atempo.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblSoftware.AssetID,
tblSoftwareUni.softwareName As AVG,
tblSoftware.softwareVersion As [AVG Version]
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Avg%') As AVG On AVG.AssetID =
tblAssets.AssetID
Where AVG.AssetID Is Not Null And Atempo.AssetID Is Not Null
Order By tblAssets.AssetName

[Cam]

Oh yes, I think the And would only apply if a pieces of software would have both AVG & Atempo in the title. I'm going to have to let the experts answer this for you, sorry.

[voncollr]

Yes they are

[Cam]

If you go into a specific computer with both pieces of software, are both pieces of software listed under the software tab?

[voncollr]

Tried that but did not reveal any results but I know for a fact there are computers out there with both pieces of software installed

[Cam]

Just change your line to this:

Where tblSoftwareUni.softwareName Like '%Avg%' And tblSoftwareUni.softwareName Like '%Atempo%'