SCCM Package Clean Up activity? When you Build SCCM in your Environment, you create lot of packages to deploy onto clients But later sometime, you get updated versions for it or no more in use.
Packages which are not in use resides in Database and occupies disk space on the source server as well on the Distribution Points.
As SCCM admin guy, you should look at package clean up activity once in year or so depends on the requirement.
For Packages which has no advertisements, look here http://eskonr.com/2012/10/sccm-report-software-distribution-packages-with-no-advertisements/
Use this report to list packages which are not used recently for 1 year based and it is filtered with its advertisement status. This will not get the packages which are used in Task Sequences.
If you have any packages which are not used for 1 year but still if they are part of Task Sequence, you won’t see the results.
SELECT
A.AdvertisementName AS [Advertisement Name],
A.PresentTime AS Created,
MAX(CAS.LastStatusTime) AS [Last Time Used],
p.packageID,
A.ProgramName,
A.CollectionID,
P.PkgSourcePath,
(SELECT COUNT(PkgID) FROM vPkgStatusSummaryDistPts WHERE PkgID = p.PackageID) AS [No of DP]
FROM
v_Package P
INNER JOIN v_Advertisement A ON P.PackageID = A.PackageID
INNER JOIN v_ClientAdvertisementStatus CAS ON A.AdvertisementID = CAS.AdvertisementID
WHERE
(CAS.LastStateName != 'Accepted - No Further Status')AND
p.PackageID NOT IN (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo) and p.Packagetype=0GROUP BY
A.AdvertisementName,
A.PresentTime,
A.CollectionID,
P.PkgSourcePath,
P.Name,
P.PackageID,
A.ProgramName,
P.Description
HAVING
(MAX(CAS.LastStatusTime) IS NOT NULL)
AND datediff(mm,MAX(CAS.LastStatusTime),getdate()-365) > 0ORDER BY
A.AdvertisementName,
A.PresentTime
Good Luck!
4 Comments
Thanks for your reply, but neither me or a college (who knows more SQL, but not SCCM) was sure what you were referring to when you say they I have to adjust the double quotes, i.e. " symbol? To what part of the code are you referring to?
Hi Fredrik,
there is only one single quote you need to replace at Accepted – No Further Status.I just tried and it worked perfect.
Likks like what I need but I get som error:
---
[[42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Oncorect syntax ner ". -- [42000][156][Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax ner the word 'and'.
----
I would like to se packages not being used eaven if thay are usd in a Task Sequences
you will have adjust the double quotes. replace them and run the query.
if you would like to have all packages from the Task sequence,remove the below lines.
AND
p.PackageID NOT IN (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo)