cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
usfitdci
Engaged Sweeper
Below I have pasted my code in. I'm trying to get a report on the versions of 2 different software apps. 1 is Symantec and the other is our TSM backup. Some or both might not have the software installed. That is 1 thing I'm looking for also. The problem I'm running into is if for example TSM is not installed on a server, the server in not even on the report. I would like to see all servers even if the software is not installed.

Appreciate your help.


Select Top 1000000 tblComputers.Computer, tblComputers.Domain,
tblSoftware.softwareVersion As [TSM Version], tblSoftware1.softwareVersion As
[Symantec Version]
From tblComputers Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
tblSoftware tblSoftware1 On tblComputers.Computername =
tblSoftware1.ComputerName
Where tblSoftware.softwareName = 'IBM Tivoli Storage Manager Client' And
tblSoftware1.softwareName = 'Symantec Endpoint Protection'
Order By tblComputers.Computer
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
try this:

Select Top 1000000 tblComputers.Computername, tblComputers.Computer,
tblComputers.Domain, tbl2.softwareName, tbl3.softwareName
From tblComputers Left Join
(Select tblSoftware.ComputerName, tblSoftware.softwareName
From tblcomputers Inner Join
tblSoftware On tblcomputers.Computername = tblSoftware.ComputerName
Where tblSoftware.softwareName Like '%visio%') tbl2 On
tblComputers.Computername = tbl2.ComputerName Left Join
(Select tblSoftware.ComputerName, tblSoftware.softwareName
From tblcomputers Inner Join
tblSoftware On tblcomputers.Computername = tblSoftware.ComputerName
Where tblSoftware.softwareName Like '%office%') tbl3 On
tblComputers.Computername = tbl3.ComputerName
Order By tblComputers.Computer

View solution in original post

4 REPLIES 4
Hemoco
Lansweeper Alumni
try this:

Select Top 1000000 tblComputers.Computername, tblComputers.Computer,
tblComputers.Domain, tbl2.softwareName, tbl3.softwareName
From tblComputers Left Join
(Select tblSoftware.ComputerName, tblSoftware.softwareName
From tblcomputers Inner Join
tblSoftware On tblcomputers.Computername = tblSoftware.ComputerName
Where tblSoftware.softwareName Like '%visio%') tbl2 On
tblComputers.Computername = tbl2.ComputerName Left Join
(Select tblSoftware.ComputerName, tblSoftware.softwareName
From tblcomputers Inner Join
tblSoftware On tblcomputers.Computername = tblSoftware.ComputerName
Where tblSoftware.softwareName Like '%office%') tbl3 On
tblComputers.Computername = tbl3.ComputerName
Order By tblComputers.Computer
usfitdci
Engaged Sweeper
Here is what I have so far. I just don't know how to add the other software version column for the other software named "IBM Tivoli Storage Manager Client".

Select Top 1000000 tblComputers.Computer, tblComputers.Domain,
tbl2.softwareVersion As [Symantec Version]
From tblComputers Left Join
(Select tblSoftware.ComputerName, tblSoftware.softwareName,
tblSoftware.softwareVersion
From tblcomputers Inner Join
tblSoftware On tblcomputers.Computername = tblSoftware.ComputerName
Where tblSoftware.softwareName Like '%Symantec Endpoint Protection%') tbl2
On tblComputers.Computername = tbl2.ComputerName
Order By tblComputers.Computer

Thanks for your help.
usfitdci
Engaged Sweeper
How to I adjust this to show versions and more then one software.
Hemoco
Lansweeper Alumni
This is an example for Visio.

It shows all computers and in the software column you see if the software is installed:

Select Top 1000000 tblComputers.Computername, tblComputers.Computer,
tblComputers.Domain, tbl2.softwareName
From tblComputers Left Join
(Select tblSoftware.ComputerName, tblSoftware.softwareName
From tblcomputers Inner Join
tblSoftware On tblcomputers.Computername = tblSoftware.ComputerName
Where tblSoftware.softwareName Like '%visio%') tbl2 On
tblComputers.Computername = tbl2.ComputerName
Order By tblComputers.Computer

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now