Handy SCCM (Updates) Queries
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