Notification

Icon
Error

Patch Tuesday report, last 3 months

Posted: Thursday, February 28, 2019 5:50:07 PM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 1,683
5
Like
Hi guys,


I've created a new report since multiple people have asked for a report similar to the Patch Tuesday reports but with more history.

The report has a column per month to indicate if the patch for that month was installed or not. If patches for the latest month have been installed, the asset should be highlighted green.

If you have suggestions or comments, feel free to let me know. This is only a initial version that I've tested on my local test machines.
The report looks in both the currently scanned Windows updates and the history table since I noticed that some Windows updates can be removed when you install a newer version

Code:
Select Distinct Top 1000000 Coalesce(tsysOS.Image,
  tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblState.Statename As State,
Case
When tblassets.assetid =Two.assetid then 'Installed'
Else 'Not Installed'
End As [Patch March],
Case
When  tblAssets.AssetID = Three.assetid Then  'Installed'
Else 'Not Installed'
  End As [Patch April],
Case
When tblassets.assetID =One.assetID then  'Installed'
Else 'Not Installed'
  End As [Patch May],
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,
  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
	When tblAssets.AssetID = Three.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
  Inner Join tblQuickFixEngineeringHist On tblQuickFixEngineeringHist.QFEID =
    tblQuickFixEngineeringUni.QFEID
      Where tblQuickFixEngineeringUni.HotFixID In ('KB4499149','KB4499180','KB4499164','KB4499175','KB4499171','KB4499158','KB4499151','KB4499165','KB4499154','KB4093109','KB4494440','KB4499181','KB4499179','KB4499167','KB4494441','KB4500331')) As One On
    tblAssets.AssetID = One.AssetID
  Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
      From tblQuickFixEngineering
        Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
          = tblQuickFixEngineering.QFEID
  Inner Join tblQuickFixEngineeringHist On tblQuickFixEngineeringHist.QFEID =
    tblQuickFixEngineeringUni.QFEID
      Where tblQuickFixEngineeringUni.HotFixID In ('KB4489876','KB4489880','KB4489885','KB4489878','KB4489884','KB4489891','KB4489883','KB4489881','KB4489872','KB4093109','KB4489882','KB4489871','KB4489886','KB4489868','KB4489899')) As Two On
    tblAssets.AssetID = Two.AssetID
  Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
      From tblQuickFixEngineering
        Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
          = tblQuickFixEngineering.QFEID
  Inner Join tblQuickFixEngineeringHist On tblQuickFixEngineeringHist.QFEID =
    tblQuickFixEngineeringUni.QFEID
      Where tblQuickFixEngineeringUni.HotFixID In ('KB4493472','KB4493448','KB4493458','KB4493471','KB4493451','KB4493450','KB4493446','KB4493467','KB4493475','KB4093109','KB4493470','KB4493474','KB4493441','KB4493464','KB4493509')) As Three On
    tblAssets.AssetID = Three.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
Saulo Benigno
#1Saulo Benigno Member Posts: 7  
posted: 3/1/2019 5:53:35 PM(UTC)
There's a CASE error on Line 19, it's missing the Case command

You need to change this:

#
Else 'Out of date'
End As [Patch February],
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

To this

#
Else 'Out of date'
End As [Patch February],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

:)


Esben.D
#2Esben.D Member Administration Original PosterPosts: 1,683  
posted: 3/13/2019 1:32:27 PM(UTC)
Originally Posted by: Saulo Benigno Go to Quoted Post
There's a CASE error on Line 19, it's missing the Case command

You need to change this:

#
Else 'Out of date'
End As [Patch February],
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

To this

#
Else 'Out of date'
End As [Patch February],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
#

:)




Thanks!

Added the March updates to the report.
jmje
#3jmje Member Posts: 21  
posted: 4/10/2019 2:42:52 PM(UTC)
Looking forward to the April update to this report.
Is there a way to make it run faster? It takes a couple minutes to run in our environment, where every other report is done in seconds.

Thanks!
Tenacious
#4Tenacious Member Posts: 2  
posted: 4/16/2019 12:09:05 AM(UTC)
I'm curious about three things in this report.

1) AFAIK, updates are cumulative (other than delta updates) so, why would something be 'not up to date' if it is missing the January update but has the March Update. This would be pretty likely scenario for a computer deployed in late February. It seems to me as though January and February would still be up-to-date even if only march was installed. Maybe I misunderstand the intent.

2) I haven't dived in too deeply to your SQL but it seems like there are 5 sections with "Where tblQuickFixEngineeringUni.HotFixID" statements and a block of KB ids.
The first and second blocks are mostly February KBs.
The third and fourth blocks are mostly January KBs.
The fifth block is March KBs.
So, question 2 is, why do the January and February blocks repeat but not the March block?

3) Each of the five blocks also contains KB4093109, which appears to be the April update for Windows 10 1511. This seems odd to me. Is it intentional?

Thanks!
joe_user
#5joe_user Member Posts: 11  
posted: 4/17/2019 2:01:52 PM(UTC)
To your point, half of our computers that show "up to date" for April are reported "out of date" for March. I feel that the "date" wording is problematic as it seems that the criteria in the report only verify that a single month's updates are installed. A more apt description would seem to be "March 2019 OS updates installed" or "Some or all March 2019 OS updates NOT installed."

[EDIT to add "OS" because application updates (Sharepoint, Exchange, SQL) are NOT in the criteria, e.g. KB4487563 https://support.microsof...icrosoft-exchange-server]
Esben.D
#6Esben.D Member Administration Original PosterPosts: 1,683  
posted: 4/17/2019 3:34:11 PM(UTC)
Originally Posted by: jmje Go to Quoted Post
Looking forward to the April update to this report.
Is there a way to make it run faster? It takes a couple minutes to run in our environment, where every other report is done in seconds.

Thanks!

The reason why it takes so long is because is because it just has to do a lot since it's 3 already complex reports rolled into 1. The quickest way would be to run it directly in SSMS.


Originally Posted by: Tenacious Go to Quoted Post
I'm curious about three things in this report.

1) AFAIK, updates are cumulative (other than delta updates) so, why would something be 'not up to date' if it is missing the January update but has the March Update. This would be pretty likely scenario for a computer deployed in late February. It seems to me as though January and February would still be up-to-date even if only march was installed. Maybe I misunderstand the intent.

You're probably right and it was just me misunderstanding things. I'll have to remake it then.

Originally Posted by: Tenacious Go to Quoted Post

2) I haven't dived in too deeply to your SQL but it seems like there are 5 sections with "Where tblQuickFixEngineeringUni.HotFixID" statements and a block of KB ids.
The first and second blocks are mostly February KBs.
The third and fourth blocks are mostly January KBs.
The fifth block is March KBs.
So, question 2 is, why do the January and February blocks repeat but not the March block?

Think this is related to the delta update mindset, where you have 3 subqueries, month 1, month 1+2 and month 1+2+3.
Originally Posted by: Tenacious Go to Quoted Post

3) Each of the five blocks also contains KB4093109, which appears to be the April update for Windows 10 1511. This seems odd to me. Is it intentional?

Thanks!

Microsoft has not released new security updates for this specific build for some time. Which is why it has remained the same.

Originally Posted by: joe_user Go to Quoted Post
To your point, half of our computers that show "up to date" for April are reported "out of date" for March. I feel that the "date" wording is problematic as it seems that the criteria in the report only verify that a single month's updates are installed. A more apt description would seem to be "March 2019 updates installed" or "Some or all March 2019 updates NOT installed."


In addition to the comments above about the updates being cumulative I'll add this. The new report will basically just show whether that month's updates have been installed or not.

I'll post again when I've update the report with all these changes.

Esben.D
#7Esben.D Member Administration Original PosterPosts: 1,683  
posted: 4/17/2019 3:56:57 PM(UTC)
Updated the report.

Feel free to continue with the feedback. This was more of a small quick fix.
Tenacious
#8Tenacious Member Posts: 2  
posted: 4/17/2019 10:46:26 PM(UTC)
Originally Posted by: Tenacious Go to Quoted Post
I'm curious about three things in this report...

Originally Posted by: Esben.D Go to Quoted Post
You're probably right and it was just me misunderstanding things. I'll have to remake it then...


Originally Posted by: Tenacious Go to Quoted Post
2) I haven't dived in too deeply to your SQL...

Originally Posted by: Esben.D Go to Quoted Post
Think this is related to the delta update mindset...


Originally Posted by: Tenacious Go to Quoted Post

3) Each of the five blocks also contains KB4093109...

Originally Posted by: Esben.D Go to Quoted Post
Microsoft has not released new security updates for this specific build...


Thanks for the update. This report could shape up to be pretty valuable to me. Thanks!
Esben.D
#9Esben.D Member Administration Original PosterPosts: 1,683  
posted: 5/21/2019 8:25:30 AM(UTC)
Updated the report for May

Active Discussions

Lansweeper Changing/Editing tblComputerSystem.DomainRole
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 2:41:17 PM(UTC)
Lansweeper Version Check Page
by  Esben.D   Go to last post Go to first unread
Last post: Today at 2:37:43 PM(UTC)
Lansweeper warranty information missing
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 2:33:03 PM(UTC)
Lansweeper Dell Switch Servicetag
by  Esben.D   Go to last post Go to first unread
Last post: Today at 2:12:22 PM(UTC)
Lansweeper Office 365 ProPlus not detected in a few PCs
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 2:10:44 PM(UTC)
Lansweeper Imageright Installation
by  CyberCitizen   Go to last post Go to first unread
Last post: Today at 1:01:30 AM(UTC)
Lansweeper Is the conditional step for OS arch broken?
by  CyberCitizen  
Go to last post Go to first unread
Last post: Today at 1:00:54 AM(UTC)