Report - Terminal Server software version comparison

[bitos]

Hi,

We recently bought lansweeper and are pretty happy with it! One of the reasons why we bought it was because of custom reports that could be made. However I'm no SQL expert, so I'm struggling a little to get what I want.

We have 3 terminal servers, and I want a report that can give me a quick overview of all the software that is installed and an overview of the version per server. If a particular server does not have the software installed, i'd like it to say "(Not installed)".

I'd like the rows of the report to look like this:

Software Name | Software Publisher | Version (Server1) | Version (Server2) | Version (Server3)

I managed to get something going, but the report only shows the versions of the software if the software is installed on all three servers. So if a package is installed on only one or two servers, it doesn't show up in the list.

The SQL I have right now looks like this:

Select Distinct Top 10000 tblSoftwareUni.softwareName,
SERVER1.softwareVersion As [version SERVER1],
SERVER2.softwareVersion As [version SERVER2],
SERVER3.softwareVersion As [version LAVSERVER3]
From tblSoftware As SERVER1
Inner Join tblSoftwareUni On SERVER1.softID = tblSoftwareUni.SoftID
Inner Join tblSoftware As SERVER2 On SERVER2.softID = tblSoftwareUni.SoftID
Inner Join tblSoftware As SERVER3 On SERVER3.softID = tblSoftwareUni.SoftID
Where SERVER1.AssetID = 5 And SERVER2.AssetID = 8 And SERVER3.AssetID = 17
Order By tblSoftwareUni.softwareName

Could someone point me in the right direction? Any help would be much appreciated!

[Hemoco]

A sample report can be seen below.

Select Top 1000000 tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
Case When SubQuery1.softwareName Is Null Then 'not installed'
When SubQuery1.softwareName Is Not Null And SubQuery1.softwareVersion Is Not
Null Then SubQuery1.softwareVersion End As SERVER1,
Case When SubQuery2.softwareName Is Null Then 'not installed'
When SubQuery2.softwareName Is Not Null And SubQuery2.softwareVersion Is Not
Null Then SubQuery2.softwareVersion End As SERVER2,
Case When SubQuery3.softwareName Is Null Then 'not installed'
When SubQuery3.softwareName Is Not Null And SubQuery3.softwareVersion Is Not
Null Then SubQuery3.softwareVersion End As SERVER3
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join (Select tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftware.AssetID = 5) SubQuery1 On SubQuery1.softwareName =
tblSoftwareUni.softwareName And SubQuery1.SoftwarePublisher =
tblSoftwareUni.SoftwarePublisher
Left Join (Select tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftware.AssetID = 😎 SubQuery2 On SubQuery2.softwareName =
tblSoftwareUni.softwareName And SubQuery2.SoftwarePublisher =
tblSoftwareUni.SoftwarePublisher
Left Join (Select tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftware.AssetID = 17) SubQuery3 On SubQuery3.softwareName =
tblSoftwareUni.softwareName And SubQuery3.SoftwarePublisher =
tblSoftwareUni.SoftwarePublisher
Where (tblSoftware.AssetID = 5) Or
(tblSoftware.AssetID = 😎 Or
(tblSoftware.AssetID = 17)
Order By tblSoftwareUni.softwareName