Lansweeper » Finished User Reports » Windows update report

Join Now

Notification

Error

12 >

Windows update report - Report which shows list of windows assets and last windows update date in table plus pie chars

Posted: Tuesday, January 17, 2017 4:10:11 PM(UTC)

ToeJoe

Member Original PosterPosts: 14

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.

Code:


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.

Code:


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

ToeJoe attached the following image(s):

Options

#1 chrsch Member Posts: 1

posted: 1/20/2017 9:49:45 AM(UTC)

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

thanks!

#2 Martyn Umpleby Member Posts: 3

posted: 1/25/2017 9:07:15 PM(UTC)

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

#3 MichaelUnify Member Posts: 12

posted: 2/1/2017 8:42:57 AM(UTC)

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.

#4 dshonwood Member Posts: 4

posted: 2/22/2017 5:48:01 PM(UTC)

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(

dshonwood attached the following image(s):

#5 AZHockeyNut Member Alpha Tester Posts: 236

posted: 3/8/2017 4:07:25 PM(UTC)

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,

#6 AZHockeyNut Member Alpha Tester Posts: 236

posted: 3/17/2017 8:49:17 PM(UTC)

Originally Posted by: dshonwood

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

#7 MeekLogic Member Posts: 1

posted: 3/30/2017 7:58:54 PM(UTC)

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.

Code:

Order By lastPatchDate Desc

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

The code now looks like this:

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

#8 Ken_SP Member Posts: 2

posted: 4/7/2017 10:57:05 AM(UTC)

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:

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

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

Originally Posted by: 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.

Code:

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:

Originally Posted by: ToeJoe

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.

Code:

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

#9 dshu Member Posts: 28

posted: 5/15/2017 4:21:28 PM(UTC)

Originally Posted by: 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:

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

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."

#10 Sola Member Posts: 2

posted: 5/17/2017 10:54:25 AM(UTC)

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

Code:

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.

#11 fernando.w Member Posts: 1

posted: 5/17/2017 1:35:24 PM(UTC)

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?

Originally Posted by: dshu

Originally Posted by: 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:

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

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."

#12 abetzold Member Posts: 19

posted: 5/18/2017 7:55:57 PM(UTC)

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

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.

Code:

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

#13 FixitDave Member Posts: 52

posted: 5/18/2017 10:37:42 PM(UTC)

Has anybody been able to fix the following error?

Quote:

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

#14 Sola Member Posts: 2

posted: 5/19/2017 7:53:21 AM(UTC)

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

#15 FixitDave Member Posts: 52

posted: 5/19/2017 10:14:28 AM(UTC)

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

Code:

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.

#16 abetzold Member Posts: 19

posted: 5/19/2017 4:41:36 PM(UTC)

Originally Posted by: abetzold

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

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.

Code:

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:

Code:


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())

#17 FixitDave Member Posts: 52

posted: 5/19/2017 11:19:46 PM(UTC)

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

Code:


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

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

Code:


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;

Code:


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

#18 myersac Member Posts: 3

posted: 6/5/2017 9:08:51 PM(UTC)

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

#19 FixitDave Member Posts: 52

posted: 6/5/2017 10:54:19 PM(UTC)

Can you reply with the code for the report and chart

#20 myersac Member Posts: 3

posted: 6/6/2017 2:11:22 PM(UTC)

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

#21 FixitDave Member Posts: 52

posted: 6/6/2017 2:21:29 PM(UTC)

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

#22 myersac Member Posts: 3

posted: 6/6/2017 2:28:48 PM(UTC)

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

#23 asm42 Member Posts: 1

posted: 7/7/2017 6:01:04 PM(UTC)

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

#24 jacob_bks Member Posts: 49

posted: 8/5/2017 4:43:49 PM(UTC)

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)

#25 abetzold Member Posts: 19

posted: 1/26/2018 6:33:57 PM(UTC)

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

Anyone know how to convert that to datetime?

#26 abetzold Member Posts: 19

posted: 1/26/2018 6:40:07 PM(UTC)

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

#27 RobG Member Posts: 3

posted: 3/5/2018 9:11:17 PM(UTC)

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

#28 abetzold Member Posts: 19

posted: 3/5/2018 10:42:50 PM(UTC)

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

#29 RobG Member Posts: 3

posted: 3/5/2018 11:26:00 PM(UTC)

Thanks for your reply,

I tried the different date formats but still unable to query results over X amount of days.

I believe the problem is the column data type, the InstalledOn is nvarchar but ideally should be datetime.

12 >

Active Discussions

Last user, login time and null entries
by cross_eur

Last post: Yesterday at 5:44:36 PM(UTC)

I need a Report of 32-bit applications in the environment created
by Colby Burdick

Last post: Yesterday at 5:32:06 PM(UTC)

Report Merge
by EnjetIT

Last post: Yesterday at 5:31:06 PM(UTC)

Software per AD group
by CPappas

Last post: Yesterday at 4:00:40 PM(UTC)

Stale Tickets
by brodiemac-too

Last post: 7/28/2021 8:47:09 PM(UTC)

MacOS IOMobileFrameBuffer 0-day Report
by Esben.D

Last post: 7/28/2021 2:56:46 PM(UTC)

SeriousSAM Vulnerability
by Esben.D

Last post: 7/28/2021 9:40:05 AM(UTC)

Building "Basic" Report User Assets
by Cripple.Zero

Last post: 7/27/2021 8:16:50 PM(UTC)

Notification

Windows update report - Report which shows list of windows assets and last windows update date in table plus pie chars

ToeJoe

Active Discussions

Product

Company

Help

Contact

Social Media