Notification

Icon
Error

Microsoft Patch Tuesday Report – January 2019

Posted: Wednesday, January 9, 2019 3:07:26 PM(UTC)
Esben.D

Esben.D

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

To start, happy new year. Now onto business. Another month, another Patch Tuesday report. This 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 since last month's report:
  • Added Windows Server 2019
Instructions to add this report to your Lansweeper installation can be found here: https://www.lansweeper.c...How-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 'KB4480968 or KB4480957'
        When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
          tsysOS.OSname = 'Win 2008 R2' Then 'KB4480970 or KB4480960'
        When tsysOS.OSname = 'Win 2012' Or
          tsysOS.OSname = 'Win 8' Then 'KB4480975 or KB4480972'
        When tsysOS.OSname = 'Win 8.1' Or
          tsysOS.OSname = 'Win 2012 R2' Then 'KB4480963 or KB4480964'
        When tsysOS.OScode Like '10.0.10240' Then 'KB4480962'
        When tsysOS.OScode Like '10.0.10586' Then 'KB4093109'
        When tsysOS.OScode Like '10.0.14393' Or
          tsysOS.OSname = 'Win 2016' Then 'KB4480961'
        When tsysOS.OScode Like '10.0.15063' Then 'KB4480973'
        When tsysOS.OScode Like '10.0.16299' Then 'KB4480978'
        When tsysOS.OScode Like '10.0.17134' Then 'KB4480966'
        When tsysOS.OScode Like '10.0.17763' Or
          tsysOS.OSname = 'Win 2019' Then 'KB4480116'
      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 ('KB4480968', 'KB4480957',
        'KB4480970', 'KB4480960', 'KB4480975', 'KB4480972', 'KB4480963',
        'KB4480964', 'KB4480962', 'KB4093109', 'KB4480961', 'KB4480973',
        'KB4480978', 'KB4480966', 'KB4480116')) 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
EagleEyeJoe
#1EagleEyeJoe Member Posts: 5  
posted: 1/9/2019 8:00:15 PM(UTC)
This is a great one - thanks again for the updates.

As an aside, I add a != 'Up of date' to the patch status so that it returns only the units in need.

Run time switch? Show all versus Show Out of date?

lrea
#2lrea Member Posts: 2  
posted: 1/9/2019 8:44:50 PM(UTC)
Originally Posted by: EagleEyeJoe Go to Quoted Post
This is a great one - thanks again for the updates.

As an aside, I add a != 'Up of date' to the patch status so that it returns only the units in need.

Run time switch? Show all versus Show Out of date?




Where are you putting that != in the SQL?
EagleEyeJoe
#3EagleEyeJoe Member Posts: 5  
posted: 1/9/2019 9:25:14 PM(UTC)
Not in the SQL exactly... In the table structure, middle of the page when you are editing the report, it has the header:

"Visible Expression Column Name Sort Type Sort Order Aggregate Grouping Criteria etc."

In that 'Criteria' column on the line that has the 'Patch Status'.

Active Discussions

Lansweeper Active Assets Versus Non-Active
by  RC62N   Go to last post Go to first unread
Last post: Yesterday at 9:29:26 PM(UTC)
Report Center Low Hard Disk Space (coloured) - Win+Linux
by  Olivier J.  
Go to last post Go to first unread
Last post: Yesterday at 4:16:02 PM(UTC)
Lansweeper check if process is running report
by  Randomusername   Go to last post Go to first unread
Last post: Yesterday at 3:29:16 PM(UTC)
Lansweeper Top 10 users submitting tickets
by  ChuckSchurman  
Go to last post Go to first unread
Last post: 8/22/2019 3:53:21 AM(UTC)
Lansweeper Identifying weak protocols
by  Jason Tree   Go to last post Go to first unread
Last post: 8/20/2019 3:06:03 PM(UTC)
Lansweeper Ticket Report Overview
by  briangmiller  
Go to last post Go to first unread
Last post: 8/20/2019 1:49:35 PM(UTC)
Lansweeper switch ports with multiple assets
by  FischbachM   Go to last post Go to first unread
Last post: 8/19/2019 9:10:42 AM(UTC)
Lansweeper Modify "....less than 1GB free HD..." Report
by  Cripple.Zero  
Go to last post Go to first unread
Last post: 8/15/2019 7:11:10 PM(UTC)