-- Step 1: Store Latest Software Update Groups (SUGs) in a temp table
DROP TABLE IF EXISTS #LatestSUG;
CREATE TABLE #LatestSUG (Title NVARCHAR(255));
INSERT INTO #LatestSUG (Title)
SELECT Title
FROM v_AuthListInfo
WHERE
(YEAR(DateCreated) = YEAR(GETDATE()) AND MONTH(DateCreated) = MONTH(GETDATE()))
OR (YEAR(DateCreated) = YEAR(GETDATE()) AND MONTH(DateCreated) = MONTH(DATEADD(MONTH, -1, GETDATE())))
;
-- Step 2: Store Compliance Data in a temp table to reduce redundant joins
DROP TABLE IF EXISTS #ComplianceData;
CREATE TABLE #ComplianceData (
ResourceID INT,
[Patch Title] NVARCHAR(255),
[Install Status] NVARCHAR(50),
[Update Group] NVARCHAR(255)
);
INSERT INTO #ComplianceData (ResourceID, [Patch Title], [Install Status], [Update Group])
SELECT
ucs.ResourceID,
ui.Title AS [Patch Title],
CASE ucs.status
WHEN 2 THEN 'Required'
WHEN 1 THEN 'NOT REQUIRED'
WHEN 0 THEN 'UNKNOWN'
WHEN 3 THEN 'Installed'
END AS [Install Status],
v_AuthListInfo.Title AS [Update Group]
FROM v_Update_ComplianceStatusAll AS ucs
INNER JOIN v_UpdateInfo AS ui ON ui.CI_ID = ucs.CI_ID
INNER JOIN v_CIRelation ON v_CIRelation.ToCIID = ui.CI_ID
INNER JOIN v_AuthListInfo ON v_CIRelation.FromCIID = v_AuthListInfo.CI_ID
WHERE v_AuthListInfo.Title IN (SELECT Title FROM #LatestSUG );
-- Step 3: Retrieve the final result set using optimized joins
SELECT
sys.Name0 AS [Machine Name],
CASE
WHEN CHARINDEX('OU=', sys.Distinguished_Name0) > 0
THEN SUBSTRING(
sys.Distinguished_Name0,
CHARINDEX('OU=', sys.Distinguished_Name0, CHARINDEX('OU=', sys.Distinguished_Name0) + 3) + 3,
CHARINDEX(',', sys.Distinguished_Name0, CHARINDEX('OU=', sys.Distinguished_Name0, CHARINDEX('OU=', sys.Distinguished_Name0) + 3))
- CHARINDEX('OU=', sys.Distinguished_Name0, CHARINDEX('OU=', sys.Distinguished_Name0) + 3) - 3
)
ELSE NULL
END AS 'Business_Unit',
cd.[Patch Title],
cd.[Install Status],
cd.[Update Group]
FROM v_ClientCollectionMembers
INNER JOIN v_R_System AS sys ON v_ClientCollectionMembers.ResourceID = sys.ResourceID
INNER JOIN #ComplianceData AS cd ON sys.ResourceID = cd.ResourceID
WHERE v_ClientCollectionMembers.CollectionID = 'ABC02BB3'
ORDER BY [Machine Name];
-- Cleanup: Drop temporary tables
DROP TABLE IF EXISTS #LatestSUG;
DROP TABLE IF EXISTS #ComplianceData;
