Count of instances of specific software registered with Add or Remove Programs‏ – Wrong counts SCCM 2012

The problem with the query is that there can be multiple ProdID’s for the same DisplayName0, Publisher0, Version0 returned. So it counts them all, skewing the expected result. I fixed this by changing Count(ProdID0) to Count(Distinct arp.ResourceID) in the dataset…

SELECT DisplayName0, COUNT(DISTINCT arp.ResourceID) AS 'Count', Publisher0, Version0, @CollID AS CollectionID 
FROM fn_rbac_Add_Remove_Programs(@UserSIDs)  arp 
JOIN fn_rbac_FullCollectionMembership(@UserSIDs)  fcm ON arp.ResourceID=fcm.ResourceID 
WHERE ((Publisher0 LIKE @softwarenamefilter) OR (DisplayName0 LIKE @softwarenamefilter) ) 
AND fcm.CollectionID=@CollID 
GROUP BY DisplayName0, Publisher0, Version0  
ORDER BY Publisher0, Version0
This entry was posted in Configuration Manager, Microsoft, System Center 2012.

Leave a Reply