Notification

Icon
Error

Microsoft Patch Tuesday Report – December 2018

Posted: Wednesday, December 12, 2018 3:18:21 PM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 1,930
7
Like
Hi Everyone,

A new patch Tuesday has arrived with new updates to the report too! The report checks if assets in your network are on the latest Windows monthly roll-up (or security) update released on this patch Tuesday. If you want more information about what is included in this update, feel free to visit the related blog post.

The report is color-coded to give you an easy and quick overview which assets are already on the latest Windows update (excluding anything older than Windows 7 SP1).

If you have any suggestions which might improve the report for future use, feel free to post your suggestion. You can find the report for last month here.

Changes made thanks to last month's suggestions:
  • Only Active assets are displayed.
  • Suggested KB installs only show when an asset is out of date now.
  • Moved the patch status column forward for ease of use.
  • Added a Distinct function to prevent duplicate results.
  • Added a Server/Workstation filter field.
  • Excluded Windows Server 2003.
Instructions to add this report to your Lansweeper installation can be found here: https://www.lansweeper.c...ow-to-run-a-report.aspx

Code:
Select Distinct Top 1000000 Coalesce(tsysOS.Image,
  tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblState.Statename As State,
  Case tblAssets.AssetID
    When SubQuery1.AssetID Then 'Up to date'
    Else 'Out of date'
  End As [Patch status],
  Case
    When tblComputersystem.Domainrole > 1 Then 'Server'
    Else 'Workstation'
  End As [Workstation/Server],
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  Case
    When tsysOS.OScode Like '10.0.10240%' Then '1507'
    When tsysOS.OScode Like '10.0.10586%' Then '1511'
    When tsysOS.OScode Like '10.0.14393%' Then '1607'
    When tsysOS.OScode Like '10.0.15063%' Then '1703'
    When tsysOS.OScode Like '10.0.16299%' Then '1709'
    When tsysOS.OScode Like '10.0.17134%' Then '1803'
    When tsysOS.OScode Like '10.0.17763%' Then '1809'
  End As Version,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblErrors.ErrorText Is Not Null Or
      tblErrors.ErrorText != '' Then
      'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
    Else ''
  End As ScanningErrors,
  Case
    When tblAssets.AssetID = SubQuery1.AssetID Then ''
    Else Case
        When tsysOS.OSname = 'Win 2008' Then 'KB4471325 or KB4471319'
        When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
          tsysOS.OSname = 'Win 2008 R2' Then 'KB4471318 or KB4471328'
        When tsysOS.OSname = 'Win 2012' Or
          tsysOS.OSname = 'Win 8' Then 'KB4471330 or KB4471326'
        When tsysOS.OSname = 'Win 8.1' Or
          tsysOS.OSname = 'Win 2012 R2' Then 'KB4471320 or KB4471322'
        When tsysOS.OScode Like '10.0.10240' Then 'KB4471323'
        When tsysOS.OScode Like '10.0.10586' Then 'KB4093109'
        When tsysOS.OScode Like '10.0.14393' Or
          tsysOS.OSname = 'Win 2016' Then 'KB4471321'
        When tsysOS.OScode Like '10.0.15063' Then 'KB4471327'
        When tsysOS.OScode Like '10.0.16299' Then 'KB4471329'
        When tsysOS.OScode Like '10.0.17134' Then 'KB4471324'
        When tsysOS.OScode Like '10.0.17763' Then 'KB4471332'
      End
  End As [Install one of these updates],
  Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
  GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
  Case
    When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
      GetDate())) > 3 Then
      'Windows update information may not be up to date. We recommend rescanning this machine.'
    Else ''
  End As Comment,
  Case tblAssets.AssetID
    When SubQuery1.AssetID Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
      From tblQuickFixEngineering
        Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
          = tblQuickFixEngineering.QFEID
      Where tblQuickFixEngineeringUni.HotFixID In ('KB4471318', 'KB4471328',
        'KB4471325', 'KB4471319', 'KB4471330', 'KB4471326', 'KB4471320',
        'KB4471322', 'KB4471323', 'KB4093109', 'KB4471321', 'KB4471327',
        'KB4471329', 'KB4471324', 'KB4471332')) As SubQuery1 On
    tblAssets.AssetID = SubQuery1.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
    tblAssets.AssetID
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
        TsysLastscan.Lasttime As QuickFixLastScanned
      From TsysWaittime
        Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
        Inner Join tblAssets On tblAssets.AssetID = TsysLastscan.AssetID
      Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
    tblAssets.AssetID = QuickFixLastScanned.ID
  Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
        Max(tblErrors.Teller) As ErrorID
      From tblErrors
        Inner Join tblAssets On tblAssets.AssetID = tblErrors.AssetID
      Group By tblAssets.AssetID) As ScanningError On tblAssets.AssetID =
    ScanningError.ID
  Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
  Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
    tblErrors.ErrorType
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
      From tblAssets Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
      Where tsysOS.OSname Like 'Win 7%' And tblAssets.SP = 0) And
  tsysOS.OSname != 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
  tsysOS.OSname Not Like '%2003%' And tsysAssetTypes.AssetTypename Like
  'Windows%' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName
rasldasl
#1rasldasl Member Posts: 13  
posted: 12/12/2018 11:20:50 PM(UTC)
Charles - Great job. Last month you added a "patch status" column which is great to filter on. Unless I'm missing something (entirely possible) you can't filter on color. I don't see that column this month.
Esben.D
#2Esben.D Member Administration Original PosterPosts: 1,930  
posted: 12/13/2018 10:38:31 AM(UTC)
It's been moved up (see change notes). It now comes after state.
rasldasl
#3rasldasl Member Posts: 13  
posted: 12/13/2018 4:40:00 PM(UTC)
Originally Posted by: Charles.X Go to Quoted Post
It's been moved up (see change notes). It now comes after state.


d'oh! Thanks!! Dancing
TheITGuy
#4TheITGuy Member Posts: 19  
posted: 12/13/2018 5:26:27 PM(UTC)
Quick Question.

Does the latest report make the previous months report obsolete?


What i mean is, If a device is Green for December will it show Red in October and November reports?

Thanks.
Esben.D
#5Esben.D Member Administration Original PosterPosts: 1,930  
posted: 12/14/2018 9:31:50 AM(UTC)
Originally Posted by: TheITGuy Go to Quoted Post
Quick Question.

Does the latest report make the previous months report obsolete?

What i mean is, If a device is Green for December will it show Red in October and November reports?

Thanks.


Yes, the report checks if the computers have this month's updates installed or not. The old reports do the same for their respective month.

If you want to, you can copy the KB numbers from the previous month and put them into this month's report. That way if they are on this month's or last month's update, they will show up green.
darren.kimber@stockland.com.au
posted: 12/21/2018 5:50:00 AM(UTC)
These are awesome and the bosses love them. From Next month could you add a filter for device type such as Server and Workstation? I tried editing the Nov18 one using DomainRoles but my report writing isn't that great. Thanks!
Esben.D
#7Esben.D Member Administration Original PosterPosts: 1,930  
posted: 12/21/2018 9:51:17 AM(UTC)
Originally Posted by: darren.kimber@stockland.com.au Go to Quoted Post
These are awesome and the bosses love them. From Next month could you add a filter for device type such as Server and Workstation? I tried editing the Nov18 one using DomainRoles but my report writing isn't that great. Thanks!


Should already be in. It's next to the Patch Status column and it's called Workstation/Server.
Caleb
#8Caleb Member Posts: 13  
posted: 1/8/2019 7:46:20 PM(UTC)
Thank you for the report!

I also added system uptime to my report. Here is the code that works for me if anyone else is interested.

Code:
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
  24))) + ' days ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
  24))) + ' hours ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
  60))) + ' minutes' As [Uptime Since Last Reboot],

Active Discussions

Lansweeper Patch Tuesday report, last 3 months
by  JacobH   Go to last post Go to first unread
Last post: Today at 5:28:37 PM(UTC)
Lansweeper Report ALL office (ms officce,libreoffice,openoffice
by  AlexRus227  
Go to last post Go to first unread
Last post: Today at 8:37:07 AM(UTC)
Lansweeper Assets Missing Quickfix Data - Last 60 Days
by  Mike6436   Go to last post Go to first unread
Last post: Yesterday at 6:11:30 PM(UTC)
Lansweeper BlueKeep Vulnerability
by  jwood.mls  
Go to last post Go to first unread
Last post: Yesterday at 4:58:59 PM(UTC)
Lansweeper tblCPlogoninfo - None interactive logons?
by  Millslansweeper   Go to last post Go to first unread
Last post: 11/8/2019 1:32:12 PM(UTC)
Lansweeper Software : changes last 24 hours Enhancement
by  RC62N  
Go to last post Go to first unread
Last post: 11/7/2019 10:05:53 PM(UTC)
Lansweeper Adding Comments
by  CyberCitizen  
Go to last post Go to first unread
Last post: 11/5/2019 12:28:21 AM(UTC)