cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Jazmine
Engaged Sweeper II
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


4 REPLIES 4
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper II
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