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

[myersac]

Why do I get this error? I have replaced with my page code

Invalid SELECT statement. Unknown object name: "web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9".: Unexpected token "web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9" at line 0, column -1

[FixitDave]

Looks like the cell had data with 16 hex chars, so I used the following sql code to change it;

update tblQuickFixEngineering
set InstalledOn = ''
where len(InstalledOn)>10

I still had issue, so amended the code, here is my working sql;

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 '%/%'
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssets.Lastseen
Order By lastPatchDate

Here is the Chart code...must save this with "Chart:" at the start of the title;

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

Replace webXXXXXXXXXXXXXX with the page code from upper report...eg the bold text from my example...

http://lansweeper/report.aspx?det=web50rep31d4d1c95f3d42a5b34325daca965d28&title=Last+Patch+Installed

I have amended the chart for within 1,2,3 and over 3 months as I thought 7 days was a little too early.

When you’re in Lansweeper,
• add a widget to the page
• Select Pie chart
• Select "Chart: Last Patch Installed"
• Legend on right or left
• Save the widget

[FixitDave]

I have mm/dd/yyyy or blanks, have used the following code to add a fake date;

update tblQuickFixEngineering
set InstalledOn = '1/1/2013'

where len(InstalledOn) < 1

Any new updates to the table are not populated, so I can't use this...which is a shame...

I did notice the LastChanged cell always had a date, I've used that in the report. I don't get an error, but I get "This report has no results".

I am in the UK and so my local date is DD/MM/YYYY if that makes a difference.

[Sola]

I have checked all values not like '%/%' and fixed them manualy

[FixitDave]

Has anybody been able to fix the following error?

Error: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

[abetzold]

I am struggling with the chart on this one. The numbers just do not align with the report. Here is my code:

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
Having tblAssets.AssetName Not Like 'TS%'
Order By lastPatchDate

I have 18 Assets over 90 years yet the next query reports that I have 166 assets over 90 days. Can anyone help? I can send the excel report if needed.

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

[abetzold]

abetzold wrote:
I am struggling with the chart on this one. The numbers just do not align with the report. Here is my code:

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
Having tblAssets.AssetName Not Like 'TS%'
Order By lastPatchDate

I have 18 Assets over 90 years yet the next query reports that I have 166 assets over 90 days. Can anyone help? I can send the excel report if needed.

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

I resolved the inaccurate counts using this code for the chart:

Select Top 1000000 '1. Patched less than a week ago' As patch_time,
  count(*) As NumberOfAssets
From web50repa76b58d39adc4e4a89cd352473add01c
Where web50repa76b58d39adc4e4a89cd352473add01c.lastPatchDate >= DateAdd(day, -7,
  GetDate())
Union All
Select '2. Patched less than a month ago' As patch_time,
  count(*) As NumberOfAssets
From web50repa76b58d39adc4e4a89cd352473add01c
Where web50repa76b58d39adc4e4a89cd352473add01c.lastPatchDate < DateAdd(day, -7,
  GetDate()) And web50repa76b58d39adc4e4a89cd352473add01c.lastPatchDate >=
  DateAdd(month, -1, GetDate())
Union All
Select '3. Patched less than 3 months ago' As patch_time,
  count(*) As NumberOfAssets
From web50repa76b58d39adc4e4a89cd352473add01c
Where web50repa76b58d39adc4e4a89cd352473add01c.lastPatchDate < DateAdd(month,
  -1, GetDate()) And web50repa76b58d39adc4e4a89cd352473add01c.lastPatchDate >=
  DateAdd(month, -3, GetDate())
Union All
Select '4. Patched more than 3 months ago' As patch_time,
  count(*) As NumberOfAssets
From web50repa76b58d39adc4e4a89cd352473add01c
Where web50repa76b58d39adc4e4a89cd352473add01c.lastPatchDate <= DateAdd(month,
  -3, GetDate())

[Sola]

Hello guys.
Thanks for your report.
I have some problem with date conversion without Where tblQuickFixEngineering.InstalledOn Like '%/%' criteria and I have looked what the problem with that small report

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname As OS,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblQuickFixEngineering.InstalledOn As lastPatchDate,
  tblQuickFixEngineeringUni.FixComments,
  tblQuickFixEngineeringUni.Description As Description1,
  tblQuickFixEngineering.QFEID
From tblQuickFixEngineering
  Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
    tblQuickFixEngineering.QFEID
Where tblQuickFixEngineering.InstalledOn Not Like '%/%'

and it shows me that some updates has date in format dd.mm.yyyy instead of dd/mm/yyyy and some of them has no date.
Now i'm planing to update SQL table and change all filled data to dd/mm/yyyy format and fill all empty cells with 01/01/2001.

[MeekLogic]

I liked this report but wanted it to sort by LastPatchDate. I added this SQL to the end of the first report to accomplish it, figured I should share.

Order By lastPatchDate Desc

You can change to Asc if you want it from oldest to newest.

The code now looks like this:

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
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssets.Lastseen
Order By lastPatchDate Desc

[Ken_SP]

This is my version to fix the problem where the date may not exist or actually not a date.

Please create a new report with the following 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 '%/%'
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssets.Lastseen

If you want to sort this report, you can follow MeekLogic's post:

MeekLogic wrote:
I liked this report but wanted it to sort by LastPatchDate. I added this SQL to the end of the first report to accomplish it, figured I should share.

Order By lastPatchDate Desc

You can change to Asc if you want it from oldest to newest.

If you want to add the chart to dashboard:

When finished creating the report, run it once, and note the report id in the URL like this:

http://lansweeper/report.aspx?det=(your report id here as a long string)

Now add a new report and use the code in ToeJoe's post, replacing PREVIOUSLYCREATEDREPORTNAME with the id you noted above:

ToeJoe wrote:
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