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

[dshu]

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

This doesn't work for me, it yields an error of

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

[fernando_w]

Hello Guys,

I got the same error here:

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

Any advice?

dshu wrote:

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

This doesn't work for me, it yields an error of

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

[AZHockeyNut]

I get a nice chart now, still playing with the report. However I kept getting an error about Date/Time. FYI I had to change one line to make it run.

I changed this line :
Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
to this line:
Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,

[dshonwood]

I get the following error when importing this code replacing my report name...


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



Select Case
When WindowsUpdateOverview.lastPatchDate >= DateAdd(day, -7,
GetDate()) Then '1. less than a week'
When WindowsUpdateOverview.lastPatchDate < DateAdd(day, -7,
GetDate()) And WindowsUpdateOverview.lastPatchDate >= DateAdd(day,
-30, GetDate()) Then '2. less than a month'
When WindowsUpdateOverview.lastPatchDate < DateAdd(day, -31,
GetDate()) And WindowsUpdateOverview.lastPatchDate >= DateAdd(

[AZHockeyNut]

dshonwood wrote:
I get the following error when importing this code replacing my report name...


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



Select Case
When WindowsUpdateOverview.lastPatchDate >= DateAdd(day, -7,
GetDate()) Then '1. less than a week'
When WindowsUpdateOverview.lastPatchDate < DateAdd(day, -7,
GetDate()) And WindowsUpdateOverview.lastPatchDate >= DateAdd(day,
-30, GetDate()) Then '2. less than a month'
When WindowsUpdateOverview.lastPatchDate < DateAdd(day, -31,
GetDate()) And WindowsUpdateOverview.lastPatchDate >= DateAdd(

so that means you do not have the report created as a DBView. I am struggling with that as well. When I figure it out I will try to post a better solution

[MichaelUnify]

How do you implement that in lansweeper? tried dumping it into the report writer in lansweeper but that doesnt seem to be it. Very little SQL experience here.

[Martyn_Umpleby]

Hello,

Thanks for this, only I have an issue that you might of mentioned.

I seem to have the issue with the date format;

"Error: There was a syntax error in the date format. [ Expression = 01cd6ee70f8d241f ]"


Only some of my hosts dates have the "Installed on" date in a un-readable format e.g. "01cd6ee70f8d241f, 01cd6daa6140a8a4"

Is there any chance to add exception in this query to no-readable format.

Thanks.


Martyn

[chrsch]

hi there,
any chance to convert this to work with dd-mm-yyyy format?

thanks!