BIOS version check

[Daniel_B]

This report looks for the most recent BIOS version scanned in your network for each model and lists computers having an older BIOS version installed

Meets the following criteria:
- Active Windows computers
- BIOS version not the same as the highest version scanned for the same model

Sorted on:
- Assetname

Select Top 1000000 tblAssets_1.AssetID,
  tblAssets_1.AssetName,
  tblAssets_1.Domain,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblBIOS.SMBIOSBIOSVersion As CurrentBios,
  Bios.biosMax As LatestBios,
  Case When tblBIOS.SMBIOSBIOSVersion = Bios.biosMax 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 tblAssetCustom_1.Manufacturer,
    tblAssetCustom_1.Model,
    Max(tblBIOS_1.SMBIOSBIOSVersion) 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'
  Group By tblAssetCustom_1.Manufacturer,
    tblAssetCustom_1.Model) As Bios On tblAssetCustom.Model = Bios.Model And
    tblAssetCustom.Manufacturer = Bios.Manufacturer And
    tblBIOS.SMBIOSBIOSVersion <> Bios.biosMax
  Inner Join tsysOS On tsysOS.OScode = tblAssets_1.OScode
Where tblBIOS.SMBIOSBIOSVersion <> Bios.biosMax And tblAssetCustom.State = 1
Order By tblAssets_1.AssetName

[kim_kaberg]

zdlease wrote:
This query works great, with the exception that there's an issue with the newer Dell BIOS naming scheme (Axx versions work fine).

I believe the issue is with the version comparison, most likely due to it being a string comparison. The current example I have is a Latitude where BIOS version 1.16.4 is installed, however the report is showing there is a newer update of 1.9.4, which is not accurate.

I'm not a SQL expert by far, any thoughts for overcoming this issue?

I can confirm this.

Reported as latest: BIOS Date: 03/09/18 01:40:23 Ver: 1.9.3.00

Reported as old: BIOS Date: 05/28/18 19:27:50 Ver: 1.10.1.00

[AZHockeyNut]

zdlease wrote:
This query works great, with the exception that there's an issue with the newer Dell BIOS naming scheme (Axx versions work fine).

I believe the issue is with the version comparison, most likely due to it being a string comparison. The current example I have is a Latitude where BIOS version 1.16.4 is installed, however the report is showing there is a newer update of 1.9.4, which is not accurate.

I'm not a SQL expert by far, any thoughts for overcoming this issue?

I am seeing it accurate on my OptiPlex where it shows current A16 Latest A19 or A13 and A15 etc
I don't have Lattitudes to compare though.

EDIT: I did see errors on a PowerEdge where 1.10.1 was reported as older than 1.9.1

[Daniel_B]

Thank you, I updated the original post as well and removed the duplicate condition.

[joes3]

Daniel.B wrote:

  Group By tblAssetCustom_1.Manufacturer,
    tblAssetCustom_1.Model) As Bios On tblAssetCustom.Model = Bios.Model And
    tblAssetCustom.Manufacturer = Bios.Manufacturer And
    tblBIOS.SMBIOSBIOSVersion <> Bios.biosMax And tblBIOS.SMBIOSBIOSVersion <>
    Bios.biosMax

The last two statements in this section appear to be duplicates.

Here's a slight modification of the report that includes devices with up-to-date BIOS versions. This one is sorted by Manufacturer, Model, AssetName and I've removed the Domain column but everything else is pretty much intact.

Select Top 1000000 tblAssets_1.AssetID,
  tblAssets_1.AssetName,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblBIOS.SMBIOSBIOSVersion As CurrentBios,
  Bios.biosMax As LatestBios,
  Case When tblBIOS.SMBIOSBIOSVersion = Bios.biosMax 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 tblAssetCustom_1.Manufacturer,
    tblAssetCustom_1.Model,
    Max(tblBIOS_1.SMBIOSBIOSVersion) 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'
  Group By tblAssetCustom_1.Manufacturer,
    tblAssetCustom_1.Model) 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 tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets_1.AssetName

[studerje]

I like this. The only modification I made was to only show devices that are active.
tblAssetCustom.State = 1

[Susan_A]

studerje wrote:
I like this. The only modification I made was to only show devices that are active.
tblAssetCustom.State = 1

We should have added this actually, like we do in all built-in reports. I've corrected our query. Thanks for letting us know!