One of my Customers asked me to create a customized report for his environment. A standard SCCM report for Software registered in Add or Removed Programs for a specific collection fit perfectly as a base, but I wanted to replace Collection information in the report (originally it is Collection ID, but I guessed the Customer would prefer a Collection Name).
My knowledge of SQL reporting is limited, but with help of Sherry Kissinger I ended up with the following result:
SELECT arp.DisplayName0, Count(Distinct arp.ResourceID) AS ‘Count’, arp.Publisher0, arp.Version0, col.collectionid, COL.Name as CollectionName
FROM fn_rbac_Add_Remove_Programs(@UserSIDs) arp
JOIN fn_rbac_FullCollectionMembership(@UserSIDs) fcm on arp.ResourceID=fcm.ResourceID
JOIN v_Collection COL ON fcm.CollectionID = COL.CollectionID
WHERE fcm.CollectionID = @CollID
GROUP BY DisplayName0, Publisher0, Version0, col.collectionid, col.name
ORDER BY Publisher0, Version0
That gave me the report I looked for (of course Collection ID column can ber removed now and some other will be added):
Like this:
Like Loading...
Related
