Query for Computers with No Antivirus Installed

[CBTechnology]

My apologies for being a slight novice with SQL queries. I am with a school district and we currently have two antivirus solutions on our computers, Symantec Endpoint protection and Symantec Antivirus. I have created a couple queries that tell me the computers that have either of these installed. What I would like to do is create a query that tells me if neither of these are installed (basically, the computer doesn't have antivirus installed).

I am having problems getting a query that doesn't just return the computername along with every piece of software that is not Antivirus. Does anyone have any tips for me as to how to get just the computername of any computers without specific software?

Thanks!

[cvaleriy]

Great thanks to pdumbleton.

[CBTechnology]

Thanks for the replies. With that info I was able to get my query working. Appreciate the quick responses!

[Hemoco]

Should be something like this (not tested for syntax errors)

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')

[pdumbleton]

This is the queries we currently use to determine if machines do not have any Symantec Anti-Virus installed. Since ther is a problem with the x64 bit machines not reporting installed software correctly I added a check against what procesess are also running just to make sure we did not miss anything.

This is setup as a view called web30repNoSymantec:

SELECT DISTINCT TOP 100 PERCENT dbo.tblComputers.Computername AS Computer, dbo.tblOperatingsystem.Description, dbo.tblOperatingsystem.Caption, dbo.tblComputers.Domain

FROM dbo.tblComputers INNER JOIN
dbo.tblComputersystem ON dbo.tblComputers.Computername = dbo.tblComputersystem.Computername INNER JOIN
dbo.TsysLastscan ON dbo.tblComputers.Computername = dbo.TsysLastscan.Computername LEFT OUTER JOIN
dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername LEFT OUTER JOIN
dbo.tblSoftware ON dbo.tblComputers.Computername = dbo.tblSoftware.ComputerName LEFT OUTER JOIN
dbo.tblProcesses ON dbo.tblComputers.Computername = dbo.tblProcesses.Computername

WHERE (dbo.tblComputers.Computername IN
(SELECT Computername
FROM dbo.TsysLastscan AS TsysLastscan_1
WHERE (CFGname = 'Software')))
AND (dbo.tblComputers.Computername NOT IN
(SELECT ComputerName
FROM dbo.tblSoftware AS tblSoftware_1
WHERE (softwareName LIKE 'Symantec% Endpoint Protection')
OR (softwareName LIKE 'Symantec% AntiVirus')
OR (softwareName LIKE 'Symantec Client Security')))
AND (dbo.tblComputers.Computername NOT IN
(SELECT Computername
FROM dbo.tblProcesses AS tblprocesses_1
WHERE (Caption LIKE 'smc.exe') OR (Caption LIKE 'rtvscan.exe')))

ORDER BY dbo.tblComputers.Computername