There was a request from twitter friend who is trying to create SQL report to list the content of distribution point group with content status such as total targeted, installed, progress and errors.
Distribution point groups is available in configuration manager for almost several years . This basically provide a logical grouping of distribution points for content distribution.
we can create and use DP groups groups to manage and monitor content from a central location for distribution points that span multiple sites.
For more information about managing distribution point and distribution point groups, please refer https://docs.microsoft.com/en-us/configmgr/core/servers/deploy/configure/install-and-configure-distribution-points
When you create DP group and add content to it, you see the content listed in the content properties as shown in the screenshot.
If you want to monitor the status of package or content on DP, you can use the default reports.
There are set of reports available with category Software Distribution – Content but there is nothing to monitor the content status of DP group.
The following SQL code help you to provide the summary of content on specific DP with targeted, installed, in progress and failure.
SELECT DISTINCT dpgr.NAME [DP Group],
pk.NAME [Package Name],
dgp.pkgid [Package ID],
dpcn.targeteddpcount,
dpcn.numberinstalled,
dpcn.numberinprogress,
dpcn.numbererrors,
CASE
WHEN pk.packagetype = 0 THEN 'Software Distribution Package'
WHEN pk.packagetype = 3 THEN 'Driver Package'
WHEN pk.packagetype = 4 THEN 'Task Sequence Package'
WHEN pk.packagetype = 5 THEN 'Software Update Package'
WHEN pk.packagetype = 6 THEN 'Device Setting Package'
WHEN pk.packagetype = 7 THEN 'Virtual Package'
WHEN pk.packagetype = 8 THEN 'Application'
WHEN pk.packagetype = 257 THEN 'Image Package'
WHEN pk.packagetype = 258 THEN 'Boot Image Package'
WHEN pk.packagetype = 259 THEN 'Operating System Install Package'
ELSE 'Unknown'
END AS 'Package Type'
FROM vsms_dpgroupinfo dpgr
INNER JOIN v_dpgrouppackages dgp
ON dgp.groupid = dpgr.groupid
LEFT JOIN v_package pk
ON pk.packageid = dgp.pkgid
LEFT JOIN v_dpgroupcontentdetails dpcn
ON dpcn.groupid = dpgr.groupid
AND dpcn.pkgid = pk.packageid
WHERE dpgr.NAME = 'Azure DP'
Replace the distribution point group name.
6 Comments
Why do we use group memberships linked to distribution point groups? or why do we have group memberships ?
Group membership or distribution groups are super useful when you want to distribute the content to one DP group where all DP's part of it.
Assume that, you have set up a new DP in your existing infra, and to distribute the content to this new DP, you need to go and distribute the apps, packages, and lot more and that takes alot of time.
if the content is already target to the DP group, all you need is to simply add the new DP to the GP group where content is already targeted and you are done.
Thanks,
Eswar
Very good working.
Hi, IDGroup in the table v_DistributionPointInfo does not exist and the table vsms_dpgroupinfo it's empty.
Hi,
What version of configuration manager are you using?
I can see the groupID info exist in vsms_dpgroupinfo table.
Thanks,
Eswar
Thank u sir,