Notification

Icon
Error

BIOS version check - Compares BIOS versions of all computers with the most recent one scanned

Posted: Monday, May 4, 2015 10:59:30 AM(UTC)
Daniel.B

Daniel.B

Member Original PosterPosts: 1,150
13
Like
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

Code:

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
studerje
#1studerje Member Posts: 7  
posted: 8/19/2015 5:46:05 PM(UTC)
I like this. The only modification I made was to only show devices that are active.
tblAssetCustom.State = 1
Susan.A
#2Susan.A Member Administration Posts: 1,535  
posted: 8/21/2015 3:36:33 PM(UTC)
Originally Posted by: studerje Go to Quoted Post
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!
joes3
#3joes3 Member Posts: 15  
posted: 9/9/2015 4:53:51 PM(UTC)
Originally Posted by: Daniel.B Go to Quoted Post

Code:

  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.
Daniel.B
#4Daniel.B Member Original PosterPosts: 1,150  
posted: 9/15/2015 12:01:08 PM(UTC)
Thank you, I updated the original post as well and removed the duplicate condition.
zdlease
#5zdlease Member Posts: 2  
posted: 4/30/2018 5:04:45 PM(UTC)
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?
AZHockeyNut
#6AZHockeyNut Member Alpha Tester Posts: 235  
posted: 4/30/2018 8:51:10 PM(UTC)
Originally Posted by: zdlease Go to Quoted Post
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
PeterG
#7PeterG Member Posts: 98  
posted: 5/10/2018 4:31:05 PM(UTC)
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
kim.kaberg@vr.se
#8kim.kaberg@vr.se Member Posts: 5  
posted: 8/24/2018 11:41:42 AM(UTC)
Originally Posted by: zdlease Go to Quoted Post
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
lrea
#9lrea Member Posts: 2  
posted: 9/6/2018 2:45:31 PM(UTC)
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'

Richard A
#10Richard A Member Posts: 2  
posted: 11/21/2018 1:01:47 PM(UTC)
HI all

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

Richard
RayHeathTSS
#11RayHeathTSS Member Posts: 5  
posted: 2/22/2019 6:08:55 PM(UTC)
Anybody get this modified correctly to Factor in the new Dell BIOS versioning?
KevinA-REJIS
#12KevinA-REJIS Member Posts: 19  
posted: 4/15/2019 9:36:05 PM(UTC)
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)
Tom D
#13Tom D Member Posts: 6  
posted: 5/11/2019 3:10:42 PM(UTC)
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 Angel
Code:
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
Code:
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
#14KevinA-REJIS Member Posts: 19  
posted: 5/13/2019 4:49:21 PM(UTC)
Glad someone's taking a crack at this!

I tried the top report, but I'm getting two "The multi-part identifier "tblAssetCustom.Model" could not be bound." errors. Any ideas?


I figured out the problem, I had to change the references in one of the Order By to tblAssetCustom_1, after that it worked!

Code:
Order By Row_Number() Over (Partition By tblAssetCustom_1.Model Order By
        tblAssetCustom_1.Model, tblBIOS_1.ReleaseDate Desc)) As Bios On
    tblAssetCustom.Model = Bios.Model And tblAssetCustom.Manufacturer =
    Bios.Manufacturer
Tom D
#15Tom D Member Posts: 6  
posted: 5/13/2019 10:47:41 PM(UTC)
And that is exactly why the code wouldn't work for the other bit. Thanks for catching that..

All Machines with their BIOS
Code:
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 tblAssetCustom_1.Model Order By
        tblAssetCustom_1.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


Only out of date machines
Code:

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 tblAssetCustom_1.Model Order By
        tblAssetCustom_1.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


Originally Posted by: KevinA-REJIS Go to Quoted Post
Glad someone's taking a crack at this!

I tried the top report, but I'm getting two "The multi-part identifier "tblAssetCustom.Model" could not be bound." errors. Any ideas?


I figured out the problem, I had to change the references in one of the Order By to tblAssetCustom_1, after that it worked!

Code:
Order By Row_Number() Over (Partition By tblAssetCustom_1.Model Order By
        tblAssetCustom_1.Model, tblBIOS_1.ReleaseDate Desc)) As Bios On
    tblAssetCustom.Model = Bios.Model And tblAssetCustom.Manufacturer =
    Bios.Manufacturer


PeterG
#16PeterG Member Posts: 98  
posted: 5/14/2019 1:07:11 PM(UTC)
Here is modified version that shows only assets that need bios update (not on latest version) that have but have been introduced to environment in last 14 days

Code:
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,
  tblAssets_1.Firstseen,
  tblAssets_1.Lastseen
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.Manufacturer Not Like 'VMware%' And
        tblAssetCustom_1.Manufacturer Not Like 'Amazon%' And
        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 ''
      Group By tblAssetCustom_1.Manufacturer,
        tblAssetCustom_1.Model,
        tblBIOS_1.SMBIOSBIOSVersion,
        tblBIOS_1.ReleaseDate
      Order By Row_Number() Over (Partition By tblAssetCustom_1.Model Order By
        tblAssetCustom_1.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 tblBIOS.SMBIOSBIOSVersion < Bios.biosMaxVersion And
  tblAssets_1.Firstseen > GetDate() - 14 And tblAssets_1.Lastseen > GetDate() -
  30 And tblAssetCustom.State = 1
Order By tblAssets_1.AssetName
Tom D
#17Tom D Member Posts: 6  
posted: 5/14/2019 1:49:23 PM(UTC)
Since I use VMware here I added this so I can see all desktops + servers + VMware hosts ( am sure you can do the same for the HyperV tables.
All Assets + BIOS
Code:
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.Manufacturer Not Like 'VMware%' And
        tblAssetCustom_1.Manufacturer Not Like 'Amazon%' And
        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 ''
  Group By tblAssetCustom_1.Manufacturer,
    tblAssetCustom_1.Model, tblBIOS_1.SMBIOSBIOSVersion, tblBIOS_1.ReleaseDate
    Order By Row_Number() Over (Partition By tblAssetCustom_1.Model Order By
        tblAssetCustom_1.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
union all
Select Top 1000000 tblVmwareInfo.AssetID,
  tblVmwareInfo.HostName as AssetName,
  'N/A' as Domain,
  tblVmwareInfo.Vendor as Manufacturer,
  tblVmwareInfo.Model,
  tblVmwareInfo.BiosVersion As CurrentBios,
  Bios.biosMaxVersion as NewerBiosVersion,
  Bios.biosMax as NewerBiosVersionReleaseDate,
  Case When tblVmwareInfo.BiosVersion = Bios.biosMaxVersion Then 'black' Else 'red'
  End As foregroundcolor,
  'vmware16.png' as icon
From tblVmwareInfo
  Inner Join (Select TOP 1 WITH TIES  tblVmwareInfo_2.Vendor  as Manufacturer,
    tblVmwareInfo_2.Model,
    tblVmwareInfo_2.BiosVersion as biosMaxVersion,
    Max(tblVmwareInfo_2.BiosDate) As biosMax
  From tblVmwareInfo  As tblVmwareInfo_1
    Inner Join tblVmwareInfo As tblVmwareInfo_2 On tblVmwareInfo_1.AssetID =
      tblVmwareInfo_2.AssetID
  Group By tblVmwareInfo_2.Vendor,
    tblVmwareInfo_2.Model, tblVmwareInfo_2.BiosVersion, tblVmwareInfo_2.BiosDate
    Order By Row_Number() Over (Partition By tblVmwareInfo_2.Model Order By
        tblVmwareInfo_2.Model, tblVmwareInfo_2.BiosDate Desc)) As Bios On
    tblVmwareInfo.Model = Bios.Model And tblVmwareInfo.Vendor =
    Bios.Manufacturer
Order By AssetName


Only out of Date Assets
Code:
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.Manufacturer Not Like 'VMware%' And
        tblAssetCustom_1.Manufacturer Not Like 'Amazon%' And
        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 ''
  Group By tblAssetCustom_1.Manufacturer,
    tblAssetCustom_1.Model, tblBIOS_1.SMBIOSBIOSVersion, tblBIOS_1.ReleaseDate
    Order By Row_Number() Over (Partition By tblAssetCustom_1.Model Order By
        tblAssetCustom_1.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
union all
Select Top 1000000 tblVmwareInfo.AssetID,
  tblVmwareInfo.HostName as AssetName,
  'N/A' as Domain,
  tblVmwareInfo.Vendor as Manufacturer,
  tblVmwareInfo.Model,
  tblVmwareInfo.BiosVersion As CurrentBios,
  Bios.biosMaxVersion as NewerBiosVersion,
  Bios.biosMax as NewerBiosVersionReleaseDate,
  Case When tblVmwareInfo.BiosVersion = Bios.biosMaxVersion Then 'black' Else 'red'
  End As foregroundcolor,
  'vmware16.png' as icon
From tblVmwareInfo
  Inner Join (Select TOP 1 WITH TIES  tblVmwareInfo_2.Vendor  as Manufacturer,
    tblVmwareInfo_2.Model,
    tblVmwareInfo_2.BiosVersion as biosMaxVersion,
    Max(tblVmwareInfo_2.BiosDate) As biosMax
  From tblVmwareInfo  As tblVmwareInfo_1
    Inner Join tblVmwareInfo As tblVmwareInfo_2 On tblVmwareInfo_1.AssetID =
      tblVmwareInfo_2.AssetID
  Group By tblVmwareInfo_2.Vendor,
    tblVmwareInfo_2.Model, tblVmwareInfo_2.BiosVersion, tblVmwareInfo_2.BiosDate
    Order By Row_Number() Over (Partition By tblVmwareInfo_2.Model Order By
        tblVmwareInfo_2.Model, tblVmwareInfo_2.BiosDate Desc)) As Bios On
    tblVmwareInfo.Model = Bios.Model And tblVmwareInfo.Vendor =
    Bios.Manufacturer and
    tblVmwareInfo.BiosVersion <> Bios.biosMaxVersion
Where tblVmwareInfo.BiosVersion <> Bios.biosMaxVersion
Order By AssetName
Rick I
#18Rick I Member Posts: 9  
posted: 5/14/2019 4:07:08 PM(UTC)
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 ]"


Originally Posted by: Tom D Go to Quoted Post
And that is exactly why the code wouldn't work for the other bit. Thanks for catching that..

All Machines with their BIOS
Code:
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 tblAssetCustom_1.Model Order By
        tblAssetCustom_1.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


Only out of date machines
Code:

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 tblAssetCustom_1.Model Order By
        tblAssetCustom_1.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


Originally Posted by: KevinA-REJIS Go to Quoted Post
Glad someone's taking a crack at this!

I tried the top report, but I'm getting two "The multi-part identifier "tblAssetCustom.Model" could not be bound." errors. Any ideas?


I figured out the problem, I had to change the references in one of the Order By to tblAssetCustom_1, after that it worked!

Code:
Order By Row_Number() Over (Partition By tblAssetCustom_1.Model Order By
        tblAssetCustom_1.Model, tblBIOS_1.ReleaseDate Desc)) As Bios On
    tblAssetCustom.Model = Bios.Model And tblAssetCustom.Manufacturer =
    Bios.Manufacturer




Tom D
#19Tom D Member Posts: 6  
posted: 5/14/2019 4:21:35 PM(UTC)
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?

Originally Posted by: Rick I Go to Quoted Post
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 ]"



Rick I
#20Rick I Member Posts: 9  
posted: 5/14/2019 8:04:11 PM(UTC)
Lansweeper v. 7.1.100.16

Originally Posted by: Tom D Go to Quoted Post
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?

Originally Posted by: Rick I Go to Quoted Post
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 ]"





Tom D
#21Tom D Member Posts: 6  
posted: 5/14/2019 8:29:07 PM(UTC)
I am not sure why it is not working for you, I assume you are running the database on SQL server not SQL express?

Originally Posted by: Rick I Go to Quoted Post
Lansweeper v. 7.1.100.16

Originally Posted by: Tom D Go to Quoted Post
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?

Originally Posted by: Rick I Go to Quoted Post
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 ]"







RayHeathTSS
#22RayHeathTSS Member Posts: 5  
posted: 5/15/2019 2:06:23 PM(UTC)
I am getting this Error:

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

When I use the new code...Not following what is different?
RayHeathTSS
#23RayHeathTSS Member Posts: 5  
posted: 5/15/2019 2:15:21 PM(UTC)
Originally Posted by: RayHeathTSS Go to Quoted Post
I am getting this Error:

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

When I use the new code...Not following what is different?


I am using Lansweeper Version 7.1.110.5 with SQL Express 14.0.2014
Tom D
#24Tom D Member Posts: 6  
posted: 5/15/2019 5:40:34 PM(UTC)
I think that the partition query requires SQL Server Standard or higher Eh?
Originally Posted by: RayHeathTSS Go to Quoted Post
Originally Posted by: RayHeathTSS Go to Quoted Post
I am getting this Error:

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

When I use the new code...Not following what is different?


I am using Lansweeper Version 7.1.110.5 with SQL Express 14.0.2014


Esben.D
#25Esben.D Member Administration Posts: 1,956  
posted: 5/22/2019 1:22:28 PM(UTC)
The 'With' statement that is used in the report shared in the comments is only supported in SQL Server installations.

If you are running SQL Compact, you will need to use the original report or try to find a workaround by replacing the statements with one that is supported.

Active Discussions

Lansweeper Number Of Application Hang Event ID 1002 Count , Null
by  HRS   Go to last post Go to first unread
Last post: 11/29/2019 9:42:49 PM(UTC)
Action Change Windows domain PC Name
by  DaveDischord  
Go to last post Go to first unread
Last post: 11/27/2019 10:36:02 PM(UTC)
Lansweeper LsRemote.exe Background Image Removal
by  Martin Frey   Go to last post Go to first unread
Last post: 11/27/2019 11:40:23 AM(UTC)
Lansweeper List of Software Publisher´s
by  fuesselorg  
Go to last post Go to first unread
Last post: 11/22/2019 5:11:42 PM(UTC)
Lansweeper Infopath installer help
by  Dave Ward   Go to last post Go to first unread
Last post: 11/12/2019 11:16:51 AM(UTC)
Lansweeper Remote Registry 2019
by  gareauk  
Go to last post Go to first unread
Last post: 10/24/2019 7:33:06 PM(UTC)
Lansweeper Deploy
by  CyberCitizen   Go to last post Go to first unread
Last post: 10/10/2019 2:31:27 AM(UTC)
Action Backup Computer with Disk2VHD to network share
by  pryan67  
Go to last post Go to first unread
Last post: 10/7/2019 3:36:05 PM(UTC)