List all updates that are targeted on devices within ConfigMgr

TargetedUpdatesOnDevices.sql

;with XMLNamespaces(DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration', 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' as ns1)
, cte as(
	SELECT
		UpdateGroupData.*
		, ci.CI_ID as Update_CI_ID
		, ci.SDMPackageDigest.value('(/DesiredConfigurationDigest/SoftwareUpdateBundle/ns1:Annotation/ns1:DisplayName/@Text)[1]', 'nvarchar(max)') as UpdateTitle
	FROM (

	SELECT ci.CI_ID as UpdateGroup_CI_ID
		  , cl.DisplayName as UpdateGroup_Name
		  , cl.Description as UpdateGroup_Description
		  , T2.u.value('@LogicalName','nvarchar(max)') as LogicalName
		  , ca.TargetCollectionID
		  , ca.StartTime
		  , ca.EnforcementDeadline
		  , ca.UserUIExperience
		  , ca.AssignmentEnabled
	FROM CI_ConfigurationItems ci
		join CI_LocalizedProperties cl on ci.CI_ID = cl.CI_ID and cl.LocaleID = 1033
		join CI_AssignmentTargetedGroups ctg on ci.CI_ID = ctg.CI_ID
		join CI_CIAssignments ca on ctg.AssignmentID = ca.AssignmentID and ca.IsTombstoned = 0
		outer apply ci.SDMPackageDigest.nodes('/DesiredConfigurationDigest/AuthorizationList/Updates/SoftwareUpdateReference') as T2(u)
	where ci.CIType_ID = 9
		and ci.IsTombstoned = 0
		and ci.IsEnabled = 1
		and ci.IsHidden = 0

	) UpdateGroupData
	join CI_ConfigurationItems ci on ci.CI_UniqueID = right(LogicalName, Len(logicalName) - 4)
)

select 
	cte.UpdateGroup_CI_ID,
	cte.UpdateGroup_Name,
	cte.Update_CI_ID,
	cte.UpdateTitle,
	cm.SiteID as CollectionId,
	cm.MachineID as ResourceId,
	cm.Name,
	cte.StartTime,
	cte.EnforcementDeadline
from cte
	join vCollectionMembers cm on cm.CollectionID = cte.TargetCollectionID

This script will create a result as follow: