Tag Archive: package source size

SCCM Report / SQL Query – All active content distributions

SCCM has a built-in report showing all active content distributions.  This report is very useful to track content distributions still in progress.  I just added in a few extra fields to show me information about the package type, size of the source files and the percentage completed where possible.

I have included a commented out section at the top to declare a value for the Distribution Point name.  Just remove the comment characters from the top and the WHERE clause if you want to filter your report for a specific Distribution Point.

–DECLARE @DP nvarchar(50)
–SET @DP = <DP Server Name>

SELECT
pkg.PackageID,
pkg.Name AS ‘Package Name’,
CASE
WHEN pkg.PackageType = 0 THEN ‘Software Distribution Package’
WHEN pkg.PackageType = 3 THEN ‘Driver Package’
WHEN pkg.PackageType = 4 THEN ‘Task Sequence Package’
WHEN pkg.PackageType = 5 THEN ‘Software Update Package’
WHEN pkg.PackageType = 6 THEN ‘Device Setting Package’
WHEN pkg.PackageType = 7 THEN ‘Virtual Package’
WHEN pkg.PackageType = 8 THEN ‘Application’
WHEN pkg.PackageType = 257 THEN ‘Image Package’
WHEN pkg.PackageType = 258 THEN ‘Boot Image Package’
WHEN pkg.PackageType = 259 THEN ‘Operating System Install Package’
Else ‘Unknown’
End AS ‘Package Type’,
LEFT(SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) – CHARINDEX(‘\\’,
dp.ServerNALPath) – 3 ),CHARINDEX(‘.’,SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) –
CHARINDEX(‘\\’, dp.ServerNALPath) – 3 ))-1) AS ‘DP’,
dp.SiteCode,
dp.LastRefreshTime,
stat.SourceVersion,
‘SourceSize(MB)’ = prs.SourceSize/1024,
stat.LastCopied,
stat.SummaryDate,
(
SELECT TOP 1 msg.InsString3
FROM
v_StatMsgWithInsStrings msg
JOIN v_StatMsgModuleNames modNames ON msg.ModuleName = modNames.ModuleName
JOIN v_StatMsgAttributes attpkg ON msg.RecordID=attpkg.RecordID AND msg.Time=attpkg.AttributeTime
JOIN v_StatMsgAttributes attdp ON msg.RecordID=attdp.RecordID AND msg.Time=attdp.AttributeTime
WHERE
attpkg.AttributeValue =pkg.PackageID
AND msg.MessageID=’8204′
AND msg.InsString2 = SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) –
CHARINDEX(‘\\’, dp.ServerNALPath) – 3 )
ORDER BY msg.Time DESC) AS ‘% Completed’,
stat.InstallStatus
FROM
v_Package pkg
LEFT JOIN v_PackageStatusRootSummarizer prs ON pkg.PackageID = prs.PackageID
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 LEFT(SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) – CHARINDEX(‘\\’,
dp.ServerNALPath) – 3 ),CHARINDEX(‘.’,SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) –
CHARINDEX(‘\\’, dp.ServerNALPath) – 3 ))-1) = @DP
ORDER BY
pkg.Name, dp.SiteCode