Tag Archive: package

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

SCCM Report / SQL Query – Show packages with no deployments or task sequences linked

General maintenance and housekeeping of your SCCM 2012 environment is important to keep your environment healthy.  Packages and applications can fill up drive space quickly if they are not managed and maintained.  This query for SCCM 2012 will display a list of packages that is not being deployed and are not linked to any task sequences.  Generally this means they are not being used and may be retired.

SELECT pkg.Name as [Package Name], pkg.Version, pkg.PackageID,
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’
END AS [Package Type], adv.AdvertisementID, tsp.Name as [TS Name]
FROM v_Package pkg
LEFT JOIN v_Advertisement adv on pkg.PackageID=adv.PackageID
LEFT JOIN v_TaskSequencePackageReferences tsr on pkg.PackageID=tsr.RefPackageID
LEFT JOIN v_TaskSequencePackage tsp on tsr.PackageID=tsp.PackageID
WHERE adv.AdvertisementID is null
AND tsp.Name is null
ORDER BY pkg.Name

This query will return information for all packages regardless of their package type, but you will notice that I added values for each package type so that you can differentiate between them.  Package type 5 for Software Updates never have deployments and task sequences associated to them so you can filter these out in your query.  Remember that in Software Updates you will deploy a Software Update Group to your devices and not a package.  It is however included in this query for reference.