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
Trelor89
Engaged Sweeper II
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
Engaged Sweeper II
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
Engaged Sweeper II
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
Engaged Sweeper II
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
Champion Sweeper III
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
Engaged Sweeper II
Anybody get this modified correctly to Factor in the new Dell BIOS versioning?
Richard_A
Engaged Sweeper II
HI all

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

Richard
lrea
Engaged Sweeper
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
Champion Sweeper II
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
Engaged Sweeper
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?