Custom report showing servers/workstation not updated within 30 days

[JSteele]

Hello, I'm new here and to Lansweeper. I'm interested in building a custom report showing any servers/workstations that have not had any MS updates installed in the past 30 days.

I've reviewed some postings here and have found a report that will show systems that HAVE been updated in the past 30 days but I'm looking for the reverse. The other forum post I'm referring to is here.

Any help would be appreciated.

Thanks,
Jon

[Hemoco · Accepted solution]

The report should work. Could you try running the report below instead. It lists the last update that occurred on each machine and when that update was scanned. The report is sorted by scanned date, in ascending order, so the machines with the oldest updates will be listed first. This should at least tell you how long ago the oldest update occurred.

Select Top 1000000 Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Computer, tblComputers.Domain, Web40OSName.OSname As OS, Web40OSName.SP, Web40OSName.OScode As Code, tblQuickFixEngineering.Description As [Last Fix], tblQuickFixEngineering.FixComments As [Last Fix Comment], tblQuickFixEngineering.HotFixID As [Last Fix ID], tblQuickFixEngineering.Lastchanged As [Last Fix Scanned] From tblComputers Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join (Select tblQuickFixEngineering.Computername, Max(tblQuickFixEngineering.Lastchanged) As [Last Update Scanned] From tblQuickFixEngineering Group By tblQuickFixEngineering.Computername) LastUpdates On LastUpdates.Computername = tblComputers.Computername Inner Join tblQuickFixEngineering On tblQuickFixEngineering.Computername = LastUpdates.Computername And tblQuickFixEngineering.Lastchanged = LastUpdates.[Last Update Scanned] Where tblComputers.Lastseen <> '' Order By tblQuickFixEngineering.Lastchanged

[JSteele]

Lansweeper wrote:
The report should work. Could you try running the report below instead. It lists the last update that occurred on each machine and when that update was scanned. The report is sorted by scanned date, in ascending order, so the machines with the oldest updates will be listed first. This should at least tell you how long ago the oldest update occurred.

Select Top 1000000 Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Computer, tblComputers.Domain, Web40OSName.OSname As OS, Web40OSName.SP, Web40OSName.OScode As Code, tblQuickFixEngineering.Description As [Last Fix], tblQuickFixEngineering.FixComments As [Last Fix Comment], tblQuickFixEngineering.HotFixID As [Last Fix ID], tblQuickFixEngineering.Lastchanged As [Last Fix Scanned] From tblComputers Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join (Select tblQuickFixEngineering.Computername, Max(tblQuickFixEngineering.Lastchanged) As [Last Update Scanned] From tblQuickFixEngineering Group By tblQuickFixEngineering.Computername) LastUpdates On LastUpdates.Computername = tblComputers.Computername Inner Join tblQuickFixEngineering On tblQuickFixEngineering.Computername = LastUpdates.Computername And tblQuickFixEngineering.Lastchanged = LastUpdates.[Last Update Scanned] Where tblComputers.Lastseen <> '' Order By tblQuickFixEngineering.Lastchanged

I just noticed something. The report is actually pulling the last time each system was "scanned" not the last time updates were installed.

Jon

[JSteele]

JSteele wrote:

Lansweeper wrote:
The report should work. Could you try running the report below instead. It lists the last update that occurred on each machine and when that update was scanned. The report is sorted by scanned date, in ascending order, so the machines with the oldest updates will be listed first. This should at least tell you how long ago the oldest update occurred.

Select Top 1000000 Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Computer, tblComputers.Domain, Web40OSName.OSname As OS, Web40OSName.SP, Web40OSName.OScode As Code, tblQuickFixEngineering.Description As [Last Fix], tblQuickFixEngineering.FixComments As [Last Fix Comment], tblQuickFixEngineering.HotFixID As [Last Fix ID], tblQuickFixEngineering.Lastchanged As [Last Fix Scanned] From tblComputers Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join (Select tblQuickFixEngineering.Computername, Max(tblQuickFixEngineering.Lastchanged) As [Last Update Scanned] From tblQuickFixEngineering Group By tblQuickFixEngineering.Computername) LastUpdates On LastUpdates.Computername = tblComputers.Computername Inner Join tblQuickFixEngineering On tblQuickFixEngineering.Computername = LastUpdates.Computername And tblQuickFixEngineering.Lastchanged = LastUpdates.[Last Update Scanned] Where tblComputers.Lastseen <> '' Order By tblQuickFixEngineering.Lastchanged

I just noticed something. The report is actually pulling the last time each system was "scanned" not the last time updates were installed.

Jon

I'm actually thinking I would like the report to display the last time any update was installed on each machine, only listing each machine once with the date of the last update installed. If that's possible. I'm playing around with the report but if you have an idea how to accomplish this I'd appreciate the help.

Thanks
Jon

[Hemoco]

JSteele wrote:
I would like the report to display the last time any update was installed on each machine, only listing each machine once with the date of the last update installed. If that's possible.

This would be difficult to accomplish, as install dates are pulled from the client machine's WMI, are not always available and are not in a valid SQL Server date format by default.
In our test environments there are some update entries where this field contains something other than a date. Filtering out all of this "bad" data would be very time-consuming.

[JSteele]

Lansweeper wrote:
The report should work. Could you try running the report below instead. It lists the last update that occurred on each machine and when that update was scanned. The report is sorted by scanned date, in ascending order, so the machines with the oldest updates will be listed first. This should at least tell you how long ago the oldest update occurred.

Select Top 1000000 Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Computer, tblComputers.Domain, Web40OSName.OSname As OS, Web40OSName.SP, Web40OSName.OScode As Code, tblQuickFixEngineering.Description As [Last Fix], tblQuickFixEngineering.FixComments As [Last Fix Comment], tblQuickFixEngineering.HotFixID As [Last Fix ID], tblQuickFixEngineering.Lastchanged As [Last Fix Scanned] From tblComputers Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join (Select tblQuickFixEngineering.Computername, Max(tblQuickFixEngineering.Lastchanged) As [Last Update Scanned] From tblQuickFixEngineering Group By tblQuickFixEngineering.Computername) LastUpdates On LastUpdates.Computername = tblComputers.Computername Inner Join tblQuickFixEngineering On tblQuickFixEngineering.Computername = LastUpdates.Computername And tblQuickFixEngineering.Lastchanged = LastUpdates.[Last Update Scanned] Where tblComputers.Lastseen <> '' Order By tblQuickFixEngineering.Lastchanged

This report works great and actually is probably a better option for what I'm looking for!

Thank you very much for the help, I appreciate it!!

Jon

[Hemoco]

Please use the report below for the information you are after. You may also be interested in running this registry scan, which verifies what your machines' Windows Update settings are.

Select Top 1000000 Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Computer, tblComputers.Domain, Web40OSName.OSname As OS, Web40OSName.SP, Web40OSName.OScode As Code, TsysLastscan.Lasttime As [Last Scan For Updates] From tblComputers Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join TsysLastscan On tblComputers.Computername = TsysLastscan.Computername Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode Where tblComputers.Computername Not In (Select tblQuickFixEngineering.Computername From tblQuickFixEngineering Where tblQuickFixEngineering.Lastchanged > GetDate() - 30) And TsysWaittime.CFGname = 'quickfix' And tblComputers.Lastseen <> '' Order By tblComputers.ComputerUnique

To use the specified report, do the following:
• Open the Lansweeper configuration console and select Reports & Alerts\Report Builder. Hit the “New” button.
• Copy the SQL code provided and paste it at the bottom of the newly created report, replacing the default SQL code.
• Click somewhere near tblComputers so the new code applies.
• Give the report a “View name” and a “Report name” and hit the “Save” button.
• Double-click on the report in the report list to see its results and export options. The report will also be listed in the web console under All Available Reports.

[JSteele]

Lansweeper wrote:
Please use the report below for the information you are after. You may also be interested in running this registry scan, which verifies what your machines' Windows Update settings are.

Select Top 1000000 Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Computer, tblComputers.Domain, Web40OSName.OSname As OS, Web40OSName.SP, Web40OSName.OScode As Code, TsysLastscan.Lasttime As [Last Scan For Updates] From tblComputers Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join TsysLastscan On tblComputers.Computername = TsysLastscan.Computername Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode Where tblComputers.Computername Not In (Select tblQuickFixEngineering.Computername From tblQuickFixEngineering Where tblQuickFixEngineering.Lastchanged > GetDate() - 30) And TsysWaittime.CFGname = 'quickfix' And tblComputers.Lastseen <> '' Order By tblComputers.ComputerUnique

To use the specified report, do the following:
• Open the Lansweeper configuration console and select Reports & Alerts\Report Builder. Hit the “New” button.
• Copy the SQL code provided and paste it at the bottom of the newly created report, replacing the default SQL code.
• Click somewhere near tblComputers so the new code applies.
• Give the report a “View name” and a “Report name” and hit the “Save” button.
• Double-click on the report in the report list to see its results and export options. The report will also be listed in the web console under All Available Reports.

Hello and thank you for the reply and report code.

I've followed the instructions and have created the report with your provided code. The report is returning "no data" and I know I have a lot of servers that have not been updated within the last 30 days.

Any ideas?

Thanks
Jon