widsnet.com
24Jan/130

Count number of client with specified program in a collection

Enter a program name, ID or publisher and a collectionID and the report will list the number of clients with the specified program installed.

SELECT arp.Publisher0, arp.DisplayName0, arp.ProdID0, Count(DISTINCT sys.Name0) AS 'Count'
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID 
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE (arp.DisplayName0 LIKE @SearchKey OR arp.Publisher0 LIKE @SearchKey OR arp.ProdID0 LIKE @SearchKey) 
AND fcm.CollectionID=@CollID
GROUP BY arp.Publisher0, arp.DisplayName0, arp.ProdID0
ORDER BY arp.DisplayName0 ASC

Prompts
Name: SearchKey
Prompt text: Software / Publisher / Product ID

begin
 if (@__filterwildcard = '')
  Select DISTINCT DisplayName0 FROM v_Add_Remove_Programs order by DisplayName0
 else
  Select DISTINCT DisplayName0 FROM v_Add_Remove_Programs
  WHERE DisplayName0 like @__filterwildcard
  order by DisplayName0
end

Name: CollID
Prompt text: CollectionID

begin
 if (@__filterwildcard = '')
  select CollectionID, Name from v_Collection order by Name
 else
  select CollectionID, Name from v_Collection
  WHERE Name like @__filterwildcard
  order by Name
end
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.