Windows: Workstations with antivirus expired (Built-in)

[NicholeKaligian]

The report below lists all Windows computers whose antivirus is expired

The report will only list assets that meet all of the following criteria:

• The asset state is set to "active".
  
• The asset has been successfully scanned at least once.
  
• The asset is a Windows asset.
  
• The asset is a workstation.
  
• The antivirus software is recognized by the Security Center on the client.
  
• The asset has expired antivirus software detected.
  

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAntivirus.DisplayName As AntiVirus
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
    tblComputersystem.Domainrole
  Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Where tblAntivirus.productUpToDate = 0 And tblState.Statename = 'Active' And
  tblDomainroles.Domainrolename In ('Stand-alone Workstation',
  'Member Workstation')
Order By tblAssets.Domain,
  tblAssets.AssetName