SCCM – SQL Query – Inventaire avancé de l’état des mise a jours.

par Christophe Jourdan | Août 30, 2021 | SCCM, Script, sql | 0 commentaires

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
    