Windows Defender Only Report

[jwalton4th]

I'm trying to create a report that will tell me what systems have Windows Defender installed as anti-virus and ONLY Windows Defender. I have hundreds of systems that I am moving over from one anti-virus vendor to another. We have many that list both Windows Defender and another anti-virus product. I do understand that you can clean WMI to list the correct and only active anti-virus but this requires reboots across the board and not something we want to do between now and the next maintenance/patch cycle. Below is what I tried but it still showed systems with both Windows Defender and the other products on them.

Also, in this report, how do I create the links from the Asset to the Asset Page within the report? Thank you!

Select Top 1000000 tblassets.AssetID As AssetID1,
  tblassets.AssetName As AssetName1,
  tsysassettypes.AssetTypename As AssetTypename1,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress As IPAddress1,
  tblassets.Lastseen As Lastseen1,
  tblassets.Lasttried As Lasttried1,
  tblAntivirus.AntivirusId,
  tblAntivirus.DisplayName,
  tblAntivirus.AssetID,
  tblAntivirus.ProductState,
  tblAntivirus.LastChanged,
  tblAntivirus.onAccessScanningEnabled,
  tblAntivirus.productUpToDate
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join tblAntivirus On tblassets.AssetID = tblAntivirus.AssetID
Where tblAntivirus.DisplayName Like '%Windows Defender%' And
  tblAntivirus.DisplayName Not Like '%Sophos%' And
  tblAntivirus.DisplayName Not Like '%Sentinel%' And tblassetcustom.State = 1

EDIT:

As an afterthought, I have a built in report that lists workstations without anti-virus installed. Ideally, I would like to add to it that any system with ONLY Windows Defender installed for anti-virus, would show in the report. Here is that query:

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,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan]
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 Outer 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

[Andy_Sismey]

Hi, Give this a try , I wrote it very quickly so may need a tweak or two ?

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblassets.Lasttried,
  Case
    When AV.AssetID Is Not Null Then 'Yes'
    Else 'No'
  End As AV,
  AV.DisplayName
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Left Join (Select tblAntivirus.AssetID,
      tblAntivirus.DisplayName
    From lansweeperdb.dbo.tblAntivirus
    Where tblAntivirus.DisplayName = 'Windows Defender') AV On
      tblassets.AssetID = AV.AssetID
Where tsysassettypes.AssetTypename = 'Windows' And tblassetcustom.State = 1
Order By AV

[jwalton4th]

THANK YOU! It wasn't 100% what I wanted but it was enough so that I can modify it to my needs. Appreciate your help!

Andy.S wrote:
Hi, Give this a try , I wrote it very quickly so may need a tweak or two ?

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblassets.Lasttried,
  Case
    When AV.AssetID Is Not Null Then 'Yes'
    Else 'No'
  End As AV,
  AV.DisplayName
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Left Join (Select tblAntivirus.AssetID,
      tblAntivirus.DisplayName
    From lansweeperdb.dbo.tblAntivirus
    Where tblAntivirus.DisplayName = 'Windows Defender') AV On
      tblassets.AssetID = AV.AssetID
Where tsysassettypes.AssetTypename = 'Windows' And tblassetcustom.State = 1
Order By AV