→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

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

View solution in original post

6 REPLIES 6
Nick_VDB
Champion Sweeper III
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
Engaged Sweeper II
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
Engaged Sweeper
Yes they are
Cam
Engaged Sweeper II
If you go into a specific computer with both pieces of software, are both pieces of software listed under the software tab?
voncollr
Engaged Sweeper
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
Engaged Sweeper II
Just change your line to this:

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