→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
lembke_d
Engaged Sweeper
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
1 ACCEPTED SOLUTION
lembke_d
Engaged Sweeper
2 REPLIES 2
lembke_d
Engaged Sweeper
Thanks!
RCorbeil
Honored Sweeper II
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