show PCs with SomeSoftware that is not installed

[harringg]

Now, I'm looking for a reverse query, one that shows which PC's don't have a given piece of software. This report will show me them, but also includes all the other software installed, making the report unmanageable.

How would I modify it to include just computers that do not have "softwareX"

computer1 softwareA
computer1 softwareB
computer1 softwareC
computer1 softwareD
computer2 softwareE
computer2 softwareG
computer2 softwareL
computer2 softwareM

so it just shows:
computer1 (I can hide this column in the final report)
computer2 (I can hide this column in the final report)

SELECT TOP 1000000 tblcomputers.computername,
                   tblcomputers.computerunique,
                   web40osname.compimage                         AS icon,
                   web40osname.osname,
                   tblcomputers.sp                               AS
                   [Service  Pack],
                   tblcompcustom.location,
                   tblcompcustom.department                      AS
                   [Research Unit],
                   tblcompcustom.custom1                         AS [User Name],
                   tsyschassistypes.chassisname                  AS Type,
                   tblcompcustom.barcode                         AS
                   [Inventory Number],
                   CONVERT(VARCHAR(10), tblbios.releasedate, 20) AS ReleaseDate,
                   tblsystemenclosure.serialnumber,
                   tblsoftware.softwarename,
                   tblsoftware.softwareversion,
                   tblsoftware.softwarepublisher
FROM   tblcomputers
       LEFT OUTER JOIN web40processorcapacity
                    ON tblcomputers.computername =
                       web40processorcapacity.computername
       INNER JOIN web40activecomputers
               ON tblcomputers.computername = web40activecomputers.computername
       LEFT JOIN web40correctmemory
              ON web40correctmemory.computername = tblcomputers.computername
       LEFT JOIN web40osname
              ON web40osname.computername = tblcomputers.computername
       LEFT JOIN tblcomputersystemproduct
              ON tblcomputers.computername =
                 tblcomputersystemproduct.computername
       LEFT JOIN tblcompcustom
              ON tblcomputers.computername = tblcompcustom.computername
       LEFT JOIN tblbios
              ON tblcomputers.computername = tblbios.computername
       LEFT JOIN tblsystemenclosure
              ON tblcomputers.computername = tblsystemenclosure.computername
       LEFT JOIN tsyschassistypes
              ON tsyschassistypes.chassistype = tblsystemenclosure.chassistypes
       INNER JOIN tblsoftware
               ON tblcomputers.computername = tblsoftware.computername
WHERE  tblsoftware.softwarepublisher NOT LIKE '%softwareX%'
ORDER  BY dbo.tblcomputers.computername  

[ABECU]

Thanks, that pointed me in the right direction.

[ABECU]

I'm struggling with a similar issue. I'd like to have a report that tells me which laptops do not have Symantec Endpoint Encryption installed. I've managed to make a report that shows me all laptops that do have it installed, but I cannot seem to figure out how to get the reverse. I'm not super fluent with SQL, so I assume I'm missing something simple.

This is the code that gives me the machines with encryption:

 Select Top 1000000 tblComputers.Computer, tblSoftware.softwareName, tblSoftware.softwareVersion From tblComputers Inner Join tblSystemEnclosure On tblComputers.Computername = tblSystemEnclosure.Computername Inner Join tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Where tblSoftware.softwareName Like 'Symantec Endpoint Encryption%' And tblSystemEnclosure.ChassisTypes = 10 Order By tblSoftware.softwareVersion 

I created it using the normal Report Builder.

[harringg]

Give this a try:

SELECT TOP 1000000 tblcomputers.computername,
                   tblcomputers.computerunique,
                   web40osname.osname,
                   tblCompCustom1.location,
                   tblCompCustom1.department AS [Research Unit],
                   tblCompCustom1.custom1    AS [User Name],
                   tblsystemenclosure.chassistypes
FROM   tblcomputers
       INNER JOIN tblcompcustom tblCompCustom1
               ON tblcomputers.computername = tblCompCustom1.computername
       INNER JOIN web40osname
               ON web40osname.computername = tblCompCustom1.computername
       INNER JOIN tblsystemenclosure
               ON tblcomputers.computername = tblsystemenclosure.computername
WHERE  tblcomputers.computername NOT IN (SELECT tblsoftware.computername
                                         FROM   tblsoftware
                                         WHERE
              tblsoftware.softwarename LIKE 'Symantec%')
       AND tblsystemenclosure.chassistypes = 10
ORDER  BY tblcomputers.computerunique  

[Hemoco · Accepted solution]

A sample report can be seen below. Replace YourSoftware with the name of the software package you would like to report on. The report will list computers that DO NOT have the specified software package installed.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblComputers.Firstseen, tblComputers.Lastseen
From tblComputers
Where tblComputers.Computername Not In (Select tblSoftware.ComputerName
From tblSoftware Where tblSoftware.softwareName Like '%YourSoftware%')
Order By tblComputers.ComputerUnique

harringg wrote:
This report will show me them, but also includes all the other software installed, making the report unmanageable.

The report you are currently using will never list the information you are after. What it will list is the following:
- All of your computers.
- Any software installed on your computers that does not match the specified criterion.

[harringg]

Thanks! You've made this query and future queries like it possible.

I've tweaked it a bit so I can know who I need to contact and where the PC is physically located.

Thanks again!

SELECT TOP 1000000 tblcomputers.computername,
                   tblcomputers.computerunique,
                   web40osname.osname,
                   tblCompCustom1.location,
                   tblCompCustom1.department AS [Research Unit],
                   tblCompCustom1.custom1    AS [User Name]
FROM   tblcomputers
       INNER JOIN tblcompcustom tblCompCustom1
               ON tblcomputers.computername = tblCompCustom1.computername
       INNER JOIN web40osname
               ON web40osname.computername = tblCompCustom1.computername
WHERE  tblcomputers.computername NOT IN (SELECT tblsoftware.computername
                                         FROM   tblsoftware
                                         WHERE  (
              tblsoftware.softwarename LIKE 'SoftwareX1%' )
                                                 OR (
              tblsoftware.softwarename LIKE 'SoftwareX2%' ))
       AND tblcomputers.domain LIKE '%MYDOMAIN%'
ORDER  BY tblcomputers.computerunique