Get an Overview Chart of All Computers and Their Latest Windows Update on Your Network
Find out when your Windows device was last updated and discover devices with potential update issues. This chart report will provide an overview of how many assets were last updated in the last week, the last month, the last 3 months or older. Spot computers that are falling behind in patching easier by using this audit in a chart widget on your dashboard.
For the standard version of this report, you can use the Last Windows Update report.
Please note that his report can only be used in SQL Server installations. To use this in a chart widget, prefix the report name with “Chart:” and select it in the chart report widget found on a dashboard.
Last Windows Update Chart Query
Select Top 1000000 Case
When LastPatchDate.lastPatchDate >= DateAdd(day, -7, GetDate()) Then
'1. less than a week'
When LastPatchDate.lastPatchDate < DateAdd(day, -7, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -30, GetDate()) Then
'2. less than a month'
When LastPatchDate.lastPatchDate < DateAdd(day, -31, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -90, GetDate()) Then
'3. less than 3 months'
Else '4. more than 3 months'
End As DateRange,
count(*) As NumberOfAssets
From (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
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By lastPatchDate) As LastPatchDate
Group By Case
When LastPatchDate.lastPatchDate >= DateAdd(day, -7, GetDate()) Then
'1. less than a week'
When LastPatchDate.lastPatchDate < DateAdd(day, -7, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -30, GetDate()) Then
'2. less than a month'
When LastPatchDate.lastPatchDate < DateAdd(day, -31, GetDate()) And LastPatchDate.lastPatchDate >= DateAdd(day, -90, GetDate()) Then
'3. less than 3 months'
Else '4. more than 3 months'
End
