Report for PC's without specific AntiVirus Software

[lembke_d]

I'm trying to create a report that will list all PC's that don't have our standard antivirus software (webroot) or Windows Defender on them.

My report works for PC's that don't have Webroot, but won't work when I try to add Windows Defender. Any suggestions?

Select Top 1000000 tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.AssetUnique,
tblAssets.Username,
tsysOS.Image As icon,
tblAntivirus.DisplayName,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Where (Not tblAntivirus.DisplayName = 'Webroot SecureAnywhere' Or
tblAntivirus.DisplayName = 'Windows Defender')
Order By tblAssets.AssetName

[lembke_d · Accepted solution]

Thanks!

[RCorbeil]

You need to take a different tack. You're looking to check for the presence of two different antivirus titles, so you need to make two links against the antivirus table.

Try this:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.IPAddress,
  tblAssets.AssetUnique,
  tblAssets.Username,
  tsysOS.Image As icon,
  av1.DisplayName AS Webroot,
  av2.DisplayName AS Defender,
  tblAssets.Lastseen
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  LEFT JOIN tblAntivirus AS av1 ON av1.AssetID = tblAssets.AssetID AND av1.DisplayName='Webroot SecureAnywhere'
  LEFT JOIN tblAntivirus AS av2 ON av2.AssetID = tblAssets.AssetID AND av2.DisplayName='Windows Defender'
Where
  av1.AssetID IS NULL
  AND av2.AssetID IS NULL
Order By
  tblAssets.AssetName