I had requirement to check for some specific packages if their programs are used in any other packages or not . Take an example that ,Microsoft visual C++ is prereq for multiple applications and to know what packages used this specific VC++ ,it will be hard to find all parent packages using VC++ as dependency program.
How do we get list of all packages that are using specific application as dependency ? in this case it is VC++.
In SCCM ,depedent programs are stored in view called 'v_Program' with column name 'DependentProgram' .So we can use this view to retrive the information for VC++.
Here is simple report that provides all packages which are used by dependent ones.
Manual method report:
select ProgramName, PackageID,ProgramName from v_Program
where DependentProgram like
'%MicrosoftVisualC++Redistributable_2008%'
Prompting report :
select ProgramName, PackageID,ProgramName from v_Program
where DependentProgram =@dependencypgm
Prompt for dependencypgm: Select DependentProgram from v_Program
Note: SCCM stores dependent program as packageID;programname where packageID is main application where dependency used and program is run another program first.
also refer blog post by Garth http://support.enhansoft.com/Blogs/post/Depended-Package.aspx
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]
Hi Eswar,
select ProgramName, PackageID,ProgramName from v_Program
where DependentProgram like
‘%MicrosoftVisualC++Redistributable_2008%’
I hope the query provided will fetch the packages that use Visual C++ as dependency. If it is so,
is there any possiblity to get the status of the packages that are used as dependency for other main application?
Thanks in advance!
Regards
Sridhar
I don't think so because the dependency program will not have any advertisements just installation program which will be triggered by its parent adv.
if you need the status of the dep program,you can create report based on its status messages.never tried it before.