La requête SQL ci-dessous propose un inventaire avancé des patchs de mise a jour installés ou requis sur les machines cibles.
Les variables @StartDate et @EndDate sont utilisées pour limiter la date de création du patch.
/* PATCH REQUIRED OR INSTALLED STATUS FOR ALL SERVERS WITH SOFTWARE UPDATE GROUP INFO */
DECLARE @Collection varchar(8)
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @Collection = 'SMS00001' -- Collection ID for All Systems
-- Since 1 months
SET @StartDate = DATEADD(mm, -1,GETDATE())
SET @EndDate = GETDATE()
SELECT DISTINCT VRS.Name0 as 'MachineName',
Os.Caption0 as 'OperatingSystem',
CASE
WHEN summ.ClientActiveStatus = 0 THEN 'Inactive'
WHEN summ.ClientActiveStatus = 1 THEN 'Active'
end as 'ClientActiveStatus',
CASE
WHEN LastHealthEvaluationResult = 1 THEN 'Not Yet Evaluated'
WHEN LastHealthEvaluationResult = 2 THEN 'Not Applicable'
WHEN LastHealthEvaluationResult = 3 THEN 'Evaluation Failed'
WHEN LastHealthEvaluationResult = 4 THEN 'Evaluated Remediated Failed'
WHEN LastHealthEvaluationResult = 5 THEN 'Not Evaluated Dependency Failed'
WHEN LastHealthEvaluationResult = 6 THEN 'Evaluated Remediated Succeeded'
WHEN LastHealthEvaluationResult = 7 THEN 'Evaluation Succeeded'
end as 'Last Health Evaluation Result',
CASE
WHEN LastEvaluationHealthy = 1 THEN 'Pass'
WHEN LastEvaluationHealthy = 2 THEN 'Fail'
WHEN LastEvaluationHealthy = 3 THEN 'Unknown'
end as 'Last Evaluation Healthy',
CASE
WHEN summ.ClientRemediationSuccess = 1 THEN 'Pass'
WHEN summ.ClientRemediationSuccess = 2 THEN 'Fail' ELSE ''
end as 'ClientRemediationSuccess',
UI.Title as 'Title',
UI.ArticleID as 'ArticleID',
UI.BulletinID as 'BulletinID',
UI.DatePosted,
UI.DateCreated,
UI.DateLastModified,
CASE UI.Severity
WHEN 0 THEN 'None'
WHEN 2 THEN 'Low'
WHEN 6 THEN 'Moderate'
WHEN 8 THEN 'Important'
WHEN 10 THEN 'Critical'
end as Patch_Severity,
CASE UI.IsExpired
WHEN 0 THEN 'NOT EXPIRED'
WHEN 1 THEN 'EXPIRED'
END AS [EXPIRY],
CASE UI.IsSuperseded
WHEN 0 THEN 'NOT Superseded'
WHEN 1 THEN 'Superseded'
END AS [SUPERSEDENCE],
CASE
WHEN UCS.Status = 2 THEN 'Required'
WHEN UCS.Status = 3 THEN 'Installed'
ELSE 'Unknown' END AS 'KBStatus',
UI.InfoURL as 'InformationURL',
KB_AND_SUG.Title as SoftwareUpdateGroup,
CASE
WHEN USS.LastScanState = 0 THEN 'unknown'
WHEN USS.LastScanState = 1 THEN 'waiting for catalog location'
WHEN USS.LastScanState = 2 THEN 'running'
WHEN USS.LastScanState = 3 THEN 'completed'
WHEN USS.LastScanState = 4 THEN 'pending retry'
WHEN USS.LastScanState = 5 THEN 'failed'
WHEN USS.LastScanState = 6 THEN 'completed with errors'
END as LastScanState,
USS.ScanTime,
USS.LastScanTime,
USS.LastErrorCode,
USS.LastStatusMessageID
FROM v_R_System VRS
INNER JOIN v_UpdateComplianceStatus UCS ON UCS.ResourceID = VRS.ResourceID
INNER JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
INNER JOIN v_UpdateScanStatus USS on VRS.ResourceID = USS.ResourceID
INNER JOIN v_CICategories_All CIC ON UI.CI_ID = CIC.CI_ID
INNER JOIN v_CH_ClientSummary summ on Vrs.ResourceID = summ.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM Os on UCS.ResourceID = Os.ResourceID
INNER JOIN Computer_System_DATA St on UCS.ResourceID = st.MachineID
INNER JOIN v_FullCollectionMembership Col on UCS.ResourceID = Col.ResourceID
/* JOINTURE POUR OBTENIR L'INFO DU SOFTWARE UPDATE GROUP AUQUEL APPARTIENS LE PATCH */
INNER JOIN (SELECT DISTINCT
upd.ArticleID,
AL.Title
FROM vSMS_CIRelation as cr
INNER JOIN fn_ListUpdateCIs(1033) upd ON upd.CI_ID = cr.ToCIID AND cr.RelationType = 1
INNER JOIN v_CIToContent CC ON cc.CI_ID=upd.CI_ID
INNER JOIN v_AuthListInfo AL ON al.CI_ID =cr.FromCIID
) KB_AND_SUG on UI.ArticleID = KB_AND_SUG.ArticleID
--WHERE VRS.Operating_System_Name_and0 like '%Server%'
AND Col.CollectionID = @Collection
AND DateCreated BETWEEN @StartDate AND @EndDate
--AND CategoryInstanceName = 'Security Updates' -- Only Security Updates
ORDER BY DateCreated DESC