Report for Microsoft Win 7 Extended Support

[AndyPalmerCTC]

Hi LS experts,

I have a customer who would like to pay for Extended support for their Windows 7 machines.
Microsoft have released this information as of end of November 2019 and it requires a set of prerequisite KB patches to be installed first.
(More at https://techcommunity.microsoft.com/t5/Windows-IT-Pro-Blog/How-to-get-Extended-Security-Updates-for-eligible-Windows/ba-p/917807)

So I thought i'd use the LS install that they have to scan for Win 7 machines and then detect the KB numbers.
To do this I have 'borrowed' the code published for the Monthly Patch Tuesday and adjusted for my use.

The problem is that when I tested it against a machine with all the patches installed, it doesn't show as complete.

Win 7 needs:
- Service Pack 1 (KB976932) but haven't looked for that specifically.
- The March servicing stack update (SSU) - KB4490626
- The SHA2 code sign patch - KB4474419
- The September SSU - KB4516655
- Any Monthly Roll Up from October onwards - KB4519976, KB4519972, KB4525235, KB4525251, KB4530734

In the code below I have use the IN statement to list the Monthly Roll Ups, then added an AND for each of the remaining three KB numbers.
I can see it works if I just check for each KB number one at a time (as the one machine with them all will show as green each time). Likewise adding them as OR statements seems to work and shows those machines that have at least one of the patches installed... But I need all of the three plus one of the Roll Ups.

I'm guessing this is a fault because when using an OR statement you just get a single result, with AND you will get multiple possible matches - unfortunately my SQL isn't up to knowing how to fix that!

Please can you take a look at the below and advise.
Kudos to those that can fix it.
I suspect others will benefit from this code too - now that Windows 7 is about to go EOL!

My code:

Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
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.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lasttried,
Case
When tblAssets.AssetID = SubQuery1.AssetID Then ''
Else 'Still needs ESU Patches'
End As [Install ESU Patches],
Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
Case tblAssets.AssetID
When SubQuery1.AssetID Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.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 ('KB4519976', 'KB4519972',
'KB4525235', 'KB4525251', 'KB4530734') And
tblQuickFixEngineeringUni.HotFixID = 'KB4490628' And
tblQuickFixEngineeringUni.HotFixID = 'KB4474419' And
tblQuickFixEngineeringUni.HotFixID = 'KB4516655') As SubQuery1
On tblAssets.AssetID = SubQuery1.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Distinct Top 1000000 TsysLastscan.AssetID As ID,
TsysLastscan.Lasttime As QuickFixLastScanned
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
tblAssets.AssetID = QuickFixLastScanned.ID
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Where tsysOS.OSname Like 'Win 7%' And tblAssetCustom.State = 1 And
tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.AssetName


Many Thanks
Andy

[AndyPalmerCTC]

Hi All,

The testing I have done so far seems to show this is working well...

Can I just say thanks to you all for taking the beginnings of this code and completing it for everyone!
I couldn't have finished this myself and I hope that it is indeed useful to others.

Again, thank you!
Andy

[Esben_D]

Thanks. I also updated it so it proved more information when a specific patch is missing. However, because it is all in the same case, if you are missing multiple it will only show the first missing one it encounters I think.

[RCorbeil]

AND takes precedence over OR, so you'll want to add some parentheses to that. Without them, it'll be interpreted as

WHERE
  tsysOS.OSname LIKE 'Win 7%'
  OR tsysOS.OSname LIKE 'Win 2008 R2'
  Or (tsysOS.OSname LIKE 'Win 2008'
      AND tblAssetCustom.State = 1
      AND tsysAssetTypes.AssetTypename LIKE 'Windows%')

What you want:

WHERE
  (tsysOS.OSname LIKE 'Win 7%' OR tsysOS.OSname LIKE 'Win 2008 R2' Or tsysOS.OSname LIKE 'Win 2008')
  AND tblAssetCustom.State = 1
  AND tsysAssetTypes.AssetTypename LIKE 'Windows%'

[Esben_D · Accepted solution]

Thanks!

I'll probably use this report in a blog post soon as this seems very useful. I'll be sure to credit you guys.

The only thing that might need to be added is a check at the bottom to check for Service Pack 1 since it is part of the requirements. Then again maybe this is a requirement to install the other updates anyway?

I added Server 2008 and Server 2008R2 to the report. I also changed the case to provide more accurate information when a patch is missing:

	
SELECT DISTINCT Top 1000000
  COALESCE(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) AS icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  CASE
    WHEN (sha2patch.AssetID IS NOT NULL) AND (SSUMarApr.AssetID IS NOT NULL) AND (SSUSep.AssetID IS NOT NULL) AND (MonthlyRollups.AssetID IS NOT NULL)
    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.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname AS OS,
  tblAssets.SP,
  tblAssets.Lasttried,
  CASE
    WHEN sha2patch.AssetID IS NULL then 'SHA-2 code signing support update missing' 
	WHEN SSUMarApr.AssetID IS NULL then 'Servicing stack update from March(W7/W2008R2) or April(W2008) missing'
	WHEN SSUSep.AssetID IS NULL then 'Servicing stack update from September missing' 
	WHEN MonthlyRollups.AssetID IS NULL then 'Monthly rollup patch missing'
    ELSE ''
  END AS [Missing Patches],
  CONVERT(nvarchar,DATEDIFF(DAY, QuickFixLastScanned.QuickFixLastScanned, GETDATE())) + ' days ago' AS WindowsUpdateInfoLastScanned,
  CASE
    WHEN (sha2patch.AssetID IS NOT NULL) AND (SSUMarApr.AssetID IS NOT NULL) AND (SSUSep.AssetID IS NOT NULL) AND (MonthlyRollups.AssetID IS NOT NULL)
    THEN '#d4f4be'
    ELSE '#ffadad'
  END AS backgroundcolor
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblOperatingsystem ON tblOperatingsystem.AssetID = tblAssets.AssetID
  INNER JOIN tblState ON tblState.State = tblAssetCustom.State
  INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
  LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
    
  LEFT JOIN (Select
               tblQuickFixEngineering.AssetID
             From
               tblQuickFixEngineering
               Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
             Where
               tblQuickFixEngineeringUni.HotFixID = 'KB4474419') AS sha2patch ON sha2patch.AssetID = tblAssets.AssetID
  LEFT JOIN (Select
               tblQuickFixEngineering.AssetID
             From
               tblQuickFixEngineering
               Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
             Where
               tblQuickFixEngineeringUni.HotFixID in ('KB4493730','KB4490628')) AS SSUMarApr ON SSUMarApr.AssetID = tblAssets.AssetID
  LEFT JOIN (Select
               tblQuickFixEngineering.AssetID
             From
               tblQuickFixEngineering
               Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
             Where
               tblQuickFixEngineeringUni.HotFixID in ('KB4516655','KB4517134')) AS SSUSep ON SSUSep.AssetID = tblAssets.AssetID
  LEFT JOIN (Select
               tblQuickFixEngineering.AssetID
             From
               tblQuickFixEngineering
               Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
             Where
               tblQuickFixEngineeringUni.HotFixID IN ('KB4519976', 'KB4519972', 'KB4525235', 'KB4525251', 'KB4530734','KB4520002', 'KB4520015', 'KB4525234', 'KB4525244', 'KB4530695')) AS MonthlyRollups ON MonthlyRollups.AssetID = tblAssets.AssetID
  LEFT JOIN tsysIPLocations ON tblAssets.IPNumeric >= tsysIPLocations.StartIP AND tblAssets.IPNumeric <= tsysIPLocations.EndIP
  LEFT JOIN (SELECT DISTINCT
               TsysLastscan.AssetID AS ID,
               TsysLastscan.Lasttime AS QuickFixLastScanned
             FROM
               TsysWaittime
               INNER JOIN TsysLastscan ON TsysWaittime.CFGCode = TsysLastscan.CFGcode
             WHERE
               TsysWaittime.CFGname = 'QUICKFIX'
            ) AS QuickFixLastScanned ON tblAssets.AssetID = QuickFixLastScanned.ID
  LEFT JOIN (SELECT DISTINCT
               tblErrors.AssetID AS ID,
               MAX(tblErrors.Teller) AS ErrorID
             FROM
               tblErrors
             GROUP BY
               tblErrors.AssetID
            ) AS ScanningError ON  tblAssets.AssetID = ScanningError.ID
  LEFT JOIN tblErrors ON ScanningError.ErrorID = tblErrors.Teller
  LEFT JOIN tsysasseterrortypes ON tsysasseterrortypes.Errortype = tblErrors.ErrorType
WHERE
  (tsysOS.OSname LIKE 'Win 7%' OR tsysOS.OSname LIKE 'Win 2008 R2' Or tsysOS.OSname LIKE 'Win 2008')
  AND tblAssetCustom.State = 1
  AND tsysAssetTypes.AssetTypename LIKE 'Windows%'
ORDER BY
  tblAssets.AssetName

[RCorbeil]

Ugh. You're right. I know what I was shooting for, but the result produced wasn't it. Sorry about that.

Try this. Instead of trying to do all the tests in one go, I've broken them out, replacing the one JOIN with four. Doing it this way, you can also add more specific messages to the output if you like, e.g. "Needs March SSU", "Needs SHA2 patch".

SELECT DISTINCT Top 1000000
  COALESCE(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) AS icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  CASE
    WHEN (sha2patch.AssetID IS NOT NULL) AND (SSUMar.AssetID IS NOT NULL) AND (SSUSep.AssetID IS NOT NULL) AND (MonthlyRollups.AssetID IS NOT NULL)
    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.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname AS OS,
  tblAssets.SP,
  tblAssets.Lasttried,
  CASE
    WHEN (sha2patch.AssetID IS NOT NULL) AND (SSUMar.AssetID IS NOT NULL) AND (SSUSep.AssetID IS NOT NULL) AND (MonthlyRollups.AssetID IS NOT NULL)
    THEN ''
    ELSE 'Still needs ESU Patches'
  END AS [Install ESU Patches],
  CONVERT(nvarchar,DATEDIFF(DAY, QuickFixLastScanned.QuickFixLastScanned, GETDATE())) + ' days ago' AS WindowsUpdateInfoLastScanned,
  CASE
    WHEN (sha2patch.AssetID IS NOT NULL) AND (SSUMar.AssetID IS NOT NULL) AND (SSUSep.AssetID IS NOT NULL) AND (MonthlyRollups.AssetID IS NOT NULL)
    THEN '#d4f4be'
    ELSE '#ffadad'
  END AS backgroundcolor
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblOperatingsystem ON tblOperatingsystem.AssetID = tblAssets.AssetID
  INNER JOIN tblState ON tblState.State = tblAssetCustom.State
  INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
  LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
  
  LEFT JOIN (Select
               tblQuickFixEngineering.AssetID
             From
               tblQuickFixEngineering
               Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
             Where
               tblQuickFixEngineeringUni.HotFixID = 'KB4474419') AS sha2patch ON sha2patch.AssetID = tblAssets.AssetID
  LEFT JOIN (Select
               tblQuickFixEngineering.AssetID
             From
               tblQuickFixEngineering
               Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
             Where
               tblQuickFixEngineeringUni.HotFixID = 'KB4490628') AS SSUMar ON SSUMar.AssetID = tblAssets.AssetID
  LEFT JOIN (Select
               tblQuickFixEngineering.AssetID
             From
               tblQuickFixEngineering
               Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
             Where
               tblQuickFixEngineeringUni.HotFixID = 'KB4516655') AS SSUSep ON SSUSep.AssetID = tblAssets.AssetID
  LEFT JOIN (Select
               tblQuickFixEngineering.AssetID
             From
               tblQuickFixEngineering
               Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
             Where
               tblQuickFixEngineeringUni.HotFixID IN ('KB4519976', 'KB4519972', 'KB4525235', 'KB4525251', 'KB4530734')) AS MonthlyRollups ON MonthlyRollups.AssetID = tblAssets.AssetID
  LEFT JOIN tsysIPLocations ON tblAssets.IPNumeric >= tsysIPLocations.StartIP AND tblAssets.IPNumeric <= tsysIPLocations.EndIP
  LEFT JOIN (SELECT DISTINCT
               TsysLastscan.AssetID AS ID,
               TsysLastscan.Lasttime AS QuickFixLastScanned
             FROM
               TsysWaittime
               INNER JOIN TsysLastscan ON TsysWaittime.CFGCode = TsysLastscan.CFGcode
             WHERE
               TsysWaittime.CFGname = 'QUICKFIX'
            ) AS QuickFixLastScanned ON tblAssets.AssetID = QuickFixLastScanned.ID
  LEFT JOIN (SELECT DISTINCT
               tblErrors.AssetID AS ID,
               MAX(tblErrors.Teller) AS ErrorID
             FROM
               tblErrors
             GROUP BY
               tblErrors.AssetID
            ) AS ScanningError ON  tblAssets.AssetID = ScanningError.ID
  LEFT JOIN tblErrors ON ScanningError.ErrorID = tblErrors.Teller
  LEFT JOIN tsysasseterrortypes ON tsysasseterrortypes.Errortype = tblErrors.ErrorType
WHERE
  tsysOS.OSname LIKE 'Win 7%'
  AND tblAssetCustom.State = 1
  AND tsysAssetTypes.AssetTypename LIKE 'Windows%'
ORDER BY
  tblAssets.AssetName

[AndyPalmerCTC]

Hi Esben.D,

Comment much appreciated.
I'll try and understand and give it a go too.

In old programming I'd used a loop to check each HotfixID as well as the Rollup set of ID's... never quite got SQL syntax!

Any help you can provide is appreciated, I suspect there will be others who will benefit from this code now that Windows 7 is EOL in January.

Thanks

[Esben_D]

I think because its all in one column and AND will not work along with the joining method.

I tried multiple things and I never got it to work once you go over 1 hotfix ID. The best method is probably just to create a subquery (or case if you want) per HotfixID.

I might take a look at doing this next week if I have the time

[AndyPalmerCTC]

Many thanks for your response RC62N,

However, I've tried this and it didn't result in the one asset with all the matches going green.
I changed the line

INNER JOIN tblQuickFixEngineeringUni AS MonthlyRollup ON MonthlyRollup.QFEID = tblQuickFixEngineering.QFEID AND MonthlyRollup.HotFixID IN ('KB4519976', 'KB4519972', 'KB4525235', 'KB4525251', 'KB4530734')

so it became

INNER JOIN tblQuickFixEngineeringUni AS MonthlyRollup ON MonthlyRollup.QFEID = tblQuickFixEngineering.QFEID AND MonthlyRollup.HotFixID = 'KB4530734'

As this asset has the latest Rollup installed I thought explicitly ensuring all the matches were there might do it (ruling out the IN statement)...
Unfortunately it still didn't light up as 'Up to Date'.

Oh an apology - I miss-typed the KB for the March SSU in my description (code is correct), it should read KB4490628 (not ending in 6).

Many thanks,

[RCorbeil]

At a glance, the offending bit of code appears to be

SELECT TOP 1000000
  tblQuickFixEngineering.AssetID
FROM
  tblQuickFixEngineering
  INNER JOIN tblQuickFixEngineeringUni ON tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
WHERE
  tblQuickFixEngineeringUni.HotFixID IN ('KB4519976',
                                         'KB4519972',
                                         'KB4525235',
                                         'KB4525251',
                                         'KB4530734')
  AND tblQuickFixEngineeringUni.HotFixID = 'KB4490628'
  AND tblQuickFixEngineeringUni.HotFixID = 'KB4474419'
  AND tblQuickFixEngineeringUni.HotFixID = 'KB4516655'

Try reading it like this:

Show me results when:
   x is any one of (1, 2, 3, 4, 5)
   AND x = 6
   AND x = 7
   AND x = 8

Just as x will never simultaneously be 3, 6, 7 and 8, the currently-being-tested HotFixID will never simultaneously be one of the monthly rollups AND the SHA2 update AND the March SSU AND the September SSU.

Try substituting

SELECT TOP 1000000
  tblQuickFixEngineering.AssetID
FROM
  tblQuickFixEngineering
  INNER JOIN tblQuickFixEngineeringUni AS sha2patch ON sha2patch.QFEID = tblQuickFixEngineering.QFEID AND sha2patch.HotFixID = 'KB4474419'
  INNER JOIN tblQuickFixEngineeringUni AS SSUMar    ON SSUMar.QFEID = tblQuickFixEngineering.QFEID AND SSUMar.HotFixID = 'KB4490626'
  INNER JOIN tblQuickFixEngineeringUni AS SSUSep    ON SSUSep.QFEID = tblQuickFixEngineering.QFEID AND SSUSep.HotFixID = 'KB4516655'
  INNER JOIN tblQuickFixEngineeringUni AS MonthlyRollup ON MonthlyRollup.QFEID = tblQuickFixEngineering.QFEID AND MonthlyRollup.HotFixID IN ('KB4519976', 'KB4519972', 'KB4525235', 'KB4525251', 'KB4530734')

(The KB checks could have been put in a WHERE clause, but I like keeping by conditions close to my table links for legibility.)

Each of the INNER JOINs tests one of the conditions. By using INNER JOINs, if any one of them doesn't match, nothing is returned, effectively ANDing all the conditions.