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 default report by 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 on 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 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 :
pkg.Name as [Package Name],
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',
SUBSTRING(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath) + 2, CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath) - 3 ) AS [Server Name],
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
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 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 shorter one, let me know if you want me to prepare report and export it as *.rdl file.
-- By Author Nawaz & Contributor Mamata