Patch Status Chart Problem

[aguilarr7]

Hi Lansweeper Team.

I hope you can help me, I need to create a dashboard of the patching audit report with a graph, however, I have problems generating the code since I am guided by this link: https://www.lansweeper.com/es/blog/pro-tips/patch-tuesday-improved/ but it generates an error that I am attaching.

Has anyone done something similar?

[Josha]

You need to read the page you linked and fully understand what they're telling you to do.  I'll try to explain some but the page gives you every single step with very clear wording.

Each month they release a new patch Tuesday SQL report.  You can find them here - https://www.lansweeper.com/resources/report/patch-tuesday/

You can just copy/paste that report and you'll have a detailed spreadsheet of your assets and their patch Tuesday compliance.  However, if you want a chart, you'll have to follow the directions in the link you posted which tells you what to delete and what to add.  

Using October's report and the instructions on the page you linked, the correct code would be as shown below.

Select
SubQuery4.[Patch status],
Count (distinct tblAssets.AssetID) As Total
From tblAssets
left join ( select
tblassets.assetid,
SubQuery2.Buildnumber,
CASE
    WHEN tsysOS.OSname = 'Win 2008' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OSname IN ('Win 7', 'Win 7 RC', 'Win 2008 R2') THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OSname = 'Win 8' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OSname = 'Win 2012' and SubQuery3.AssetID = tblassets.AssetID then 'Up to date'
    WHEN tsysOS.OSname = 'Win 8.1' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OSname = 'Win 2012 R2' and SubQuery3.AssetID = tblassets.AssetID then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.10240%' And SubQuery2.Buildnumber >= 20796 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.10586%' THEN 'EOL, update to a higher Windows version'
    WHEN (tsysOS.OScode LIKE '10.0.14393%' OR tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 7428 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.15063%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.16299%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.17134%' THEN 'EOL, update to a higher Windows version'
	WHEN tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 6414 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.17763' AND tblOperatingsystem.Caption NOT LIKE '%LTSC%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.17763' AND tblOperatingsystem.Caption LIKE '%LTSC%' And SubQuery2.Buildnumber >= 6414 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.18362%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.18363%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.19041%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.19042%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.19043%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.19044%' And SubQuery2.Buildnumber >= 5011 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.19045%' And SubQuery2.Buildnumber >= 5011 Then 'Up to date'
WHEN tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 2762 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.22000%' And SubQuery2.Buildnumber >= 3260 Then 'Up to date'
    WHEN (tsysOS.OScode LIKE '10.0.22621%' OR tsysOS.OScode LIKE '10.0.22631%') And SubQuery2.Buildnumber >= 4317 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.25398%' And SubQuery2.Buildnumber >= 1189 Then 'Up to date'
	WHEN tsysOS.OScode LIKE '10.0.26100%' And SubQuery2.Buildnumber >= 2033 Then 'Up to date'
Else 'Out of date'
End As [Patch status],
CASE
    WHEN tsysOS.OSname = 'Win 2008' THEN 'EOL'
    WHEN tsysOS.OSname IN ('Win 7', 'Win 7 RC', 'Win 2008 R2') THEN 'EOL'
    WHEN tsysOS.OSname = 'Win 8' THEN 'EOL'
    WHEN tsysOS.OSname = 'Win 2012' AND SubQuery3.AssetID <> tblassets.AssetID THEN 'KB5044342'
    WHEN tsysOS.OSname = 'Win 8.1' THEN 'EOL'
    WHEN tsysOS.OSname = 'Win 2012 R2' AND SubQuery3.AssetID <> tblassets.AssetID THEN 'KB5044343'
    WHEN tsysOS.OScode LIKE '10.0.10240%' AND SubQuery2.Buildnumber < 20796 THEN 'KB5044286'
    WHEN tsysOS.OScode LIKE '10.0.10586%' THEN 'EOL'
    WHEN (tsysOS.OScode LIKE '10.0.14393%' OR tsysOS.OSname = 'Win 2016') AND SubQuery2.Buildnumber < 7428 THEN 'KB5044293'
    WHEN tsysOS.OScode LIKE '10.0.15063%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.16299%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.17134%' THEN 'EOL'
    WHEN tsysOS.OSname = 'Win 2019' AND SubQuery2.Buildnumber < 6414 THEN 'KB5044277'
    WHEN tsysOS.OScode LIKE '10.0.17763' AND tblOperatingsystem.Caption NOT LIKE '%LTSC%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.17763' AND tblOperatingsystem.Caption LIKE '%LTSC%' AND SubQuery2.Buildnumber < 6414 THEN 'KB5044277'
    WHEN tsysOS.OScode LIKE '10.0.18362%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.18363%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.19041%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.19042%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.19043%' THEN 'EOL'
    WHEN tsysOS.OScode LIKE '10.0.19044%' AND SubQuery2.Buildnumber < 5011 THEN 'KB5044273'
    WHEN tsysOS.OScode LIKE '10.0.19045%' AND SubQuery2.Buildnumber < 5011 THEN 'KB5044273'
    WHEN tsysOS.OSname = 'Win 2022' AND SubQuery2.Buildnumber < 2762 THEN 'KB5044281'
    WHEN tsysOS.OScode LIKE '10.0.22000%' AND SubQuery2.Buildnumber < 3260 THEN 'KB5044280'
    WHEN (tsysOS.OScode LIKE '10.0.22621%' OR tsysOS.OScode LIKE '10.0.22631%') AND SubQuery2.Buildnumber < 4317 THEN 'KB5044285'
    WHEN tsysOS.OScode LIKE '10.0.25398%' AND SubQuery2.Buildnumber < 1189 THEN 'KB5044288'
    WHEN tsysOS.OScode LIKE '10.0.26100%' AND SubQuery2.Buildnumber < 2033 THEN 'KB5044284'
    ELSE ''
END AS [Install one of these updates]
from tblassets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
left Join (select tblassets.AssetID, 
CASE
WHEN ISNUMERIC(tblAssets.BuildNumber) = 1 THEN CONVERT(bigint, tblAssets.BuildNumber)
ELSE NULL
END as Buildnumber 
from tblAssets where Assettype = -1) As SubQuery2 On tblAssets.AssetID =
SubQuery2.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB5044342','KB5044343')) As SubQuery3 On
tblAssets.AssetID = SubQuery3.AssetID
) As SubQuery4 On tblAssets.AssetID =
SubQuery4.AssetID

Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
left JOIN (
SELECT 
    AssetId,
    EventTime
FROM (
    SELECT 
        AssetId,
        EventTime,
        ROW_NUMBER() OVER (PARTITION BY AssetId ORDER BY EventTime DESC) AS rn
    FROM 
        tblUptime
    WHERE 
        EventType = 1
        AND ISDATE(EventTime) = 1
) AS rankedEvents
WHERE 
    rn = 1) AS LatestEvent ON tblAssets.AssetID = LatestEvent.AssetId
    Left JOIN tblQuickFixEngineering ON tblAssets.AssetID = tblQuickFixEngineering.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID,
CASE
WHEN ISNUMERIC(Max(Right(tblQuickFixEngineeringUni.HotFixID, 7))) = 1 THEN Max(Cast(Right(tblQuickFixEngineeringUni.HotFixID, 7) As bigint))
ELSE NULL
END as PatchIDMax,
MAX(CAST(installedon AS date)) AS InstalledOn
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where Right(tblQuickFixEngineeringUni.HotFixID, 7) Not Like '%[^0-9]%' and ISDATE(installedon) = 1
Group By tblQuickFixEngineering.AssetID) As SubQuery1 On tblAssets.AssetID =
SubQuery1.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 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 <> 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
tsysOS.OSname Not Like '%2003%' And tblAssetCustom.State = 1 And
tsysAssetTypes.AssetTypename Like 'Windows%'
Group By SubQuery4.[Patch status]
Order By Total Desc

[KevinA-REJIS]

You'll only remove the code between "Select" and "From tblAssets", and also replace the bit at the bottom, but it needs the rest of the code from the Patch Tuesday report that's between "From tblAssets" to just before "Order By".