SCCMRequiredSoftwareUpdatesReport.sql

declare @COLLID AS nvarchar(8) = 'ABC12345'

declare @Today DateTime = GetDate()
declare @30DaysBeforeToday DateTime = DateAdd("d",-30,GetDate())
declare @31DaysBeforeToday DateTime = DateAdd("d",-31,GetDate())
declare @60DaysBeforeToday DateTime = DateAdd("d",-60,GetDate())
declare @61DaysBeforeToday DateTime = DateAdd("d",-61,GetDate())
declare @90DaysBeforeToday DateTime = DateAdd("d",-90,GetDate())
declare @91DaysBeforeToday DateTime = DateAdd("d",-91,GetDate())
declare @10YearsAgo DateTime = DateAdd("d",-3650,GetDate())

declare @DetectionStateUnknown int = 0
declare @UpdateNotRequired int = 1
declare @UpdateIsRequired int = 2
declare @UpdateAlreadyInstalled int = 3

declare @Applications int = 16777242
declare @CriticalUpdates int = 16777243
declare @DefinitionUpdates int = 16777244
declare @Drivers int = 16777245
declare @FeaturePacks int = 16777246
declare @SecurityUpdates int = 16777247
declare @ServicePacks int = 16777248
declare @Tools int = 16777249
declare @UpdateRollups int = 16777250
declare @Updates int = 16777251
declare @Upgrades int = 16777252
declare @WSUSInfrastructureUpdates int = 16777253
declare @DriverSets int = 16777488

select
	ComputerName = CS.Name0,
	Domain = CS.Domain0,
	UserName = CS.UserName0,
	CS.ResourceID,
	FCM.CollectionID,
	OS = OS.Caption0,
	LastSoftwareUpdateScan = CONVERT(VARCHAR(26), USS.LastScanTime, 100),
	IPv4 = IP.IPAddress,

	PendingReboot = 
		case when BGBLD.ClientState > 0
		then
			STUFF(
				(
					select  '; ' + BitMask.Flag
					from (
						values
						(1, 'SCCM Client'),
						(2, 'File Rename Operation'),
						(3, 'Windows Update Agent'),
						(4, 'Add or Remove Features')
					) BitMask(BitNumber, Flag)
					where
						BGBLD.ClientState & POWER(2, BitMask.BitNumber - 1) = POWER(2, BitMask.BitNumber - 1)
					for xml path (''),
					type --no clue what this does, but it is needed or this doesn't work
				).value(
					'.',
					'varchar(max)'
				),
				1,
				2,
				''
			)
		else 'No'
		end,

	UpdatesRequired =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				then 1
				else 0
				end
			) > 0
		then 
			sum(
				case when UCS.Status = @UpdateIsRequired
				then 1
				else 0
				end
			)
		else 0
		end,
	
	'0 to 30' =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and UI.DateRevised >= @30DaysBeforeToday
				and UI.DateRevised < @Today
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status=2
				and UI.DateRevised >= @30DaysBeforeToday
				and UI.DateRevised < @Today
				then 1
				else 0
				end
			)
		else 0
		end,

	'31 to 60' =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and UI.DateRevised >= @60DaysBeforeToday
				and UI.DateRevised < @31DaysBeforeToday
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and UI.DateRevised >= @60DaysBeforeToday
				and UI.DateRevised < @31DaysBeforeToday
				then 1
				else 0
				end
			)
		else 0
		end,

	'61 to 90' =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and UI.DateRevised >= @90DaysBeforeToday
				and UI.DateRevised < @61DaysBeforeToday
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and UI.DateRevised >= @90DaysBeforeToday
				and UI.DateRevised < @61DaysBeforeToday
				then 1
				else 0
				end
			)
		else 0
		end,

	'91 plus' =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and UI.DateRevised >= @10YearsAgo
				and UI.DateRevised < @91DaysBeforeToday
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and UI.DateRevised >= @10YearsAgo
				and UI.DateRevised < @91DaysBeforeToday
				then 1
				else 0
				end
			)
		else 0
		end,

	SecurityUpdates =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @SecurityUpdates
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @SecurityUpdates
				then 1
				else 0
				end
			)
		else 0
		end,

	CriticalUpdates =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @CriticalUpdates
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @CriticalUpdates
				then 1
				else 0
				end
			)
		else 0
		end,

	ServicePacks =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @ServicePacks
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @ServicePacks
				then 1
				else 0
				end
			)
		else 0
		end,

	UpdateRollups =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @UpdateRollups
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @UpdateRollups
				then 1
				else 0
				end
			)
		else 0
		end,

	DefinitionUpdates =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @DefinitionUpdates
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @DefinitionUpdates
				then 1
				else 0
				end
			)
		else 0
		end,

	'Updates' =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @Updates
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @Updates
				then 1
				else 0
				end
			)
		else 0
		end,
		
	FeaturePacks =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @FeaturePacks
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @FeaturePacks
				then 1
				else 0
				end
			)
		else 0
		end,
		
	Upgrades =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @Upgrades
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @Upgrades
				then 1
				else 0
				end
			)
		else 0
		end,
		
	WSUSInfrastructureUpdates =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @WSUSInfrastructureUpdates
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @WSUSInfrastructureUpdates
				then 1
				else 0
				end
			)
		else 0
		end,
		
	Tools =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @Tools
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @Tools
				then 1
				else 0
				end
			)
		else 0
		end,
		
	Applications =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @Applications
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @Applications
				then 1
				else 0
				end
			)
		else 0
		end,
		
	Drivers =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @Drivers
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @Drivers
				then 1
				else 0
				end
			)
		else 0
		end,
		
	DriverSets =
		case when
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @DriverSets
				then 1
				else 0
				end
			) > 0
		then
			sum(
				case when UCS.Status = @UpdateIsRequired
				and CI.CategoryInstanceID = @DriverSets
				--and CI.CategoryInstanceName = 'Driver Sets' --This works but string comparison is less efficient than integer comparison...I think
				then 1
				else 0
				end
			)
		else 0
		end

from
	v_UpdateComplianceStatus UCS
left outer join
	dbo.v_GS_COMPUTER_SYSTEM CS
on
	CS.ResourceID = UCS.ResourceID
join
	v_CICategories_All CICA
on
	CICA.CI_ID = UCS.CI_ID
join
	v_CategoryInfo CI
on
	CICA.CategoryInstance_UniqueID = CI.CategoryInstance_UniqueID
and
	CI.CategoryTypeName = 'UpdateClassification'
left join
	v_GS_WORKSTATION_STATUS GSWS
on
	GSWS.ResourceID = CS.ResourceID
left join
	v_FullCollectionMembership FCM
on
	FCM.ResourceID = CS.ResourceID
left join
	BGB_LiveData BGBLD
on
	BGBLD.ResourceID = UCS.ResourceID
inner join
	v_UpdateInfo UI
on
	UI.CI_ID = UCS.CI_ID
left join
	v_GS_OPERATING_SYSTEM OS
on
	OS.ResourceID = UCS.ResourceID
left join
	v_UpdateScanStatus USS
on
	USS.ResourceID = UCS.ResourceID
left join
	(
		select
			RASIPA1.ResourceID AS RID,
			IPAddress = substring(
				(
					select
						'; ' + IP_Addresses0
					from
						v_RA_System_IPAddresses RASIPA2
					where
						RASIPA2.IP_Addresses0 NOT LIKE '0.%' --Exclude subnet 0.0.0.0/8 reserved for "This Network" by IANA RFC 5735
					and
						RASIPA2.IP_Addresses0 NOT LIKE '127.%' --Exclude subnet 127.0.0.0/8 reserved for Loopback by IANA RFC 5735
					and
						RASIPA2.IP_Addresses0 NOT LIKE '169.254.%' --Exclude subnet 169.254.0.0/16 reserved for Link Local by IANA RFC 5735
					and
						RASIPA2.IP_Addresses0 NOT LIKE '%:%' --Exclude IPv6
					and
						RASIPA1.ResourceID = RASIPA2.ResourceID
					for xml path('')
				),
				3,
				100
			)
		from
			v_RA_System_IPAddresses RASIPA1
		group by
			ResourceID
	) IP
on
	IP.RID = UCS.ResourceID

where
	FCM.collectionid = @COLLID
and
	UI.IsExpired = 0
and
	UI.IsSuperseded = 0
and
	(
		CI.CategoryInstanceID = @CriticalUpdates
		or
			CI.CategoryInstanceID = @SecurityUpdates
		or
			CI.CategoryInstanceID = @ServicePacks
		or
			CI.CategoryInstanceID = @UpdateRollups
		or
			CI.CategoryInstanceID = @Updates
		or
			CI.CategoryInstanceID = @DefinitionUpdates
	)
group by --don't understand why grouping is needed, but errors result unless grouped by each of these properties
	CS.Name0,
	CS.UserName0,
	CS.Domain0,
	CS.ResourceID,
	FCM.CollectionID,
	OS.Caption0,
	USS.LastScanTime,
	IPAddress,
	BGBLD.ClientState
order by
	ComputerName