none
Software Updates Compliance - SQL Views RRS feed

  • שאלה

  • Hi All, 

    I'm trying to find a ccm database view that show me a single row for each computer .... is the computer is compliant or not compliant.

    when it is compliant it means the computer don't miss any deployed update KB

    when not compliant , then it miss one or more update KB

    any ideas ?

    יום ראשון 19 מאי 2019 18:58

תשובות

  • Hello,

    You could run a Query for it. Something like:

    SELECT
    	sys.Netbios_Name0
    	,CASE
    		WHEN stat.Missing <> ''
    			THEN 'Not compliant'
    		ELSE
    			'Compliant'
    	END 'Compliance'
    FROM
    	v_R_System sys
    LEFT OUTER JOIN
    	(SELECT
    		sys.ResourceID
    		,COUNT(ui.ArticleID) 'Missing'
    	FROM
    		v_R_System sys
    		INNER JOIN
    			v_Update_ComplianceStatus usc
    			ON usc.ResourceID = sys.ResourceID
    		INNER JOIN
    			v_UpdateInfo ui
    			ON ui.CI_ID = usc.CI_ID
    		INNER JOIN
    			v_GS_OPERATING_SYSTEM os
    			ON os.ResourceID = sys.ResourceID
    	WHERE
    		ui.CIType_ID != 9
    		AND usc.Status = 2
    		AND ui.IsSuperseded = 0
    	GROUP BY
    		sys.ResourceID) stat
    	ON stat.ResourceID = sys.ResourceID
    GROUP BY
    	sys.Netbios_Name0
    	,stat.Missing

    Note that the above query will return status for all updates in your enviromnent and return:

    Computer Name (Netbios Name), Compliance (Compliant/Not compliant).

    It will not take into account whether or not the updates have been approved/deployed, if it is a server or a workstation or how many updates are actually missing but you could easily add that.


    David Gustafsson

    • סומן כתשובה על-ידי Immerbonus יום שני 20 מאי 2019 16:27
    יום שני 20 מאי 2019 14:02

כל התגובות

  • this doesn't exist in the database.

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    יום ראשון 19 מאי 2019 23:31
    מנחה דיון
  • Hi,

    As mentioned, there is no such a view to show this for you.
    Instead, I think we can use report Compliance 5-Specific computer under Software Update-A Compliance to see the update status of a computer.

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    יום שני 20 מאי 2019 02:51
  • Hello,

    You could run a Query for it. Something like:

    SELECT
    	sys.Netbios_Name0
    	,CASE
    		WHEN stat.Missing <> ''
    			THEN 'Not compliant'
    		ELSE
    			'Compliant'
    	END 'Compliance'
    FROM
    	v_R_System sys
    LEFT OUTER JOIN
    	(SELECT
    		sys.ResourceID
    		,COUNT(ui.ArticleID) 'Missing'
    	FROM
    		v_R_System sys
    		INNER JOIN
    			v_Update_ComplianceStatus usc
    			ON usc.ResourceID = sys.ResourceID
    		INNER JOIN
    			v_UpdateInfo ui
    			ON ui.CI_ID = usc.CI_ID
    		INNER JOIN
    			v_GS_OPERATING_SYSTEM os
    			ON os.ResourceID = sys.ResourceID
    	WHERE
    		ui.CIType_ID != 9
    		AND usc.Status = 2
    		AND ui.IsSuperseded = 0
    	GROUP BY
    		sys.ResourceID) stat
    	ON stat.ResourceID = sys.ResourceID
    GROUP BY
    	sys.Netbios_Name0
    	,stat.Missing

    Note that the above query will return status for all updates in your enviromnent and return:

    Computer Name (Netbios Name), Compliance (Compliant/Not compliant).

    It will not take into account whether or not the updates have been approved/deployed, if it is a server or a workstation or how many updates are actually missing but you could easily add that.


    David Gustafsson

    • סומן כתשובה על-ידי Immerbonus יום שני 20 מאי 2019 16:27
    יום שני 20 מאי 2019 14:02