This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

BIOS version check

Daniel_B
Lansweeper Alumni

‎05-04-2015 11:59 AM

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
Esben_D
Lansweeper Employee
Lansweeper Employee

‎05-22-2019 02:22 PM

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.
0 Kudos
RayHeathTSS
Engaged Sweeper II

‎05-15-2019 03:06 PM

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?
0 Kudos
RayHeathTSS
Engaged Sweeper II
In response to RayHeathTSS

‎05-15-2019 03:15 PM

RayHeathTSS wrote:
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
0 Kudos
Trelor89
Engaged Sweeper II
In response to RayHeathTSS

‎05-15-2019 06:40 PM

I think that the partition query requires SQL Server Standard or higher
RayHeathTSS wrote:
RayHeathTSS wrote:
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


0 Kudos
Trelor89
Engaged Sweeper II

‎05-14-2019 02:49 PM

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
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
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
0 Kudos
PeterJG
Champion Sweeper II

‎05-14-2019 02:07 PM

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

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
0 Kudos
KevinA-REJIS
Champion Sweeper III

‎05-13-2019 05:49 PM

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!

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
0 Kudos
Trelor89
Engaged Sweeper II
In response to KevinA-REJIS

‎05-13-2019 11:47 PM

And that is exactly why the code wouldn't work for the other bit. Thanks for catching that..

All Machines with their BIOS
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

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


KevinA-REJIS wrote:
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!

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


0 Kudos
SecqureIT
Engaged Sweeper II
In response to Trelor89

‎05-14-2019 05:07 PM

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 wrote:
And that is exactly why the code wouldn't work for the other bit. Thanks for catching that..

All Machines with their BIOS
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

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


KevinA-REJIS wrote:
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!

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




0 Kudos

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now