SQL Query for Missing Task Sequence Packages on a Distribution Point

[lord_hydrax]

I've seen a lot of SQL queries over the years for finding missing Task Sequence packages on a Distribution Point, and never got one to work.

All I wanted to do was query a server name and a task sequence package ID to find out what applications or packages referenced by the TS still need to be distributed.

I finally actually got one to work (SQL isn't my strongest area ) so I thought I'd share with everyone:

SELECT DISTINCT TSR.PackageID
,PS.PackageID
,PSD.Name
 
FROM v_TaskSequencePackageReferences AS TSR
 
JOIN v_PackageStatus AS PS ON TSR.RefPackageID = PS.PackageID
JOIN v_PackageStatusDetailSumm AS PSD ON TSR.RefPackageID = PSD.PackageID
 
WHERE TSR.PackageID = '<TSPACKAGEID>' AND PS.PackageID NOT IN (SELECT
PS.PackageID FROM v_PackageStatus AS PS 
JOIN v_TaskSequencePackageReferences AS TSR ON PS.PackageID = TSR.RefPackageID 
WHERE PS.PkgServer LIKE '%<SERVERNAME>%')

Replace <TSPACKAGEID> with the Task Sequence's unique Package ID and <SERVERNAME> with the hostname of the Distribution Point you want to query.

Also anyone who is familar with the 'Create Task Sequence Media' wizard, you can use that to check what servers are missing packages as you get a listing of servers and the number of packages from the TS that it has. Good starting point to find DPs that have missing packages.

[lord_hydrax]

Well I've just found my own post googling for this information today.... At least I've helped someone 

[pwstrain]

So I'm trying this query. It indicates all packages in the TS are distributed to the correct DP. However the TS Media wizard still insists that one package is missing. I've even selected the TS and don a 'Distribute Content' to the DP. Going to check all versions on the Applications that are referenced. Ugh.