Tweak LanSweepers Bios Version Audit to show Last Seen

[Tythesly]

Hello everybody... I need some help here.

https://www.lansweeper.com/report/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_jones · Accepted solution]

Try this.

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]

Brandon wrote:
Try this.

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!