→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
AndyPalmerCTC
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
Esben_D
Lansweeper Employee
Lansweeper Employee
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

View solution in original post

9 REPLIES 9
AndyPalmerCTC
Engaged Sweeper II
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
Lansweeper Employee
Lansweeper Employee
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
Honored Sweeper II
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
Lansweeper Employee
Lansweeper Employee
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
Honored Sweeper II
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
Engaged Sweeper II
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
Lansweeper Employee
Lansweeper Employee
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
Engaged Sweeper II
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
Honored Sweeper II
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.