PI Services

Le blog des collaborateurs de PI Services

SCCM - SQL Query - Inventaire avancé de l'état des mise a jours.

 

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
	

 

Ajouter un commentaire

Loading