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.
— By Author Nawaz & Contributor Mamata