Another AntiVirus Report

[HarkinsIT]

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

[HarkinsIT]

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]

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]

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.