SCCM report all applications with dependency programs

Something like this possibly:

declare @__timezoneoffset int
select @__timezoneoffset = DateDiff(ss,getutcdate(),getdate())

;WITH ProgramHierarchy(ParentID,[Level],PackageID,Birth,AdvertID,AdvertisementName,[Exp],ProgramName,Success,
Acpt,Succ,Fail,Wait,CollID,ChildPackageID,ChildProgramName,[Path]) AS
(
SELECT
PRG.PackageID AS ParentID,
CASE WHEN PRG.DependentProgram = '' then 0 ELSE 1 END AS [Level],
PRG.PackageID,
CAST(AD.PresentTime as date) as Birth,
AD.AdvertisementID as AdvertID,
CAST(AD.AdvertisementName AS VARCHAR(100)) AS AdvertisementName,
CASE WHEN (AD.ExpirationTime <= GETDATE() and AD.ExpirationTimeEnabled = 2) or
(0x00001000 & PRG.ProgramFlags)/0x00001000 = 1 THEN 0 ELSE 1 END AS [Exp],
PRG.ProgramName,
CAST(tmp1.Success as date) as Success,
SUM(CASE CAS.LastState when 0 then 0 else 1 end) AS Acpt,
SUM(CASE CAS.LastStateName when 'Succeeded' then 1 when 'Reboot Pending' then 1
when 'Will Not Rerun' then 1 else 0 end) AS Succ,
SUM(CASE CAS.LastStateName when 'Failed' then 1
when 'Retrying' then 1 else 0 end) AS Fail,
SUM(CASE CAS.LastStateName when 'Waiting' then 1 else 0 end) AS Wait,
AD.CollectionID AS CollID,
CAST(LEFT(PRG.DependentProgram,8) as VARCHAR(8)) AS ChildPackageID,
CAST(SUBSTRING(PRG.DependentProgram,CHARINDEX(';',PRG.DependentProgram,10)+1,999) AS VARCHAR(100)) AS ChildProgramName,
CAST(PRG.PackageID + SUBSTRING(PRG.DependentProgram,CHARINDEX(';',PRG.DependentProgram,10)+1,999) + AD.AdvertisementID AS VARCHAR(MAX)) AS [Path]
FROM
v_Program AS PRG
Left JOIN v_advertisement AD on PRG.PackageID = AD.PackageID
LEFT JOIN v_ClientAdvertisementStatus CAS on AD.AdvertisementID = CAS.AdvertisementID
left join (select cs.AdvertisementID, DATEADD(ss,@__timezoneoffset,MAX(CS.LastStatusTime)) as Success
from v_ClientAdvertisementStatus cs where cs.LastStateName = 'Succeeded' or cs.LastStateName = 'Reboot Pending'
or cs.LastStateName = 'Will Not Rerun' group by cs.AdvertisementID) tmp1 on tmp1.AdvertisementID = ad.AdvertisementID
WHERE AD.ProgramName = PRG.ProgramName and PRG.ProgramName != '*'
GROUP by PRG.PackageID, PRG.ProgramName, PRG.DependentProgram, AD.AdvertisementName, AD.PresentTime,
AD.AdvertisementID, AD.CollectionID, PRG.ProgramFlags, AD.ExpirationTime, AD.ExpirationTimeEnabled,
tmp1.Success
UNION ALL

--RECURSIVE QUERY - recursively join to v_Program to get level 1,2,3,etc.
SELECT
Parent.ParentID AS ParentID,
parent.[Level]+1 AS [Level],
child.PackageID,
Parent.Birth,
Parent.AdvertId AS AdvertID,
parent.AdvertisementName as AdvertisementName,
Parent.[Exp] as [Exp],
child.ProgramName,
parent.Success as Success,parent.Acpt as Acpt,parent.Succ as Succ,parent.Fail as Fail,parent.Wait as Wait,
parent.CollID AS CollID,
CAST(LEFT(child.DependentProgram,8) as VARCHAR(8)) AS ChildPackageID,
CAST(SUBSTRING(child.DependentProgram,CHARINDEX(';',child.DependentProgram,10)+1,999) AS VARCHAR(100)) AS ChildProgramName,
CAST(parent.[Path] + child.PackageID + SUBSTRING(child.DependentProgram,CHARINDEX(';',child.DependentProgram,10)+1,999) AS VARCHAR(MAX)) AS [Path]
FROM
v_Program AS child
INNER JOIN ProgramHierarchy AS parent
ON child.PackageID = parent.ChildPackageID
AND parent.ChildProgramName = child.ProgramName and child.ProgramName != '*'
)
SELECT PH.ParentID, [Level], PK.PackageID, PK.Name, PK.Version, PH.Birth, PH.AdvertID, PH.AdvertisementName, PH.[Exp],
PH.ProgramName, PH.Success, PH.Acpt, PH.Succ, PH.Fail, PH.Wait,
Sum(Case when tmp0.syst IS NULL then 0 else tmp0.syst end) + (CASE when tmp2.Usrs IS NULL then 0 else tmp2.Usrs end)
+ (CASE when tmp3.usr IS NULL then 0 else tmp3.usr end) as Total, COL.CollectionID as CollID,COL.Name, PSRS.SourceSize,
Case
when PK.PkgSourcePath like '%\server\%' then 'Server'
when PK.PkgSourcePath like '%\OSD\%' then 'OSD'
when PK.PkgSourcePath like '%\Patches\%' then 'Patches'
when PK.PkgSourcePath = '' then '' else 'Other' END AS Folder,PH.[Path]
FROM v_Package PK
left JOIN ProgramHierarchy PH on Pk.PackageID = Ph.PackageID
LEFT JOIN v_PackageStatusRootSummarizer PSRS on PK.PackageID = PSRS.PackageID
LEFT JOIN v_Collection COL on PH.CollID = COL.CollectionID
left join (select fcm.CollectionID, COUNT(fcm.resourceid) as Syst from v_FullCollectionMembership fcm
where fcm.ResourceType = '5' group by fcm.CollectionID) tmp0 on tmp0.CollectionID = PH.CollID
left join (select fcm.CollectionID, COUNT(fcm.resourceid) as usr from v_FullCollectionMembership fcm
where fcm.ResourceType = '4' group by fcm.CollectionID) tmp3 on tmp3.CollectionID = PH.CollID
left join (select fcm.CollectionID, COUNT(ugn.ResourceID) as Usrs from v_FullCollectionMembership fcm
join v_RA_User_UserGroupName ugn on fcm.Name = ugn.User_Group_Name0
where fcm.ResourceType = '3' group by fcm.CollectionID) tmp2 on tmp2.CollectionID = PH.CollID
Group By PH.ParentID, PK.PackageID, PK.Name, PK.Version, PSRS.SourceSize, PK.PkgSourcePath,
PH.Success, PH.Acpt, PH.Succ, PH.Fail, PH.Wait, PH.AdvertID,PH.Birth,
PH.AdvertisementName, PH.[Exp], COL.CollectionID, COL.Name, PH.ProgramName, PH.[Path], PH.[Level],
tmp0.Syst, tmp2.Usrs, tmp3.usr

Union All

select distinct tsp.PackageID as ParentID, '1', tsp.PackageID, tsp.Name, tsp.Version,Cast(Ad.PresentTime as date),'','',
CASE WHEN (AD.ExpirationTime <= GETDATE() and AD.ExpirationTimeEnabled = 2) THEN 0 ELSE 1 END AS [Exp],
'*', max(tmp1.Success),
SUM(CASE CAS.LastState when 0 then 0 else 1 end) AS Acpt,
SUM(CASE CAS.LastStateName when 'Succeeded' then 1 when 'Reboot Pending' then 1
when 'Will Not Rerun' then 1 else 0 end) AS Succ,
SUM(CASE CAS.LastStateName when 'Failed' then 1
when 'Retrying' then 1 else 0 end) AS Fail,
SUM(CASE CAS.LastStateName when 'Waiting' then 1 else 0 end) AS Wait,
Sum(Case when tmp0.syst IS NULL then 0 else tmp0.syst end) + (CASE when tmp2.Usrs IS NULL then 0 else tmp2.Usrs end)
+ (CASE when tmp3.usr IS NULL then 0 else tmp3.usr end) as Total,'','','',
'-TS-', tsp.PackageID + '_' + tsp.DependentProgram
from v_TaskSequencePackage tsp
left join v_Advertisement AD on tsp.PackageID = AD.PackageID
left join v_ClientAdvertisementStatus CAS on AD.AdvertisementID = CAS.AdvertisementID
left join (select cs.AdvertisementID, DATEADD(ss,@__timezoneoffset,MAX(CS.LastStatusTime)) as Success
from v_ClientAdvertisementStatus cs where cs.LastStateName = 'Succeeded' or cs.LastStateName = 'Reboot Pending'
or cs.LastStateName = 'Will Not Rerun' group by cs.AdvertisementID) tmp1 on tmp1.AdvertisementID = ad.AdvertisementID
left join (select fcm.CollectionID, COUNT(fcm.resourceid) as Syst from v_FullCollectionMembership fcm
where fcm.ResourceType = '5' group by fcm.CollectionID) tmp0 on tmp0.CollectionID = ad.CollectionID
left join (select fcm.CollectionID, COUNT(fcm.resourceid) as usr from v_FullCollectionMembership fcm
where fcm.ResourceType = '4' group by fcm.CollectionID) tmp3 on tmp3.CollectionID = ad.CollectionID
left join (select fcm.CollectionID, COUNT(ugn.ResourceID) as Usrs from v_FullCollectionMembership fcm
join v_RA_User_UserGroupName ugn on fcm.Name = ugn.User_Group_Name0
where fcm.ResourceType = '3' group by fcm.CollectionID) tmp2 on tmp2.CollectionID = ad.CollectionID

Group By tsp.PackageID, tsp.Name, tsp.Version, AD.PresentTime,Ad.AdvertisementID,AD.AdvertisementName,
tsp.DependentProgram,AD.ExpirationTime, AD.ExpirationTimeEnabled, tmp0.Syst, tmp2.Usrs, tmp3.usr

Union All

select distinct tsr.PackageID as ParentID, '2', tsr.ReferencePackageID as PackageID, tsr.ReferenceName, tsr.ReferenceVersion,
Cast(Ad.PresentTime as date),'','', CASE WHEN (AD.ExpirationTime <= GETDATE() and AD.ExpirationTimeEnabled = 2) THEN 0 ELSE 1 END AS [Exp],
'*', max(tmp1.Success), SUM(CASE CAS.LastState when 0 then 0 else 1 end) AS Acpt,
SUM(CASE CAS.LastStateName when 'Succeeded' then 1 when 'Reboot Pending' then 1
when 'Will Not Rerun' then 1 else 0 end) AS Succ,
SUM(CASE CAS.LastStateName when 'Failed' then 1
when 'Retrying' then 1 else 0 end) AS Fail,
SUM(CASE CAS.LastStateName when 'Waiting' then 1 else 0 end) AS Wait,
Sum(Case when tmp0.syst IS NULL then 0 else tmp0.syst end) + (CASE when tmp2.Usrs IS NULL then 0 else tmp2.Usrs end)
+ (CASE when tmp3.usr IS NULL then 0 else tmp3.usr end) as Total,'','',
psrs.SourceSize, '-TS-' AS Folder, tsr.PackageID + tsr.ReferencePackageID
from v_TaskSequenceReferencesInfo tsr
left join v_Advertisement AD on tsr.PackageID = AD.PackageID
left join v_ClientAdvertisementStatus CAS on AD.AdvertisementID = CAS.AdvertisementID
left join (select cs.AdvertisementID, DATEADD(ss,@__timezoneoffset,MAX(CS.LastStatusTime)) as Success
from v_ClientAdvertisementStatus cs where cs.LastStateName = 'Succeeded' or cs.LastStateName = 'Reboot Pending'
or cs.LastStateName = 'Will Not Rerun' group by cs.AdvertisementID) tmp1 on tmp1.AdvertisementID = ad.AdvertisementID
left join (select fcm.CollectionID, COUNT(fcm.resourceid) as Syst from v_FullCollectionMembership fcm
where fcm.ResourceType = '5' group by fcm.CollectionID) tmp0 on tmp0.CollectionID = ad.CollectionID
left join (select fcm.CollectionID, COUNT(fcm.resourceid) as usr from v_FullCollectionMembership fcm
where fcm.ResourceType = '4' group by fcm.CollectionID) tmp3 on tmp3.CollectionID = ad.CollectionID
left join (select fcm.CollectionID, COUNT(ugn.ResourceID) as Usrs from v_FullCollectionMembership fcm
join v_RA_User_UserGroupName ugn on fcm.Name = ugn.User_Group_Name0
where fcm.ResourceType = '3' group by fcm.CollectionID) tmp2 on tmp2.CollectionID = ad.CollectionID
join v_PackageStatusRootSummarizer psrs on tsr.ReferencePackageID = psrs.PackageID

Group By tsr.PackageID, tsr.ReferencePackageID, tsr.ReferenceName, tsr.ReferenceVersion, psrs.SourceSize,AD.PresentTime,
Ad.AdvertisementID, AD.AdvertisementName, AD.ExpirationTime, AD.ExpirationTimeEnabled,tmp0.Syst, tmp2.Usrs, tmp3.usr

Union All

select distinct tsp.packageid as ParentID, '2', pk.PackageID, pk.Name, pk.Version, CAST(AD.PresentTime as Date),'','',
CASE WHEN (AD.ExpirationTime <= GETDATE() and AD.ExpirationTimeEnabled = 2) THEN 0 ELSE 1 END AS [Exp],
'*', max(tmp1.Success),
SUM(CASE CAS.LastState when 0 then 0 else 1 end) AS Acpt,
SUM(CASE CAS.LastStateName when 'Succeeded' then 1 when 'Reboot Pending' then 1
when 'Will Not Rerun' then 1 else 0 end) AS Succ,
SUM(CASE CAS.LastStateName when 'Failed' then 1
when 'Retrying' then 1 else 0 end) AS Fail,
SUM(CASE CAS.LastStateName when 'Waiting' then 1 else 0 end) AS Wait,
Sum(Case when tmp0.syst IS NULL then 0 else tmp0.syst end) + (CASE when tmp2.Usrs IS NULL then 0 else tmp2.Usrs end)
+ (CASE when tmp3.usr IS NULL then 0 else tmp3.usr end) as Total,'','',psrs.SourceSize,
'-TS-', tsp.PackageID + '_' + tsp.DependentProgram
from v_TaskSequencePackage tsp
join v_Package PK on LEFT(tsp.DependentProgram,8) = pk.PackageID
left join v_Advertisement AD on tsp.PackageID = AD.PackageID
left join v_ClientAdvertisementStatus CAS on AD.AdvertisementID = CAS.AdvertisementID
left join (select cs.AdvertisementID, DATEADD(ss,@__timezoneoffset,MAX(CS.LastStatusTime)) as Success
from v_ClientAdvertisementStatus cs where cs.LastStateName = 'Succeeded' or cs.LastStateName = 'Reboot Pending'
or cs.LastStateName = 'Will Not Rerun' group by cs.AdvertisementID) tmp1 on tmp1.AdvertisementID = ad.AdvertisementID
left join (select fcm.CollectionID, COUNT(fcm.resourceid) as Syst from v_FullCollectionMembership fcm
where fcm.ResourceType = '5' group by fcm.CollectionID) tmp0 on tmp0.CollectionID = ad.CollectionID
left join (select fcm.CollectionID, COUNT(fcm.resourceid) as usr from v_FullCollectionMembership fcm
where fcm.ResourceType = '4' group by fcm.CollectionID) tmp3 on tmp3.CollectionID = ad.CollectionID
left join (select fcm.CollectionID, COUNT(ugn.ResourceID) as Usrs from v_FullCollectionMembership fcm
join v_RA_User_UserGroupName ugn on fcm.Name = ugn.User_Group_Name0
where fcm.ResourceType = '3' group by fcm.CollectionID) tmp2 on tmp2.CollectionID = ad.CollectionID
join v_PackageStatusRootSummarizer psrs on pk.PackageID = psrs.PackageID

Group By tsp.PackageID, pk.PackageID, pk.Name, pk.Version, tsp.DependentProgram, psrs.SourceSize,AD.PresentTime,
Ad.AdvertisementID, AD.AdvertisementName, AD.ExpirationTime, AD.ExpirationTimeEnabled,tmp0.Syst, tmp2.Usrs, tmp3.usr

ORDER BY
PH.[Path], PH.[Level]