cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Daniel_B
Lansweeper Alumni
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
25 REPLIES 25
kim_kaberg
Engaged Sweeper II
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
Champion Sweeper III
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
Lansweeper Alumni
Thank you, I updated the original post as well and removed the duplicate condition.
joes3
Engaged Sweeper III
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
Engaged Sweeper II
I like this. The only modification I made was to only show devices that are active.
tblAssetCustom.State = 1
Susan_A
Lansweeper Alumni
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!