→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
harringg
Champion Sweeper
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
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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.

View solution in original post

5 REPLIES 5
ABECU
Engaged Sweeper III
Thanks, that pointed me in the right direction.
ABECU
Engaged Sweeper III
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
Champion Sweeper
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
Lansweeper Alumni
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
Champion Sweeper
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