Software Report

[usfitdci]

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

[Hemoco · Accepted solution]

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]

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]

How to I adjust this to show versions and more then one software.

[Hemoco]

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