Task Sequence and References packages distribution status on single Distribution Point.

Hello Community,

As there are lots of work/testing going around OSD/Windows 10 Migration; there is always a question from the service desk how can we see if all the references packages are distributed on respective Distribution Point. Sccm has a default report by the name “Content referenced by a specific task sequence” but in this report, you need to click on each reference package and see what’s the status of the DP.

so we thought let’s get a query created in SQL which will give this information quickly.

Here is the code which gives us a chance to pull out information from the sccm database. To get exact results you need to have  Task Sequence ID and Distribution Point Name on which you have distributed the Task Sequence.

SQL Query :

select
pkg.Name as [Package Name],
Case pkg.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'
Else ' '
END AS 'Package Type',
pkg.PackageID,
SUBSTRING(dp.ServerNALPath, CHARINDEX('\', dp.ServerNALPath) + 2, CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\', dp.ServerNALPath) - 3 ) AS [Server Name],
dp.SiteCode,
dp.LastRefreshTime,
stat.SourceVersion,
stat.LastCopied,
stat.SummaryDate,
stat.InstallStatus,
case when dp.IsPeerDP=1 then '*' else '' end as BranchDP
from v_Package pkg
join v_DistributionPoint dp on pkg.PackageID=dp.PackageID
join v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPath
and dp.PackageID=stat.PackageID
where stat.State!=0 and pkg.PackageID in (select ReferencePackageID from v_TaskSequenceReferencesInfo
where PackageID ='CAS***'  -- You Need to need to Mention Task Sequence Id here and SUBSTRING(dp.ServerNALPath, CHARINDEX('\',
dp.ServerNALPath) + 2, CHARINDEX('"]',   dp.ServerNALPath) - CHARINDEX('\',
dp.ServerNALPath) - 3 ) like '%DistributionPointName%')

Now Once you Distribute Task Sequence on a specific DP run this query and you will be able to fetch information

As the packages are distributed you will see the No of Results decreasing time by time and if you see no results means all the required Refrence packages are distributed.

This Blog is a shorter one, let me know if you want me to prepare a report and export it as a *.rdl file.

Happy Sharing!

— By Author Nawaz & Contributor Mamata