Computers/Systems with no Anti-Virus Installed

[smozie]

I have written queries that are returning all systems with anti virus and I have written one that shows all systems without anti virus. When I add the two together, I get way more endpoints than I should have. I believe the query for those with Anti-virus is correct, just trying to figure out what I can do about the "without" query.

Query With Anti-virus

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tsysOS.OSname As OS,
tblAntivirus.DisplayName As AntiVirus
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAntivirus.onAccessScanningEnabled <> 0 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
AntiVirus



Query Without Anti-virus

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
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

From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
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 tblComputersystem.Domainrole <= 1 And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Esben_D]

Reports to show assets without AV are already built-in. There is one for servers and one for workstations. If you just want a report for all assets, you can remove "Where tblComputersystem.Domainrole >= 2" from the server report to get all assets.

For your query specifically, it returned correct information for me when I removed the domainrole where condition.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
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
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
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 tblAssetCustom.State = 1
Order By tblAssets.AssetName