BIOS Version Audit

Select Top 1000000 tblAssets_1.AssetID,
  tblAssets_1.AssetName,
  tblAssets_1.Domain,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblBIOS.SMBIOSBIOSVersion As CurrentBios,
  Bios.biosMaxVersion as NewerBiosVersion,
  Bios.biosMax as NewerBiosVersionReleaseDate,
  Case When tblBIOS.SMBIOSBIOSVersion = Bios.biosMaxVersion Then 'black' Else 'red'
  End As foregroundcolor,
  tsysOS.Image As icon
From tblAssets As tblAssets_1
  Inner Join tblBIOS On tblAssets_1.AssetID = tblBIOS.AssetID
  Inner Join tblAssetCustom On tblAssets_1.AssetID = tblAssetCustom.AssetID
  Inner Join (Select TOP 1 WITH TIES  tblAssetCustom_1.Manufacturer,
    tblAssetCustom_1.Model,
    tblBIOS_1.SMBIOSBIOSVersion as biosMaxVersion,
    Max(tblBIOS_1.ReleaseDate) As biosMax
  From tblAssets
    Inner Join tblAssetCustom As tblAssetCustom_1 On tblAssets.AssetID =
      tblAssetCustom_1.AssetID
    Inner Join tblBIOS As tblBIOS_1 On tblAssets.AssetID = tblBIOS_1.AssetID
  Where tblAssetCustom_1.Model Not Like 'VirtualBox' And
    tblAssetCustom_1.Model Not Like 'VMware Virtual Platform' And
    tblAssetCustom_1.Model Not Like 'Virtual Machine' and
    tblAssetCustom_1.Model Not Like '' and
    tblAssetCustom_1.Manufacturer not like 'VMware%' and
    tblAssetCustom_1.Manufacturer not like 'Amazon%'
  Group By tblAssetCustom_1.Manufacturer,
    tblAssetCustom_1.Model, tblBIOS_1.SMBIOSBIOSVersion, tblBIOS_1.ReleaseDate
    Order By Row_Number() Over (Partition By tblAssetCustom_1.Model Order By
        tblAssetCustom_1.Model, tblBIOS_1.ReleaseDate Desc)) As Bios On
    tblAssetCustom.Model = Bios.Model And tblAssetCustom.Manufacturer =
    Bios.Manufacturer
 
  Inner Join tsysOS On tsysOS.OScode = tblAssets_1.OScode
Where tblAssetCustom.State = 1
Order By tblAssets_1.AssetName