→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

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
Esben_D
Lansweeper Employee
Lansweeper Employee
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.
RayHeathTSS
Engaged Sweeper II
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 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
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


Trelor89
Engaged Sweeper II
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
PeterJG
Champion Sweeper II
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
KevinA-REJIS
Champion Sweeper III
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
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


SecqureIT
Engaged Sweeper II
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