Close Menu
    Facebook X (Twitter) Instagram
    Sunday, July 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»SCCM 2007»SCCM report all applications with dependency programs

    SCCM report all applications with dependency programs

    Eswar KonetiBy Eswar KonetiJuly 11, 4:09 pm1 Min Read SCCM 2007 8,328 Views
    Share
    Facebook Twitter LinkedIn Reddit

     

    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

    CM07 configuration Manager report dependency applications dependency apps report for all applications with dependencies. report for run another program first sccm report dependent applications list SQL code
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    SCCM SQL Report – Compare Installed Apps on TWO Different Computers

    July 13, 10:35 am

    Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

    October 11, 8:48 pm

    SCCM report list collections with no deployments

    December 05, 12:04 pm

    3 Comments

    1. Nik on February 24, 2017 9:45 AM

      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. Sridhar on March 14, 2013 5:38 PM

      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
      • Eswar Koneti on March 17, 2013 10:57 PM

        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 ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.