Friday, March 23, 2012

Packages referenced by a specific task sequence that are Missing

So you have many task sequences but when you setup a DP you need to know if that DP is ready for that Task sequence. There are reports for Packages on a DP, Packages for a Task Sequence but here is a report that will tell you what packages are missing from the DP for the specific task sequence.

The current draw back is that this report doesn't check to makes sure the package is already installed, just deployed so I will update that soon. But here you go. Hope this helps with some deployments.

TaskSequenceID = Task Sequence ID
ID = Distribution point ID

SELECT ps.Name AS Name
, ps.SourceVersion
, ps.SourceDate
, ps.SourceSize
, ps.SourceCompressedSize
, ps.PackageID
SELECT DISTINCT v_TaskSequenceReferencesInfo.ReferencePackageID AS PackageID
FROM v_TaskSequenceReferencesInfo
WHERE v_TaskSequenceReferencesInfo.PackageID=@TaskSequenceID
) RefPkgs
JOIN v_PackageStatusRootSummarizer ps ON ps.PackageID = RefPkgs.PackageID
where RefPkgs.PackageID not in (SELECT v_Package.PackageID
FROM v_Package
INNER JOIN v_DistributionPoint ON v_Package.PackageID = v_DistributionPoint.PackageID
WHERE v_DistributionPoint.ServerNALPath LIKE '%' + @ID + '%')
ORDER BY ps.Name