Brett Wilms at myITforum.com

The SMS 2003 web report query below returns the list of Distribution Points for a specific package, separated by "Installed" and "Not Installed".  I created this report to help me understand what Distribution Points were missing particular packages.

SELECT 'INSTALLED'
SELECT  pkg.Name,
                pkg.PackageID,
                SUBSTRING(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath) + 2, CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath) - 3 ) as C070,
                 dp.SiteCode
FROM v_Package pkg
LEFT OUTER JOIN v_DistributionPoint dp ON pkg.PackageID = dp.PackageID
WHERE pkg.PackageID = @PackageID
ORDER by C070

SELECT 'NOT INSTALLED'
SELECT distinct SUBSTRING(ServerNALPath, CHARINDEX('\\', ServerNALPath) + 2, CHARINDEX('"]', ServerNALPath) - CHARINDEX('\\', ServerNALPath) - 3 ) as C070,
               v_DistributionPoint.SiteCode
FROM v_Package
LEFT OUTER JOIN v_DistributionPoint ON v_Package.PackageID = v_DistributionPoint.PackageID
WHERE v_DistributionPoint.ServerNALPath NOT IN (SELECT v_DistributionPoint.ServerNALPath FROM v_DistributionPoint WHERE v_DistributionPoint.PackageID = @PackageID) and v_DistributionPoint.ServerNALPath IS NOT NULL
ORDER by C070

Copy and paste the above query into SMS web reporting, remembering to create a prompt.  Or simply download the attached TXT file, rename it to a MOF and import it into SMS Reporting.

All distribution points for a specific package (custom).mof.txt