Expired Antivirus Report - How to show only assets with both expired?

[Jazmine]

I'm brand new to creating reports and am having trouble displaying the results that I want.
The default report shows assets that have outdated Antivirus A or Antivirus B (or both), which also results in some machines appearing multiple times.

Is there a way to only show results for assets that are outdated for both? Even better if it can avoid duplicates!

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAntivirus.DisplayName As Antivirus,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress As [IP Address],
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Lasttried,
tblAssets.Firstseen,
tblAssets.Lastseen
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
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.State = 1 And tblAntivirus.productUpToDate = 0
Order By tblAssets.AssetName

[RCorbeil]

You have to remember that you're (the SQL server is) processing the records one-by-one. As presented, along with the other conditions, you're checking
- is the current antivirus record "Antivirus A"
- is the current antivirus record also expired
- is the current antivirus record also "Antivirus B"
- is the current antivirus record also expired
The current antivirus record being checked will never be both "Antivirus A" and "Antivirus B". You need to make two links to the antivirus table: one to check the status of A, the second to check the status of B.

Try this:

Select Top 1000000
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.IPAddress As [IP Address],
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Lasttried,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  AntivirusA.DisplayName AS A_name,
  AntivirusB.DisplayName AS B_name
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputerSystem On tblComputerSystem.AssetID = tblAssets.AssetID
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
  Left Join (Select tblAntivirus.AssetID,
               tblAntivirus.DisplayName
             From tblAntivirus
             Where tblAntivirus.DisplayName Like '%ANTIVIRUS A%'
               And tblAntivirus.productUpToDate = 0) AS AntivirusA On AntivirusA.AssetID = tblAssets.AssetID
  Left Join (Select tblAntivirus.AssetID,
               tblAntivirus.DisplayName
             From tblAntivirus
             Where tblAntivirus.DisplayName Like '%ANTIVIRUS B%'
               And tblAntivirus.productUpToDate = 0) AS AntivirusB On AntivirusB.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1              -- active
  AND tblAssets.AssetName Not Like '%SERV%'
  AND AntivirusA.AssetID IS NOT NULL    -- expired Antivirus A found
  AND AntivirusB.AssetID IS NOT NULL    -- expired Antivirus B found

I don't have any records in my inventory that meet the conditions you're after, but when I modified the code to look for machines that have two specific non-expired antivirus programs, I did produce results, so I think this should do what you're after.

[Jazmine]

Thank you for taking the time to respond- your solution worked perfectly!
I also appreciate you breaking down the process, because I'm learning SQL and this explains why I've been having some difficulties creating my reports.

[RCorbeil]

Take a look at this thread. You should be able to use the same approach to check for two known antivirus products. Or if you know that machines may have as many as two and you know one for certain, make the first check for (Antivirus A AND expired) and the second for (NOT(Antivirus A) AND expired).

[Jazmine]

Thank you so much!
I tried to make the changes, but I went wrong somewhere:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAntivirus.DisplayName As Antivirus,
  tblAssets.IPAddress As [IP Address],
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tblAssets.Lasttried,
  tblAssets.Lastseen,
  tblSoftwareUni.softwareName
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
  Left Join tblSoftware On tblAntivirus.AssetID = tblSoftware.AssetID
  Left Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetName Not Like '%SERV%' And 
  tblSoftwareUni.softwareName Like '%ANTIVIRUS A%' And
  tblAntivirus.productUpToDate = 0 And tblSoftwareUni.softwareName Like
  '%ANTIVIRUS B%' And tblAntivirus.productUpToDate = 0 And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName