none
Software Updates Compliance - SQL Views RRS feed

  • Domanda

  • 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 ?

    domenica 19 maggio 2019 18:58

Risposte

  • 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

    • Contrassegnato come risposta Immerbonus lunedì 20 maggio 2019 16:27
    lunedì 20 maggio 2019 14:02

Tutte le risposte

  • 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

    domenica 19 maggio 2019 23:31
    Moderatore
  • 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.

    lunedì 20 maggio 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

    • Contrassegnato come risposta Immerbonus lunedì 20 maggio 2019 16:27
    lunedì 20 maggio 2019 14:02