Combined Program or Task Sequence Status Report

I put up the Combined Application Status Report, but didn’t do the combined one for packages and task sequences. This report combines all statuses (Successful, In Progress, etc.) onto one page.

To make it more user friendly, there’s a parameter @Type, where the user selects “Task Sequence” or “Program”. This is to filter the drop down for Advertisement:

select AdvertisementID, AdvertisementName from v_Advertisement where (ProgramName = '*' and @Type = 'Task Sequence') or (ProgramName  '*' and @Type = 'Program')
order by 2

This populates a second parameter, @AdvertisementID.

select
  s.ResourceID
, s.Full_Domain_Name0
, s.Name0
, s.User_Domain0 + '\' + s.User_Name0 LastUser
, DATEADD(HH, DATEDIFF (hh, GETUTCDATE(),GETDATE()), cas.LastStatusTime) LastStatusTime
, cas.LastStateName
, cas.LastStatusMessageIDName
, cas.LastAcceptanceStateName
, cas.LastAcceptanceMessageIDName
, min (i.IP_Addresses0) IP_Address0
from v_R_System s
inner join v_ClientAdvertisementStatus cas on cas.ResourceID = s.ResourceID
inner join v_Advertisement a on a.AdvertisementID = cas.AdvertisementID
left join v_RA_System_IPAddresses i on s.ResourceID = i.ResourceID and (i.IP_Addresses0 like '1%' and i.IP_Addresses0 not like '169.%')
where cas.AdvertisementID = @AdvertisementID
group by 
  s.ResourceID
, s.Full_Domain_Name0
, s.Name0
, s.User_Domain0 + '\' + s.User_Name0
, cas.LastStatusTime
, cas.LastStateName
, cas.LastStatusMessageIDName
, cas.LastAcceptanceStateName
, cas.LastAcceptanceMessageIDName
order by 2, 3