Finding distributed packages that may not be in use

select
  p2.PackageID
, p2.Name
, p2.Version
, p2.Language
, p2.Manufacturer
, p2.Description
, count (*)
from v_PackageStatusDistPointsSumm dp
left join v_tasksequencepackagereferences ts on ts.RefPackageID = dp.PackageID -- check for use in task sequences
left join v_Advertisement a on a.PackageID = dp.PackageID -- check for advertisements
left join v_Package p on dp.PackageID = p.PackageID and PackageType in (3 /* drivers */, 5 /* patches */) and ShareName = '' -- not a share
left join v_DeploymentSummary ds on ds.PackageID = dp.PackageID -- check for deployments
inner join v_Package p2 on p2.PackageID = dp.PackageID
where /* ServerNALPath like '%nameofserver%' -- can filter to one server
and */ ts.RefPackageID is null and a.PackageID is null and p.PackageID is null and ds.PackageID is null
group by
  p2.PackageID
, p2.Name
, p2.Version
, p2.Language
, p2.Manufacturer
, p2.Description
order by p2.PackageID
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s