Windows update report

[ToeJoe]

I would like to share report on installed windows updates which shows details on windows assets plus date of latest windows update installed. In order to get this info, I used latest KB installed date in table yblQuickFixEngineering.

SELECT tblAssets.AssetID, tblAssets.AssetName, tsysOS.OSname, tblAssets.Username, tblAssets.IPAddress, tblAssets.Description,
	MAX(CONVERT(datetime, InstalledOn)) as lastPatchDate,
		tblAssets.Lastseen
FROM tblQuickFixEngineering 
JOIN tblAssets on tblAssets.AssetID=tblQuickFixEngineering.AssetID
join tsysOS on tblAssets.OScode = tsysOS.OScode
Group by tblAssets.AssetID, tblAssets.AssetName, tsysOS.OSname, tblAssets.Username, tblAssets.IPAddress, tblAssets.Description,
	tblAssets.Lastseen

Format of column InstalledOn is mm/dd/yyyy and Convert function nicely converts it to datetime. The issue here could be that you may have wrong date in some cell and convert might fail. You should note this report name (db view) because it will be required for the next query.

Next, you can create report with Chart: prefix. This may be better utilized SQL code but it was my first shot. In this query, I wanted only clients using where OSname In ('Win 7', 'Win 8', 'Win 8.1',
'Win 10'). You may customize date period as needed.

Select Case
    When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day, -7,
    GetDate()) Then '1. less than a week'
    When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate < DateAdd(day, -7,
    GetDate()) And PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day,
    -30, GetDate()) Then '2. less than a month'
    When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate < DateAdd(day, -31,
    GetDate()) And PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day,
    -90, GetDate()) Then '3. less than 3 months' Else '4. more than 3 months'
  End As DateRange,
  count(*) As NumberOfAssets
From PREVIOUSLYCREATEDREPORTNAME
Where PREVIOUSLYCREATEDREPORTNAME.OSname In ('Win 7', 'Win 8', 'Win 8.1',
  'Win 10')
Group By Case
    When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day, -7,
    GetDate()) Then '1. less than a week'
    When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate < DateAdd(day, -7,
    GetDate()) And PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day,
    -30, GetDate()) Then '2. less than a month'
    When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate < DateAdd(day, -31,
    GetDate()) And PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day,
    -90, GetDate()) Then '3. less than 3 months' Else '4. more than 3 months'
  End

Now you can call this report via Chart widget and get nice dashboard report on windows updates on clients

[abetzold]

Change this line from Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As lastPatchDate, to Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,

[RobG]

These reports are great but is anyone else finding the dates are wrong when compared to looking up qfe, for example if you run the following in powershell

Get-WmiObject -Class Win32_QuickFixEngineering | Select-Object -Property Description, HotFixID, InstalledOn | Format-Table -Autosize

Look for your most recent date and compare that to above reports, I find that the date on the reports doesn't match with any entries in qfe

However when I check the QuickFix within Lansweeper (Select an Asset | Click Windows | Click QuickFix) these dates match with powershell, so the information must be there, could it be the conversion or Max is failing?

The report Im using has an added line to only retrieve results older than 90 days

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
Max(Convert(date,tblQuickFixEngineering.Lastchanged)) As lastPatchDate,
tblAssets.Lastseen
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblQuickFixEngineering.InstalledOn Like '%/%' And
tblQuickFixEngineering.Lastchanged <= DateAdd(day, -90, GetDate())
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By lastPatchDate

I believe to resolve the issue the date queried shouldn't be lastchanged but instead InstalledOn, as these values are often more recent, but I've come across an error when trying to query results older than 90 days

Conversion failed when converting date and/or time from character string.

The query :

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
tblAssets.Lastseen
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblQuickFixEngineering.InstalledOn Like '%/%' And
tblQuickFixEngineering.InstalledOn <= DateAdd(day, -90, GetDate())
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By lastPatchDate

[abetzold]

This is my query:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As LastPatchDate,
SubQuery1.Value As 'Last Auto Update',
tblAssets.Lastseen,
tblAssetCustom.Custom1 As 'Patching Comments',
tblAssetCustom.Custom2 As 'Patching Schedule',
tblAssetCustom.Custom3 As 'Technical Contact'
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Value
From tblRegistry
Where
tblRegistry.Regkey Like
'%SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install') SubQuery1 On SubQuery1.AssetID = tblAssets.AssetID
Where (tblQuickFixEngineering.InstalledOn Like '%/%' or tblQuickFixEngineering.InstalledOn = '') And
tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
SubQuery1.Value,
tblAssets.Lastseen,
tblAssetCustom.Custom1,
tblAssetCustom.Custom2,
tblAssetCustom.Custom3,
tblAssets.Description
Having tblAssets.AssetName Not Like 'TS%'
Order By LastPatchDate

for the 20 server 2008 servers that I have reporting back in hex I need to know how to convert the tblquickfixengineering.installedon from hex to datetime

[abetzold]

2008 and prior would report a hex value in the tblquickfixengineering.installedon

Anyone know how to convert that to datetime?

[jacob_bks]

Great post - thank you!

to have a chart by IP Location (I use prefixes and standard naming convention in my IP Location names to assist in grouping), and windows servers only, and don't forget active servers...

In the original report you base the chart on,


You can inner join tblAssetCustom, and inner join tblComputersystem, and left join tsysIPLocations


and add the criteria in the WHERE clause

tsysIPLocations.IPLocation Like 'IPLOCATONHERE%'
And
tblComputersystem.Domainrole > 1
And
tblAssetCustom.State = 1


Then of course you can remove (or edit to like 'Win 2%') the where OSNAME = in the chart query

Windows 2003 servers will likely show not being patched in a really long time unless you did the wannacrypt patch (hopefully you did)

[asm42]

Once I moved my database from the compact sql to SQL Server express I was able to load the chart.

[myersac]

https:// .edu/report.aspx?det=web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9&title=LastUpdatePatch

[FixitDave]

I can only assume the web code link is incorrect, can you post the full URL for the LastUpdatePatch report

[myersac]

This report is saved as LastUpdatePatch

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
tblAssets.Lastseen
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblQuickFixEngineering.InstalledOn Like '%/%'
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By lastPatchDate


This is the code that is giving me the error "Invalid SELECT statement. Unknown object name: "web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9".: Unexpected token "web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9" at line 0, column -1" and I can not save it.

Select Top 1000000 Case
When web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9.lastPatchDate >= DateAdd(day,
-30, GetDate()) Then '1. Within 1 month'
When web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9.lastPatchDate < DateAdd(day,
-31, GetDate()) And web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9.lastPatchDate
>= DateAdd(day, -60, GetDate()) Then '2. Within 2 months'
When web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9.lastPatchDate < DateAdd(day,
-61, GetDate()) And web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9.lastPatchDate
>= DateAdd(day, -90, GetDate()) Then '3. Within 3 months'
Else '4. more than 3 months' End As DateRange,
count(*) As NumberOfAssets
From web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9
Group By Case
When web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9.lastPatchDate >= DateAdd(day,
-30, GetDate()) Then '1. Within 1 month'
When web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9.lastPatchDate < DateAdd(day,
-31, GetDate()) And web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9.lastPatchDate
>= DateAdd(day, -60, GetDate()) Then '2. Within 2 months'
When web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9.lastPatchDate < DateAdd(day,
-61, GetDate()) And web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9.lastPatchDate
>= DateAdd(day, -90, GetDate()) Then '3. Within 3 months'
Else '4. more than 3 months' End

[FixitDave]

Can you reply with the code for the report and chart