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

[Trelor89]

What version are you running I am running Version 7 (Latest), I am unsure as to what version this requires. I wrote all the code in just SQL Server studio, then ported it to the WebUI. What version are you on?

Rick I wrote:
Tom D,

Not sure what this means but when I implement your code vs the original I get the following

"There was an error parsing the query. [ Token line number = 1,Token line offset = 613,Token in error = With ]"

[SecqureIT]

Lansweeper v. 7.1.100.16

Tom D wrote:
What version are you running I am running Version 7 (Latest), I am unsure as to what version this requires. I wrote all the code in just SQL Server studio, then ported it to the WebUI. What version are you on?

Rick I wrote:
Tom D,

Not sure what this means but when I implement your code vs the original I get the following

"There was an error parsing the query. [ Token line number = 1,Token line offset = 613,Token in error = With ]"

[Trelor89]

I am not sure why it is not working for you, I assume you are running the database on SQL server not SQL express?

Rick I wrote:
Lansweeper v. 7.1.100.16

Tom D wrote:
What version are you running I am running Version 7 (Latest), I am unsure as to what version this requires. I wrote all the code in just SQL Server studio, then ported it to the WebUI. What version are you on?

Rick I wrote:
Tom D,

Not sure what this means but when I implement your code vs the original I get the following

"There was an error parsing the query. [ Token line number = 1,Token line offset = 613,Token in error = With ]"

[Trelor89]

I know this is an old post, but I modified the code above and made some changes this appears to be working for me now with all the Bios versions (Matching on Date). First post so be nice

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 Model ORDER BY Model, tblBIOS_1.ReleaseDate DESC)) As Bios On tblAssetCustom.Model = Bios.Model And
    tblAssetCustom.Manufacturer = Bios.Manufacturer and
	tblBIOS.SMBIOSBIOSVersion <> Bios.biosMaxVersion
  Inner Join tsysOS On tsysOS.OScode = tblAssets_1.OScode
Where tblBIOS.SMBIOSBIOSVersion <> Bios.biosMaxVersion And tblAssetCustom.State = 1
Order By tblAssets_1.AssetName

Also if you remove the <> statements you will see all the machines and their bios versions but the code doesn't work in the LanSweeper reporting, only straight SQL. I am unsure why

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 Model ORDER BY 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

[KevinA-REJIS]

Anyone have any luck fixing this report?

If not, is there a way to reset the value for biosMax? The report is still returning a lower BIOS even though no machines have it installed (ex. report shows 1.8.1 as max for specific model, but all machines of that model have 1.11.1 or higher)

[RayHeathTSS]

Anybody get this modified correctly to Factor in the new Dell BIOS versioning?

[Richard_A]

HI all

Are there any plans to fix this report, to work with Dell's new BIOS numbering?

Richard

[lrea]

I added to the where statement to remove all the virtual machines (I just wanted hardware):
Where tblAssetCustom.Model != 'virtual machine'

so the final lines look like this:
Where tblAssetCustom.Model != 'virtual machine' And tblState.Statename =
'Active'

[PeterJG]

is there quick way to modify current report to only show assets not on latest version?
.....

found way.. by modifying last Where statement to:

Where tblAssetCustom.State = 1 and tblBIOS.SMBIOSBIOSVersion != bios.biosMax

[zdlease]

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?