SCCM report software Distribution Packages with No Advertisements

If you are Performing package cleanup,this query might help you to Identify how many packages with no advertisements(standard advertisement and Task Sequence)

so you know What action to Take next!

select  v_Package.PackageID, v_Package.Name,v_Package.SourceVersion,v_Package.SourceDate
from dbo.v_package
Where
packageID not in (select PackageID from dbo.v_Advertisement)and
PackageID not in (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo) and
v_Package.name not like '%osd%' and V_package.PackageType ='0'

group by v_Package.PackageID, v_Package.Name,v_Package.SourceVersion,v_Package.SourceDate

order by v_Package.PackageID

If you want to know other packages like OSD,Driver,Software update packages,you can replace Package type with below Values :

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’

Leave a Reply