cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
HarkinsIT
Champion Sweeper
We are currently in the process of switching all company computers from Trend Micro to Kaspersky AntiVirus. I have created two separate reports to show me which computers have each application installed. I now want a report to show me any computers that have neither of these programs installed. This is what I have currently:

SELECT TOP 100 PERCENT dbo.tblComputers.Computername AS Computer, dbo.tblFileVersions.Lastchanged, dbo.tblFileVersions.FilePathfull,
dbo.tblFileVersions.[Found]
FROM dbo.tblComputers INNER JOIN
dbo.tblComputersystem ON dbo.tblComputers.Computername = dbo.tblComputersystem.Computername LEFT OUTER JOIN
dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername LEFT OUTER JOIN
dbo.tblFileVersions ON dbo.tblComputers.Computername = dbo.tblFileVersions.Computername
WHERE (dbo.tblFileVersions.FilePathfull LIKE '%PccNTMon%') AND (dbo.tblFileVersions.[Found] = 0) OR
(dbo.tblFileVersions.FilePathfull LIKE '%avp.exe%') AND (dbo.tblFileVersions.[Found] = 0)
ORDER BY dbo.tblComputers.Computername

This report doesn't show me exactly what I want though. It just shows me a combination of all the computers that don't have one or the other, but not both. I have seen a few other people in the forums having a similar issue, but because of my limited SQL skills, I'm not able to apply their solutions to my particular problem.

Would anyone mind helping me out?

Thanks
-Chris
3 REPLIES 3
HarkinsIT
Champion Sweeper
OK, I guess I've been doing these types of reports incorrectly. I normally just set up a scanned file and base software reports off of that. I will work on changing these reports around to work more like you've shown here.

Thanks.
-Chris
Hemoco
Lansweeper Alumni
HarkinsIT wrote:
OK, I guess I've been doing these types of reports incorrectly. I normally just set up a scanned file and base software reports off of that. I will work on changing these reports around to work more like you've shown here.

Thanks.
-Chris


That would work for most other reports but not for this one.
Hemoco
Lansweeper Alumni
Your query should be formed like this:

Select computername from tblcomputers
where
computername not in (SELECT ComputerName FROM dbo.tblSoftware WHERE softwareName LIKE 'antivirus1')
and
computername not in (SELECT ComputerName FROM dbo.tblSoftware WHERE softwareName LIKE 'antivirus2')
and
computername not in (SELECT ComputerName FROM dbo.tblSoftware WHERE softwareName LIKE 'antivirus3')

The 3 lines in bold can be replaced by the criteria that you need.