SCCM report Count packages with no advertisements

This blog post will help you to identify packages (count) for which there is no advertisement created and not used these packages in any task sequence.Once you have the packages ,you can further take action whether you really need these packages to exist in SCCM or cleanup to save disk space and avoid unused packages in sccm.

SQL Query to run from SQL server management studio or use this to create report.

select     PackageType =
Case 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 ,
COUNT(PackageType) as Total

packageID not in (select PackageID from dbo.v_Advertisement)and
PackageID not in (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo)

group by PackageType

4 Responses to "SCCM report Count packages with no advertisements"

  1. Hi Eswar, Nice Blog and very useful stuff i find in your Blog , could you please help me in getting query to find 1. Applications with no advertisements,( i found query to get packages with no advertisements) 2. Computers details with more than 10 Failed Advertisements

  2. Hi,

    Could you please help in getting SCCM report Count packages with no advertisements. in this we need package Details like Package Name with ID and Versions of it.

    Please help me in getting this report

    Thanks in advance,



Leave a Reply