BIOS Version Audit report for Microsoft Windows, Linux , VMware ESXi

[Mister_Nobody]

There is a report in the Report Library

https://www.lansweeper.com/resources/report/hardware/bios-version-audit/

But I don't like assetcustom model and manufacturer attributes because I don't undestand when LS refresh their.

I like baseboard table and have rewritten query:

See below post:
https://community.lansweeper.com/t5/reports-analytics/bios-version-audit-report-for-windows-linux-es...

[Obi_1_Cinobi]

Hello there!

Thanks again for sharing! 😎

[Mister_Nobody · Accepted solution]

After deep dive into the problem I have decided to rewrite query from scratch:

Select Top 1000000 tblassets.assetname,
  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
Where TB.product Not In ('440BX Desktop Reference Platform', 'RHEL-AV',
  'VirtualBox', 'Virtual Machine')

Very Fast and Simple code!

New features:

1. Support Windows +Linux, VMware

2. Show operating system

3. Show days between current BIOS and latest known

4. Filter for Virtual Machines from esxi, hyper-v, kvm, virtualbox