workstations without Anti-virus

[luana_ferreira]

Prezados,

Preciso da ajuda de vocês referente ao relatório abaixo, pois utilizamos como antivírus padrão o da Symantec, e no relatório das estações de trabalho sem o antivírus, não consigo filtrar as estações que não possui o cliente do antivírus por causa do windows defender. Pois fica se houvesse um cliente de antivírus instalado.

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

[AZHockeyNut]

Sorry I do not speak Portuguese, however, I think you wanted this?

Select Top 1000000 unioned.assetid,
tblAssets.AssetName,
tsysOS.Image As icon,
unioned.software,
unioned.version,
unioned.Enabled,
unioned.Uptodate,
unioned.RetrievedFrom,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From ((Select a.assetid As assetid,
a.software As software,
a.version As version,
'software comparison' As RetrievedFrom,
'' As Enabled,
'' As Uptodate
From (Select tblSoftware.AssetID As assetid,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) a)
Union
(Select tblAntivirus.AssetID As assetid,
tblAntivirus.DisplayName As software,
Null As version,
'WMI' As RetrievedFrom,
Case When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes' Else 'No'
End As Enabled,
Case When tblAntivirus.productUpToDate = 1 Then 'Yes' Else 'No'
End As Uptodate
From tblAntivirus)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblComputersystem.Domainrole <= 1 and software like '%Syman%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName