Help creating a report to show computers that do not have any AV

[kcovingt]

I have created a report that shows me all pc's that have one of the three particular AV packages we have used throughout the years. What I am trying to create is a report that shows me any pc's that do not have any of the three installed. This is the SQL code for the report that shows me all the pc's that do have AV installed.

SELECT
tblSoftware.ComputerName,
tblComputers.LastknownIP,
tblSoftware.softwareVersion,
tblSoftware.softwareName
FROM
tblComputers
INNER JOIN tblSoftware ON (tblComputers.Computername = tblSoftware.ComputerName)
WHERE
tblSoftware.softwareName = 'Norton Antivirus Corporate Edition' OR
tblSoftware.softwareName = 'Symantec Endpoint Protection' OR
tblSoftware.softwareName = 'Symantec Antivirus'

Basically I want the inverse of this report, I need a report that shows all pc's and their ip addresses that have none of the three packages installed. My problem seems to be no matter what combination I change I end up getting a report showing every pc in our domain and not just the ones that have none of the above installed.

Thanks for any help and we really enjoy the product, it has been a lifesaver as far as documentating what is installed in our network of 5,000 pc's.

[Hemoco]

Must have missed this thread, sorry.

Is "Sophos Anti-Virus" the exact software listed in your software screen?

[afionda]

Can anyone help a brother out ?

THANKS !!!

[afionda]

Lansweeper Admin,

Can you please post the full statement. I am having a rough time getting the (not in) command to work.

I need to find all computer that dont have Sophos Anti-Virus installed.

Here is what i have so far:

Select Top 100 Percent tblSoftware.ComputerName, tblComputers.LastknownIP,
tblSoftware.softwareName, tblComputers.Username, tblSoftware.softwareVersion
From tblComputers Left Outer Join
tblSoftware On (tblComputers.Computername = tblSoftware.ComputerName)
Where tblSoftware.softwareName = 'Sophos Anti-Virus' And tblComputers.Username =
''

I am a premium user and LOVE the product.

Thanks

[kcovingt]

Thank you very much for the quick reply and that worked beautifully and gave us exactly the report we were looking for.

[Hemoco]

Should be something like

... where computername not in (select computername from tblsoftware where softwareName = 'Norton Antivirus Corporate Edition') and computername not in (select computername from tblsoftware where softwareName = 'Symantec Endpoint Protection') and computername not in (select computername from tblsoftware where softwareName = 'Symantec Antivirus')