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