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,
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.
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 ass.AssignmentName IS NULL
and tspr.PackageID IS NULL
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?
You can easily do this by adding the field pkg.PkgSourcePath into the SELECT statement.
Pingback: Query to show packages with no deployments or task sequences linked | automatizeblog
Thanks, this comes in handy.