Create custom Report with SCCM Package Size Details

3

Hi – If you are looking for a package list of a DP along with Package Size then we can use the below report.

SELECT p.Name, p.Description, n.SourceCompressedSize, n.SourceSize, dp.LastRefreshTime, p.Manufacturer, p.Version, p.Language, p.SourceSite, p.PackageID, case when dp.IsPeerDP=1 then ‘*’ else ” end as BranchDP, psd.InstallStatus
FROM v_Package p
INNER JOIN
v_DistributionPoint dp
ON
p.PackageID = dp.PackageID
LEFT JOIN
v_PackageStatusRootSummarizer n
ON
p.PackageID = n.PackageID
LEFT JOIN
v_PackageStatusDistPointsSumm psd
ON
dp.ServerNALPath=psd.ServerNALPath
AND
dp.PackageID=psd.PackageID
LEFT JOIN
v_PackageStatus ps
ON
dp.ServerNALPath=ps.PkgServer
AND
dp.PackageID=ps.PackageID
WHERE
dp.ServerNALPath LIKE ‘%’ + @ID + ‘%’

PROMPT
========

begin
if (@__filterwildcard = ”)
SELECT DISTINCT SUBSTRING(ServerNALPath, CHARINDEX(‘\\’, ServerNALPath) + 2, CHARINDEX(‘”]’, ServerNALPath) – CHARINDEX(‘\\’, ServerNALPath) – 3 ) AS C070 FROM v_DistributionPoint
else
SELECT DISTINCT SUBSTRING(ServerNALPath, CHARINDEX(‘\\’, ServerNALPath) + 2, CHARINDEX(‘”]’, ServerNALPath) – CHARINDEX(‘\\’, ServerNALPath) – 3 ) AS C070 FROM v_DistributionPoint
WHERE SUBSTRING(ServerNALPath, CHARINDEX(‘\\’, ServerNALPath) + 2, CHARINDEX(‘”]’, ServerNALPath) – CHARINDEX(‘\\’, ServerNALPath) – 3 ) like @__filterwildcard
end

3 COMMENTS

  1. Hi, Thanks for sharing this but I can't make this to work. I get the following error:

    Msg 137, Level 15, State 2, Line 24
    Must declare the scalar variable “@ID”.
    Msg 137, Level 15, State 2, Line 30
    Must declare the scalar variable “@__filterwildcard”.
    Msg 156, Level 15, State 1, Line 32
    Incorrect syntax near the keyword 'else'.
    Msg 137, Level 15, State 2, Line 34
    Must declare the scalar variable “@__filterwildcard”.

  2. Hi Anoop, Thanks for sharing the query. I am also getting the same error,
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘‘’.
    Msg 137, Level 15, State 2, Line 30
    Must declare the scalar variable “@__filterwildcard”.
    Msg 102, Level 15, State 1, Line 31
    Incorrect syntax near ‘‘’.
    Msg 102, Level 15, State 1, Line 33
    Incorrect syntax near ‘‘’.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.