Handy SCCM (Updates) Queries

Статья
04/09/2015

Context

Recently , while still basically focusing on platforms related technologies, I’ve been doing more and more work focusing on datacenter/cloud automation. This obviously involves a lot more focus on integrating to accommodate business processes. In short this is a collection of SCCM queries which I’m going to be maintaining as much for my own reference as anything else.

Note: This will be updated as I come across new queries and or optimize the queries.

My thanks to all the many folks out there who had similar samples for me to build off of.

References

Systems needing updates which are included in baselines

Get a list of systems that need updates in the Software Update Groups, regardless of whether or not the update is actually deployed to the system.

Excluding unknown status

SELECT s.Name0, sn.StateName
    , CASE
        WHEN uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0 THEN 402
        ELSE 500
    END As StateType
    , CASE
        WHEN uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0 THEN uss.LastEnforcementMessageID
        ELSE uss.[Status]
    END As StateID
    , MAX(CASE
        WHEN uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0 THEN uss.LastEnforcementMessageTime
        ELSE uss.LastStatusCheckTime
    END) As StateTime
FROM v_R_System s
INNER JOIN v_UpdateComplianceStatus uss ON s.ResourceID = uss.ResourceID
INNER JOIN v_CIRelation cr ON uss.CI_ID = cr.ToCIID
INNER JOIN v_AuthListInfo ali ON cr.FromCIID = ali.CI_ID
INNER JOIN v_StateNames sn ON
CASE
        WHEN uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0 THEN 402
        ELSE 500
    END = sn.TopicType
    AND
    CASE
        WHEN uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0 THEN uss.LastEnforcementMessageID
        ELSE uss.[Status]
    END = sn.StateID
WHERE NOT ( NOT (uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0) AND (uss.[Status] = 1 OR uss.[Status] = 3))
GROUP BY s.Name0, sn.StateName
    , CASE
        WHEN uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0 THEN 402
        ELSE 500
    END
    , CASE
        WHEN uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0 THEN uss.LastEnforcementMessageID
        ELSE uss.[Status]
    END

Including unknown status

SELECT s.Name0, sn.StateName, MAX(uss.StateTime) As StateTime
FROM v_R_System s
INNER JOIN v_UpdateState_Combined uss ON s.ResourceID = uss.ResourceID
INNER JOIN v_CIRelation cr ON uss.CI_ID = cr.ToCIID
INNER JOIN v_AuthListInfo ali ON cr.FromCIID = ali.CI_ID
INNER JOIN v_StateNames sn ON uss.StateType = sn.TopicType AND uss.StateID = sn.StateID
WHERE NOT (uss.StateType = 500 AND (uss.StateID = 1 or uss.StateiD=3))
GROUP BY s.Name0, sn.StateName

Note: The WHERE clause excludes ”Update Not Required” (StateID = 1) and “Update Installed” (StateID = 3)

Information About Updates Needed Per Computer (For which there are baselines)

This gives pretty much all the information needed about the patch state for all needed, unknown, error, and in process statuses. This will also tell if the computer is in a collection that the update is deployed to.

Note: At the database level, Software Update Group deployments get deployed on a per update basis (Assignments). This means that by joining of v_CIAssignmentToCI and v_AuthListInfo to the CI_ID of the update will duplicate the rows.

All Needed Updates Excluding Unknown Status: Simplified Version

SELECT s.Name0, ali.Title As [SoftwareUpdateGroup], ui.ArticleID, ui.BulletinID, ui.Title As UpdateTitle, ui.InfoURL, sn.StateName
    , MAX(CASE
        WHEN uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0 THEN uss.LastEnforcementMessageTime
        ELSE uss.LastStatusCheckTime
    END) As StateTime, CAST(MAX(CASE WHEN tm.ResourceID IS NOT NULL THEN 1 ELSE 0 END) AS BIT) AS IsDeployedToThisComputer
FROM v_R_System s
INNER JOIN v_UpdateComplianceStatus uss ON s.ResourceID = uss.ResourceID
INNER JOIN v_CIRelation cr ON uss.CI_ID = cr.ToCIID
INNER JOIN v_AuthListInfo ali ON cr.FromCIID = ali.CI_ID
INNER JOIN v_StateNames sn ON
    CASE
        WHEN uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0 THEN 402
        ELSE 500
    END = sn.TopicType
    AND
    CASE
        WHEN uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0 THEN uss.LastEnforcementMessageID
        ELSE uss.[Status]
    END = sn.StateID
INNER JOIN v_CIAssignmentToCI atci ON uss.CI_ID = atci.CI_ID
INNER JOIN v_CIAssignment a ON atci.AssignmentID = a.AssignmentID
LEFT JOIN v_CITargetedMachines tm ON uss.CI_ID = tm.CI_ID AND s.ResourceID = tm.ResourceID
INNER JOIN v_UpdateInfo ui ON uss.CI_ID = ui.CI_ID
WHERE NOT ( NOT (uss.[Status] = 2 AND uss.LastEnforcementMessageID > 0) AND (uss.[Status] = 1 OR uss.[Status] = 3))
GROUP BY s.Name0, ali.Title, ui.BulletinID, ui.ArticleID, ui.Title, sn.StateName, ui.InfoURL

All Needed Updates Excluding Unknown Status: Performance Optimized Version (about 8x faster)

SELECT uss.Name0, aliloc.[DisplayName] As SoftwareUpdateGroup, uss.BulletinID, uss.ArticleID, loc.[DisplayName] AS [UpdateTitle], loc.CIInformativeURL As InfoURL, sn.StateName, uss.LastErrorCode
    , MAX(uss.StateTime) As StateTime, CAST(MAX(CASE WHEN cm.MachineID IS NOT NULL THEN 1 ELSE 0 END) AS BIT) AS IsDeployedToThisComputer
FROM
(
    SELECT uci.CI_ID, s.ResourceID, uci.ArticleID, uci.BulletinID, s.Name0
        , CASE
            WHEN cs.[Status] = 2 AND LastEnforcementMessageID > 0 THEN 402
            WHEN ISNULL(cs.Status, case when ss.ScanPackageVersion>=uci.MinSourceVersion then 1 else 0 end) != 2 OR LastEnforcementMessageID IS NULL or LastEnforcementMessageID = 0 THEN 500
        END As StateType
        , CASE
            WHEN cs.[Status] = 2 AND LastEnforcementMessageID > 0 THEN LastEnforcementMessageID
            WHEN ISNULL(cs.Status, case when ss.ScanPackageVersion>=uci.MinSourceVersion then 1 else 0 end) != 2 OR LastEnforcementMessageID IS NULL or LastEnforcementMessageID = 0 THEN ISNULL(cs.Status, case when ss.ScanPackageVersion>=uci.MinSourceVersion then 1 else 0 end)
        END As StateId
        , CASE
            WHEN cs.[Status] = 2 AND LastEnforcementMessageID > 0 THEN LastEnforcementMessageTime
            WHEN ISNULL(cs.Status, case when ss.ScanPackageVersion>=uci.MinSourceVersion then 1 else 0 end) != 2 OR LastEnforcementMessageID IS NULL or LastEnforcementMessageID = 0 THEN ISNULL(cs.LastStatusCheckTime, ss.ScanTime)
        END As StateTime
        , cs.LastErrorCode
    FROM CI_UpdateCIs uci
    INNER JOIN CI_ConfigurationItems (NOLOCK) AS ci ON uci.CI_ID = ci.CI_ID AND ci.CIType_ID IN (1, 8) AND ci.IsHidden = 0 AND ci.IsTombstoned = 0
    INNER JOIN Update_ComplianceStatus cs ON uci.CI_ID = cs.CI_ID AND cs.[Status] > 0
    LEFT JOIN v_R_System s ON cs.MachineID = s.ResourceID
    INNER JOIN Update_ScanStatus ss ON uci.UpdateSource_ID = ss.UpdateSource_ID AND s.ResourceID = ss.MachineID
) As uss
INNER JOIN v_CIRelation cr ON uss.CI_ID = cr.ToCIID
INNER JOIN CI_ConfigurationItems ali ON cr.FromCIID = ali.CI_ID AND ali.IsHidden=0 AND ali.CIType_ID=9
INNER JOIN v_LocalizedCIProperties_SiteLoc aliloc on ali.CI_ID = aliloc.CI_ID
INNER JOIN v_StateNames sn ON uss.StateType = sn.TopicType AND uss.StateID = sn.StateID
INNER JOIN CI_AssignmentTargetedCIs (NOLOCK) atci ON uss.CI_ID = atci.CI_ID
INNER JOIN CI_CIAssignments (NOLOCK) a ON atci.AssignmentID = a.AssignmentID
INNER JOIN Collections_G (NOLOCK) c ON a.TargetCollectionID = c.CollectionID
LEFT JOIN CollectionMembers (NOLOCK) cm ON c.SiteID = cm.SiteID AND cm.MachineID = uss.ResourceID
INNER JOIN v_LocalizedCIProperties_SiteLoc loc ON uss.CI_ID = loc.CI_ID
WHERE NOT (uss.StateType = 500 AND (uss.StateId = 1 OR uss.StateId = 3))
GROUP BY uss.Name0, aliloc.[DisplayName], uss.BulletinID, uss.ArticleID, loc.[DisplayName], loc.CIInformativeURL, sn.StateName, uss.LastErrorCode

All Updates Including Unknown Status: Simplified Version

SELECT s.Name0, ali.Title As [SoftwareUpdateGroup], ui.ArticleID, ui.BulletinID, ui.Title As UpdateTitle, ui.InfoURL, sn.StateName
, MAX(uss.StateTime) As StateTime, CAST(MAX(CASE WHEN tm.ResourceID IS NOT NULL THEN 1 ELSE 0 END) AS BIT) AS IsDeployedToThisComputer
FROM v_R_System s
INNER JOIN v_UpdateState_Combined uss ON s.ResourceID = uss.ResourceID AND NOT (uss.StateType = 500 AND (uss.StateID = 1 OR uss.StateID = 3))
INNER JOIN v_CIRelation cr ON uss.CI_ID = cr.ToCIID
INNER JOIN v_AuthListInfo ali ON cr.FromCIID = ali.CI_ID
INNER JOIN v_StateNames sn ON uss.StateType = sn.TopicType AND uss.StateID = sn.StateID
INNER JOIN v_CIAssignmentToCI atci ON uss.CI_ID = atci.CI_ID
INNER JOIN v_CIAssignment a ON atci.AssignmentID = a.AssignmentID
LEFT JOIN v_CITargetedMachines tm ON uss.CI_ID = tm.CI_ID AND s.ResourceID = tm.ResourceID
INNER JOIN v_UpdateInfo ui ON uss.CI_ID = ui.CI_ID
GROUP BY s.Name0, ali.Title, ui.BulletinID, ui.ArticleID, ui.Title, sn.StateName, ui.InfoURL

All Updates Including Unknown Status: Performance Optimized Version (My testing shows a 45x improvement in query times):

SELECT s.Name0, aliloc.[DisplayName] As SoftwareUpdateGroup, uss.BulletinID, uss.ArticleID, loc.[DisplayName] AS [UpdateTitle], loc.CIInformativeURL As InfoURL, sn.StateName, uss.LastErrorCode
    , MAX(uss.StateTime) As StateTime, CAST(MAX(CASE WHEN cm.MachineID IS NOT NULL THEN 1 ELSE 0 END) AS BIT) AS IsDeployedToThisComputer
FROM v_R_System s
INNER JOIN
(
    SELECT uci.CI_ID, cm.ResourceID, uci.ArticleID, uci.BulletinID
        , CASE
            WHEN cs.[Status] = 2 AND LastEnforcementMessageID > 0 THEN 402
            WHEN ISNULL(cs.Status, case when ss.ScanPackageVersion>=uci.MinSourceVersion then 1 else 0 end) != 2 OR LastEnforcementMessageID IS NULL or LastEnforcementMessageID = 0 THEN 500
        END As StateType
        , CASE
            WHEN cs.[Status] = 2 AND LastEnforcementMessageID > 0 THEN LastEnforcementMessageID
            WHEN ISNULL(cs.Status, case when ss.ScanPackageVersion>=uci.MinSourceVersion then 1 else 0 end) != 2 OR LastEnforcementMessageID IS NULL or LastEnforcementMessageID = 0 THEN ISNULL(cs.Status, case when ss.ScanPackageVersion>=uci.MinSourceVersion then 1 else 0 end)
        END As StateId
        , CASE
            WHEN cs.[Status] = 2 AND LastEnforcementMessageID > 0 THEN LastEnforcementMessageTime
            WHEN ISNULL(cs.Status, case when ss.ScanPackageVersion>=uci.MinSourceVersion then 1 else 0 end) != 2 OR LastEnforcementMessageID IS NULL or LastEnforcementMessageID = 0 THEN ISNULL(cs.LastStatusCheckTime, ss.ScanTime)
        END As StateTime
        , cs.LastErrorCode
    FROM CI_UpdateCIs (NOLOCK) uci
    INNER JOIN CI_ConfigurationItems (NOLOCK) AS ci ON uci.CI_ID = ci.CI_ID AND ci.CIType_ID IN (1, 8) AND ci.IsHidden = 0 AND ci.IsTombstoned = 0
    CROSS JOIN v_ClientMachines cm
    LEFT JOIN Update_ComplianceStatus (NOLOCK) cs on cs.CI_ID=uci.CI_ID and cs.MachineID=cm.ResourceID and cs.[Status] > 0
    LEFT JOIN Update_ScanStatus (NOLOCK) ss on ss.MachineID=cm.ResourceID and ss.UpdateSource_ID=uci.UpdateSource_ID
) uss ON s.ResourceID = uss.ResourceID AND NOT (uss.StateType = 500 AND (uss.StateId = 1 OR uss.StateId = 3))
INNER JOIN v_StateNames sn ON uss.StateType = sn.TopicType AND uss.StateID = sn.StateID
INNER JOIN v_CIRelation cr ON uss.CI_ID = cr.ToCIID
INNER JOIN CI_ConfigurationItems ali ON cr.FromCIID = ali.CI_ID AND ali.IsHidden=0 AND ali.CIType_ID=9
INNER JOIN v_LocalizedCIProperties_SiteLoc aliloc on ali.CI_ID = aliloc.CI_ID
INNER JOIN CI_AssignmentTargetedCIs (NOLOCK) atci ON uss.CI_ID = atci.CI_ID
INNER JOIN CI_CIAssignments (NOLOCK) a ON atci.AssignmentID = a.AssignmentID
INNER JOIN Collections_G (NOLOCK) c ON a.TargetCollectionID = c.CollectionID
LEFT JOIN CollectionMembers (NOLOCK) cm ON c.SiteID = cm.SiteID AND cm.MachineID = s.ResourceID
INNER JOIN v_LocalizedCIProperties_SiteLoc loc ON uss.CI_ID = loc.CI_ID
GROUP BY s.Name0, aliloc.[DisplayName], uss.BulletinID, uss.ArticleID, loc.[DisplayName], loc.CIInformativeURL, sn.StateName, uss.LastErrorCode

Determine Service Windows for Machines

Parse the binary field that stores the details of the schedule token and also what collection the service window comes from.

Reference: https://myitforum.com/cs2/blogs/jhuston/archive/2007/07/30/sms-schedule-token-strings.aspx

SELECT
    s.Name0
    , c.CollectionName
    , CASE (sw.rawSchedules / POWER(CAST(2 AS BIGINT), 19)) & (POWER(CAST(2 AS BIGINT), 3) - 1)
        WHEN 1 THEN 'Effective only'
        WHEN 2 THEN 'Every '
            + CAST((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 3)) & (POWER(CAST(2 AS BIGINT), 5) - 1) AS VARCHAR)
            + ' day' + CASE WHEN ((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 3)) & (POWER(CAST(2 AS BIGINT), 5) - 1)) > 1 THEN 's' ELSE '' END
        WHEN 3 THEN 'Every '
            + CAST((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 13)) & (POWER(CAST(2 AS BIGINT), 3) - 1) AS VARCHAR) + ' week'
            + CASE WHEN ((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 13)) & (POWER(CAST(2 AS BIGINT), 3) - 1)) > 1 THEN 's' ELSE '' END + ' on '
            + CASE (sw.rawSchedules / POWER(CAST(2 AS BIGINT), 16)) & (POWER(CAST(2 AS BIGINT), 3) - 1)
                WHEN 1 THEN 'Sunday'
                WHEN 2 THEN 'Monday'
                WHEN 3 THEN 'Tuesday'
                WHEN 4 THEN 'Wednesday'
                WHEN 5 THEN 'Thursday'
                WHEN 6 THEN 'Friday'
                WHEN 7 THEN 'Saturday'
            END
        WHEN 4 THEN 'Every '
            + CAST((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 12)) & (POWER(CAST(2 AS BIGINT), 3) - 1) AS VARCHAR)
            + ' month' + CASE WHEN ((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 12)) & (POWER(CAST(2 AS BIGINT), 3) - 1)) > 1 THEN 's' ELSE '' END
            + ' on the ' + CASE CAST((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 8)) & (POWER(CAST(2 AS BIGINT), 3) - 1) - 1 AS VARCHAR)
                WHEN 1 THEN 'first'
                WHEN 2 THEN 'second'
                WHEN 3 THEN 'third'
                WHEN 4 THEN 'fourth'
            END + ' '
            + CASE CAST((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 16)) & (POWER(CAST(2 AS BIGINT), 3) - 1) AS VARCHAR)
                WHEN 1 THEN 'Sunday'
                WHEN 2 THEN 'Monday'
                WHEN 3 THEN 'Tuesday'
                WHEN 4 THEN 'Wednesday'
                WHEN 5 THEN 'Thursday'
                WHEN 6 THEN 'Friday'
                WHEN 7 THEN 'Saturday'
            END
        WHEN 5 THEN 'Every '
            + CAST((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 10)) & (POWER(CAST(2 AS BIGINT), 4) - 1) AS VARCHAR)
            + ' month' + CASE WHEN ((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 10)) & (POWER(CAST(2 AS BIGINT), 4) - 1)) > 1 THEN 's' ELSE '' END
            + CASE WHEN (sw.rawSchedules / POWER(CAST(2 AS BIGINT), 14)) & (POWER(CAST(2 AS BIGINT), 4) - 1)> 0
                THEN ' on day ' + CAST((sw.rawSchedules / POWER(CAST(2 AS BIGINT), 14)) & (POWER(CAST(2 AS BIGINT), 4) - 1) AS VARCHAR)
                ELSE ' on the last day of the month'
            END
    END + ' starting on ' + CONVERT(VARCHAR, sw.StartTime, 120) + CASE WHEN sw.UseGMTTimes = 1THEN ' (UTC) ' ELSE ' (System Local) ' END
    + ' for ' + CAST(sw.Duration AS VARCHAR) + ' minutes'
    As [Description]
FROM
(
    SELECT CollectionID, CAST(CONVERT(BINARY(8),'0x'+ Schedules, 1) AS BIGINT) As RawSchedules, StartTime, UseGMTTimes, Duration
    FROM CEP_ServiceWindows
) as sw
INNER JOIN [dbo].[Collections] c ON sw.CollectionID = c.CollectionID
INNER JOIN [dbo].[CollectionMembers] cm ON c.SiteID = cm.SiteID
INNER JOIN [dbo].[System_DATA] s ON cm.MachineID = s.MachineID

Get a comma delimited list of CI_IDs for each Bulletin and Article

Useful for automating approval of the updates and much faster than WMI queries.

SELECT
DISTINCT uci2.BulletinID, uci2.ArticleID,
STUFF
(
    (
        SELECT N','+ CAST(uci.CI_ID As NVARCHAR(MAX))
        FROM [dbo].[CI_UpdateCIs] uci
        INNER JOIN CI_ConfigurationItems (NOLOCK) AS ci ON uci.CI_ID = ci.CI_ID AND ci.CIType_ID IN (1, 8) AND ci.IsHidden = 0 AND ci.IsTombstoned = 0
        WHERE (uci.BulletinID = uci2.BulletinId AND uci.ArticleID = uci2.ArticleID)
        ORDER BY uci.CI_ID
        FOR XML PATH('')
    ), 1, 1, ''
) AS CI_IDs
FROM
(
    SELECT DISTINCT uci.BulletinID, uci.ArticleID FROM [dbo].[CI_UpdateCIs] uci
    INNER JOIN CI_ConfigurationItems (NOLOCK) AS ci ON uci.CI_ID = ci.CI_ID AND ci.CIType_ID IN (1, 8) AND ci.IsHidden = 0 AND ci.IsTombstoned = 0
) AS uci2

Comments

Anonymous
June 08, 2015
The comment has been removed
Anonymous
June 17, 2015
Happy to help, but there are multiple queries in this list and I don't know which one you are working with. Also, when you say returns "NULL", all these queries return multiple columns, so are you stating that one of the columns returns NULL or that the query returns no rows.

Поделиться через