Bios: Current versus latest Bios version - per model

[FrodeD]

Here is the custom Bios report posted at the August 2025 - Open Office Hours. Will query the list of available bios versions found per model of computer and sort on versions and date to find the latest one available (internally in lansweeper database) and then show the bios status for each computer in the report. 

Not claiming ownership of this code as it's surely partly customized from previous ideas and reports found on this community 🙂 Have fun modifying this to your own use!

Select Top 1000000 tblAssets_1.AssetID,
tblAssets_1.AssetName,
tblADusers.Displayname,
tblAssets_1.Userdomain,
tblAssets_1.Username,
tblAssets_1.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblBIOS.SMBIOSBIOSVersion As CurrentBiosVersion,
tblBIOS.ReleaseDate As CurrentBiosVersionReleaseDate,
Bios.biosMaxVersion As NewerBiosVersion,
Bios.biosMax As NewerBiosVersionReleaseDate,
tsysIPLocations.IPLocation,
tblAssets_1.IPAddress,
tblAssets_1.LastLsAgent
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.Manufacturer Not Like 'VMware%' And
tblAssetCustom_1.Manufacturer Not Like 'Amazon%' And
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 ''
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
Left Join tblADusers On tblAssets_1.Username = tblADusers.Username And
tblAssets_1.Userdomain = tblADusers.Userdomain
Left Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets_1.LocationID
Where tblBIOS.SMBIOSBIOSVersion <> Bios.biosMaxVersion And
tblAssetCustom.State = 1
Order By tblAssets_1.AssetName

[Mister_Nobody]

I like my version:

Select Top 1000000 tblassets.assetname,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer As Asset_Vendor,
  tblAssetCustom.Model,
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease,
  tblVmwareInfo.Version) As OS,
  TB.*,
  DateDiff(d, TB.ReleaseDate, TB.maxbiosdate) DiffDays,
  Case
    When TB.version = TB.lastbiosversion Then 'black'
    Else 'red'
  End As foregroundcolor
From (Select Top 1000000 biosinfo.*,
      Max(biosinfo.ReleaseDate) Over (Partition By biosinfo.manufacturer,
      biosinfo.product) As maxbiosdate,
      First_Value(biosinfo.version) Over (Partition By biosinfo.manufacturer,
      biosinfo.product Order By biosinfo.ReleaseDate Desc) As lastbiosversion
    From (Select tblBIOS.assetid,
          tblBaseBoard.Manufacturer,
          tblBaseBoard.Product,
          tblBIOS.ReleaseDate,
          tblBIOS.SMBIOSBIOSVersion As version
        From tblBaseBoard
          Inner Join tblBIOS On tblBaseBoard.assetid = tblBIOS.assetid
        Union
        Select tblLinuxBaseBoard.assetid,
          tblLinuxBaseBoard.Manufacturer,
          tblLinuxBaseBoard.productname As product,
          tbllinuxBIOS.ReleaseDate,
          tbllinuxBIOS.Version
        From tblLinuxBaseBoard
          Inner Join tbllinuxBIOS On tblLinuxBaseBoard.assetid =
              tbllinuxBIOS.assetid
        Union
        Select tblVmwareInfo.AssetID,
          tblVmwareInfo.Vendor As Manufacturer,
          tblVmwareInfo.Model As product,
          tblVmwareInfo.BiosDate As ReleaseDate,
          tblVmwareInfo.BiosVersion As version
        From tblVmwareInfo) biosinfo) TB
  Inner Join tblassets On tblassets.assetid = TB.assetid
  Left Join tblOperatingsystem On tblassets.AssetID = tblOperatingsystem.AssetID
  Left Join tblLinuxSystem On tblassets.AssetID = tblLinuxSystem.AssetID
  Left Join tblVmwareInfo On tblassets.AssetID = tblVmwareInfo.AssetID
  Left Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
  Left Join tsysIPLocations On tblassets.LocationID = tsysIPLocations.LocationID
Where TB.product Not In ('440BX Desktop Reference Platform', 'RHEL-AV',
  'VirtualBox', 'Virtual Machine')
Order By TB.manufacturer,
  TB.product,
  tblassets.assetname

[Katgroup]

Thanks for posting this!