Software Distribution Reports

I have 85 or so DPs across more than 5 Primary sites and sometimes I see failures in newly created Task Sequences (or even old ones) during an OS deployment due to a package not being available.

• Distribution status of a specific package on a specific distribution point – This report shows the installation status of a package on the specified distribution point.
• Distribution points without a specific package – This report shows any Distribution Points a Package is not installed on. N.B. Package MUST be targeted to the site in order to be detected
• Distribution points without a specific package in a site – This report shows any Distribution Points a Package is not installed on in the specified site. N.B. Package MUST be targeted to the site in order to be detected
• Distribution status of a specific package in a site – This report shows the installation status of a package on each of its distribution points in the specified site

Prompt Queries:

@compname

begin
if (@__filterwildcard = '')
 SELECT DISTINCT SYS.Netbios_Name0
 FROM v_R_System SYS
 ORDER By SYS.Netbios_Name0
 else
 SELECT DISTINCT SYS.Netbios_Name0
 FROM v_R_System SYS
 WHERE SYS.Netbios_Name0 like @__filterwildcard
 ORDER By SYS.Netbios_Name0
end

@DP

begin
 if (@__filterwildcard = '')
 SELECT DISTINCT
 SUBSTRING(ServerNALPath, CHARINDEX('\\', ServerNALPath) + 2, CHARINDEX('"]', ServerNALPath) - CHARINDEX('\\', ServerNALPath) - 3 ) AS C070
 FROM v_DistributionPoint
 ORDER BY C070
 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
 ORDER BY C070
end

@PkgID

begin
 if (@__filterwildcard = '')
 SELECT DISTINCT PackageID, Name AS C062
 FROM v_Package
 ORDER BY Name
 else
 SELECT DISTINCT PackageID, Name AS C062
 FROM v_Package
 WHERE PackageID like @__filterwildcard
 order by Name
end

@scode

begin
 if (@__filterwildcard = '')
 SELECT DISTINCT SiteCode, SiteName AS C062
 FROM v_DistributionPoint
 ORDER BY SiteCode
 else
 SELECT DISTINCT SiteCode, SiteName AS C062
 FROM v_DistributionPoint
 WHERE SiteName like @__filterwildcard
 order by SiteCode
end

Reports:

• Distribution status of a specific package in a site.sql (513) – Requires prompts @PkgID & @scode
• Distribution status of a specific package on a specific distribution point.sql (518) – Requires prompts @PkgID & @DP
• Distribution points without a specific package.sql (425) – Requires prompts @PkgID & @DP
• Distribution points without a specific package in a site.sql (415) – Requires prompts @PkgID & @scode