Notification

Icon
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

ToeJoe

Member Original PosterPosts: 14
6
Like
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):
Windows update rep.jpg
chrsch
#1chrsch 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!
Martyn Umpleby
#2Martyn 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
MichaelUnify
#3MichaelUnify 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.
dshonwood
#4dshonwood 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):
report_chart.PNG
AZHockeyNut
#5AZHockeyNut 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,

AZHockeyNut
#6AZHockeyNut Member Alpha Tester Posts: 236  
posted: 3/17/2017 8:49:17 PM(UTC)
Originally Posted by: dshonwood Go to Quoted Post
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

MeekLogic
#7MeekLogic 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
Ken_SP
#8Ken_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 Go to Quoted Post
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 Go to Quoted Post
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
dshu
#9dshu Member Posts: 28  
posted: 5/15/2017 4:21:28 PM(UTC)
Originally Posted by: Ken_SP Go to Quoted Post
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."
Sola
#10Sola 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.

fernando.w
#11fernando.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 Go to Quoted Post
Originally Posted by: Ken_SP Go to Quoted Post
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."


abetzold
#12abetzold 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
FixitDave
#13FixitDave 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.
Sola
#14Sola Member Posts: 2  
posted: 5/19/2017 7:53:21 AM(UTC)
I have checked all values not like '%/%' and fixed them manualy
FixitDave
#15FixitDave 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.
abetzold
#16abetzold Member Posts: 19  
posted: 5/19/2017 4:41:36 PM(UTC)
Originally Posted by: abetzold Go to Quoted Post
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())
FixitDave
#17FixitDave 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
myersac
#18myersac 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
FixitDave
#19FixitDave Member Posts: 52  
posted: 6/5/2017 10:54:19 PM(UTC)
Can you reply with the code for the report and chart
myersac
#20myersac 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
FixitDave
#21FixitDave 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
myersac
#22myersac Member Posts: 3  
posted: 6/6/2017 2:28:48 PM(UTC)
https:// .edu/report.aspx?det=web50rep9d6f500f17bb43fdbe33cf8e7c2e21d9&title=LastUpdatePatch
asm42
#23asm42 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.
jacob_bks
#24jacob_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)





abetzold
#25abetzold 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?
abetzold
#26abetzold 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
RobG
#27RobG 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
abetzold
#28abetzold 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,
RobG
#29RobG 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

Lansweeper Upgrade Win 10 build to version 2004
by  CyberCitizen   Go to last post Go to first unread
Last post: Today at 12:11:55 AM(UTC)
Lansweeper Sort Reports by Last Changed
by  LANGuy  
Go to last post Go to first unread
Last post: Yesterday at 7:02:23 PM(UTC)
Lansweeper Windows Encryption Method
by  elKastr0nom   Go to last post Go to first unread
Last post: Yesterday at 6:02:07 PM(UTC)
Lansweeper Built in report - AD Password Expired
by  FezUSA  
Go to last post Go to first unread
Last post: Yesterday at 3:12:17 PM(UTC)
Lansweeper Helpdesk Dashboard Shared Tab
by  mark chamberlain   Go to last post Go to first unread
Last post: Yesterday at 2:32:20 PM(UTC)
Lansweeper Uninstall an software with Password
by  CyberCitizen   Go to last post Go to first unread
Last post: Yesterday at 6:20:28 AM(UTC)
Lansweeper Scanning for Events in Applications and Services Logs
by  Geoff P G  
Go to last post Go to first unread
Last post: 10/20/2021 5:22:11 PM(UTC)