Stuck on query for software

[NetAdminNJ]

This is great software. I wish the report builder had a help tool of some kind in formulating SQL queries.

Here is my issue. I created a view of all servers in my domain using the view VIEW1. Now I want to report on all servers in my domain and report back if they have a certain software or not. I am not sure if this can be done.
I can get list of all servers containing software "XYZABC". But when do i not contain XYZABC I get all servers with listing of all software installed on them but not containing XYZABC. Is there a easier way to get this ?


SELECT
VIEW1.Computername,
VIEW1.BuildNumber,
VIEW1.Description,
VIEW1.InstallDate,
VIEW1.Manufacturer,
VIEW1.OSType,
VIEW1.ServicePackMajorVersion,
VIEW1.TotalVisibleMemorySize,
VIEW1.Domain,
VIEW1.DefaultIPGateway,
VIEW1.IPAddress,
tblSoftware.softwareName,
tblSoftware.softwareVersion
FROM
web30repMyMultiView VIEW1,
tblSoftware
WHERE
VIEW1.Computername = tblSoftware.Computername AND
tblSoftware.softwareName NOT LIKE 'XYZABC%'

Basically I am attempting to report on all the VIEW1 attributes plus report if software XYZABC is installed on the server or not.

Any help highly appreciated.

Cheers
NANJ

[Hemoco]

Should be somthing like this :

SELECT     dbo.tblComputers.Computername, CASE isnull(DERIVEDTBL.softwareName, '') WHEN '' THEN 'Not installed' ELSE 'Installed' END AS isinstalled
FROM         dbo.tblComputers LEFT OUTER JOIN
                          (SELECT     ComputerName, softwareName
                            FROM          dbo.tblSoftware
                            WHERE      (softwareName = 'Windows Internet Explorer 7')) DERIVEDTBL ON dbo.tblComputers.Computername = DERIVEDTBL.ComputerName

[NetAdminNJ]

Hi,

Is there any update to whether this can be done ?

[NetAdminNJ]

Yes. That is correct. On this and any other software detected I want to be able to get a field customized and report on whether that software exists or not.

As I see it for the future there will be other software that we will be auditing/deploying on a piece-by-piece basis so today it is software XYZ tomorrow it could be ABC.

Thanks

[Hemoco]

So basically you want some sort of field "installed : YES/NO"?

[NetAdminNJ]

Might I add that the software is already being identified by Lansweeper and I am able to go to the web console and look for it under Software reports.