SCCM Report / SQL Query – Show packages with no deployments or task sequences linked

General maintenance and housekeeping of your SCCM 2012 environment is important to keep your environment healthy.  Packages and applications can fill up drive space quickly if they are not managed and maintained.  This query for SCCM 2012 will display a list of packages that is not being deployed and are not linked to any task sequences.  Generally this means they are not being used and may be retired.

SELECT pkg.Name as [Package Name], pkg.Version, pkg.PackageID,
CASE pkg.PackageType
WHEN 0 THEN ‘Software Distribution Package’
WHEN 3 THEN ‘Driver Package’
WHEN 4 THEN ‘Task Sequence Package’
WHEN 5 THEN ‘Software Update Package’
WHEN 6 THEN ‘Device Settings Package’
WHEN 7 THEN ‘Virtual Package’
WHEN 257 THEN ‘Image Package’
WHEN 258 THEN ‘Boot Image Package’
WHEN 259 THEN ‘OS Install Package’
END AS [Package Type], adv.AdvertisementID, tsp.Name as [TS Name]
FROM v_Package pkg
LEFT JOIN v_Advertisement adv on pkg.PackageID=adv.PackageID
LEFT JOIN v_TaskSequencePackageReferences tsr on pkg.PackageID=tsr.RefPackageID
LEFT JOIN v_TaskSequencePackage tsp on tsr.PackageID=tsp.PackageID
WHERE adv.AdvertisementID is null
AND tsp.Name is null
ORDER BY pkg.Name

This query will return information for all packages regardless of their package type, but you will notice that I added values for each package type so that you can differentiate between them.  Package type 5 for Software Updates never have deployments and task sequences associated to them so you can filter these out in your query.  Remember that in Software Updates you will deploy a Software Update Group to your devices and not a package.  It is however included in this query for reference.

5 Comments

  1. Bas

    To do this for Applications:

    SELECT pkg.PackageID, pkg.Name, ass.AssignmentName, tspr.PackageID
    FROM v_Package pkg
    inner join fn_ListApplicationCIs(1033) apps on apps.ModelName = pkg.SecurityKey
    left join vSMS_ApplicationAssignment ass on ass.AssignedCI_UniqueID = apps.CI_UniqueID
    left join v_TaskSequencePackageReferences tspr on tspr.ObjectID = apps.ModelName
    where PackageType = 8
    and apps.IsLatest=1
    and ass.AssignmentName IS NULL
    and tspr.PackageID IS NULL

    Reply
  2. JaimeH

    Thank you, this is useful information. How can I include the sourcepath information? I know Select name,pkgsourcepath from v_package , gives the source for all packages, but how to modify your query so it also includes the sourcepath?

    Reply
    1. Jacques (Post author)

      You can easily do this by adding the field pkg.PkgSourcePath into the SELECT statement.

      Reply
  3. Pingback: Query to show packages with no deployments or task sequences linked | automatizeblog

  4. JyriSi

    Thanks, this comes in handy.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *