All workstations without Anti-virus software - Symantec Endpoint Protection

[luana_ferreira]

Dear,

I need your help regarding the report below because we use Symantec's default antivirus, and in the workstations report without the antivirus, I can not filter the stations that the antivirus client does not have because of windows defender. It looks like there's an antivirus client installed.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Inner Join tsysantivirus On tblSoftwareUni.softwareName
Like tsysantivirus.Software) And tblAssets.AssetID Not In (Select
tblAntivirus.AssetID
From tblAntivirus) And tblState.Statename = 'Active' And
tblDomainroles.Domainrolename In ('Stand-alone Workstation',
'Member Workstation')
Order By tblAssets.Domain,
tblAssets.AssetName

[RCorbeil]

tsysAntivirus is a list of patterns to match against the software name.
tblAntivirus contains a list of antivirus software found.

The existing WHERE clause contains two conditions that appear to be redundant:

• installed software does not include a known antivirus name
  
• a known antivirus package was found in the installed software

If you want to maintain the redundancy, you can check for your expected software in both locations but you should be able to get away with just checking either one.

WHERE
  tblAssets.AssetID NOT IN
    (SELECT
       tblSoftware.AssetID
     FROM
       tblSoftware
       INNER JOIN tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
     WHERE
       tblSoftwareUni.softwareName LIKE 'Symantec Endpoint Protection%')
  AND tblAssets.AssetID NOT IN
    (SELECT
       tblAntivirus.AssetID
     FROM
       tblAntivirus
     WHERE
       tblAntivirus.DisplayName LIKE 'Symantec Endpoint Protection%')