Self-Updating Report: Bios Out of Date

[wgknowles]

Here is a report to help keep your bios revisions up to date and compliant.

This is a 2 part report that will automatically update whenever a new bios version is detected on the network

Part 1: What's the latest bios revision?
Report Name: MaxBiosVersion - IMPORTANT - USE THIS NAME!!

SELECT TOP 100 PERCENT Query.Model, max(Query.SMBIOSBIOSVersion) LatestRev, Max(Query.ReleaseDate) MaxReleaseDate FROM ((SELECT tblBIOS.ReleaseDate, tblBIOS.SMBIOSBIOSVersion, tblComputersystem.Model FROM tblComputersystem INNER JOIN tblComputers ON tblComputers.Computername = tblComputersystem.Computername INNER JOIN tblBIOS ON tblComputers.Computername = tblBIOS.Computername)) Query GROUP BY Query.Model ORDER BY Query.Model

Part 2: Which computers are out of date?

Select Top 1000000 tblcomputers.Computername, tblcomputers.ComputerUnique, dbo.tblComputersystem.Model, dbo.tblBIOS.SMBIOSBIOSVersion As InstalledBIOS, dbo.web30repMaxBiosVersion.LatestRev As LatestBIOS, dbo.tblBIOS.Lastchanged From dbo.tblBIOS Inner Join dbo.tblComputersystem On dbo.tblBIOS.Computername = dbo.tblComputersystem.Computername Inner Join dbo.web30repMaxBiosVersion On dbo.tblComputersystem.Model = dbo.web30repMaxBiosVersion.Model And dbo.tblBIOS.ReleaseDate < dbo.web30repMaxBiosVersion.MaxReleaseDate Inner Join tblcomputers On tblcomputers.Computername = dbo.tblBIOS.Computername And tblcomputers.Computername = dbo.tblComputersystem.Computername

This is an v4 compatible update to an old post:
http://www.lansweeper.com/forum/yaf_postst2164_Bios-information.aspx

[Hemoco]

Nice one.

Try this to merge everything into one query:
There is also a little change to filter out same bios versions with different release date.

Select Top 1000000 tblcomputers.Computername, tblcomputers.ComputerUnique,   dbo.tblComputersystem.Model, dbo.tblBIOS.SMBIOSBIOSVersion As InstalledBIOS,   MaxBios.LatestRev As LatestBIOS, dbo.tblBIOS.Lastchanged From dbo.tblBIOS Inner Join   dbo.tblComputersystem On dbo.tblBIOS.Computername =     dbo.tblComputersystem.Computername Inner Join   (Select Query.Model, Max(Query.SMBIOSBIOSVersion) LatestRev,       Max(Query.ReleaseDate) MaxReleaseDate     From ((Select tblBIOS.ReleaseDate, tblBIOS.SMBIOSBIOSVersion,           tblComputersystem.Model         From tblComputersystem Inner Join           tblComputers On tblComputers.Computername =             tblComputersystem.Computername Inner Join           tblBIOS On tblComputers.Computername = tblBIOS.Computername)) Query     Group By Query.Model) MaxBios On dbo.tblComputersystem.Model = MaxBios.Model     And dbo.tblBIOS.ReleaseDate < MaxBios.MaxReleaseDate Inner Join   tblcomputers On tblcomputers.Computername = dbo.tblBIOS.Computername     And tblcomputers.Computername = dbo.tblComputersystem.Computername Where dbo.tblBIOS.SMBIOSBIOSVersion <> MaxBios.LatestRev Order By dbo.tblComputersystem.Model, tblcomputers.ComputerUnique