Notification

Icon
Error

Tweak LanSweepers Bios Version Audit to show Last Seen

Posted: Thursday, May 6, 2021 7:44:52 PM(UTC)
Tythesly

Tyler M.

Member Original PosterPosts: 31
1
Like
This issue has been solved! Click here to view the solution
Hello everybody... I need some help here.

https://www.lansweeper.c...port/bios-version-audit/

I cannot figure out why it wont let me add Last Seen to the report. It keeps giving me an error "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

Can anybody help me?
Brandon
#1Brandon Member Posts: 154  
posted: 5/6/2021 8:16:09 PM(UTC)
Try this.

Quote:
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.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 tblAssetCustom.State = 1
Order By tblAssets_1.AssetName
Tythesly
#2Tyler M. Member Original PosterPosts: 31  
posted: 5/6/2021 8:22:40 PM(UTC)
Originally Posted by: Brandon Go to Quoted Post
Try this.

Quote:
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.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 tblAssetCustom.State = 1
Order By tblAssets_1.AssetName


That's perfect thank you!

Active Discussions

Lansweeper Enterprise Options in Menu Bar/Configuration
by  mk@allan   Go to last post Go to first unread
Last post: 6/18/2021 7:38:43 PM(UTC)
Lansweeper No One getting back to me from Lansweeper
by  Kenneth Lindsay  
Go to last post Go to first unread
Last post: 6/18/2021 3:31:06 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: 6/18/2021 10:48:57 AM(UTC)
Lansweeper RPC Unavailable error
by  Greeno  
Go to last post Go to first unread
Last post: 6/17/2021 7:15:07 PM(UTC)
Lansweeper Exclude Search
Belarus  pryan67  
Go to last post Go to first unread
Last post: 6/16/2021 4:01:43 PM(UTC)
Lansweeper Report: All Apple Mac devices with Memory RAM asset
by  gabrielo   Go to last post Go to first unread
Last post: 6/16/2021 3:17:24 PM(UTC)
Lansweeper Does technical support for LS really respond?
by  tosch  
Go to last post Go to first unread
Last post: 6/16/2021 12:48:50 PM(UTC)