Hi All,

Just thought I would provide an update; I had more success with the following query

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
tblAssets.Lastseen
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where ISDATE(tblQuickFixEngineering.InstalledOn) = 1
And OSname Not In ('Win 7', 'Win XP', 'Win 8', 'Win 8.1', 'Win 10')
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By lastPatchDate Desc

This should return all your MS servers (or others outside the OSnames specified in the where clause) you can obviously remove "And OSname Not In ('Win 7', 'Win XP', 'Win 8', 'Win 8.1', 'Win 10')" if you want all systems.

I was not able to add "tblQuickFixEngineering.InstalledOn <= DateAdd(day, -90, GetDate())" to this where clause and eventually gave up, instead like the others above I created and saved this report to be queried by another

Select Top 1000000 [your report name].AssetName,
[your report name].OSname,
[your report name].Username,
[your report name].IPAddress,
[your report name].lastPatchDate
From [your report name]
Where [your report name].lastPatchDate <= DateAdd(day,
-90, GetDate())
Ordery By [your report name].AssetName

Thanks for the inspiration!!

Looks like we have to use the HAVING clause here for the date exclusion piece.



Hopes this helps someone.

Here's the color coded version if anyone needs it :) I'm not that fluent in SQL, but this does the job and it's still compatible with chart.



EDIT: corrected a color coding bug

Well, this report was included in the reports library. Nice!

https://www.lansweeper.com/report/latest-windows-update-audit-chart/