SCCM report all applications with dependency programs

 

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

3 Responses to "SCCM report all applications with dependency programs"

  1. 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]

    Reply
  2. 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

    Reply
    1. 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.

      Reply

Leave a Reply to Eswar Koneti Cancel reply