Count Software by Prompting for Publisher Name

Version: SCCM 2012 SP1 CU2

There may be times when you are asked to create a static report to count software from a specific publisher on workstations, servers, or both. You can create a dynamic report that will accept as input the publisher name. The following are SQL query examples to display software by publisher using wildcards if necessary.

If you are running this in SQL Management Studio, either declare and set the @ParamPublisher variable or you can put the publisher name directly in the where section. If you will use SSRS, then this will serve as a parameter for your report.

For Workstations:

DECLARE @ParamPublisher nvarchar(15)
SET @ParamPublisher = 'Publisher_Name'

SELECT arp32.DisplayName0, arp32.Publisher0, arp32.Version0, '32-bit' as 'Bitness', Count(Distinct sys.ResourceID) AS 'Count'
FROM v_GS_ADD_REMOVE_PROGRAMS arp32
JOIN v_R_System sys on arp32.ResourceID=sys.ResourceID
WHERE (arp32.Publisher0 like @ParamPublisher) and sys.Operating_System_Name_and0 like '%workstation%'
GROUP BY DisplayName0, Publisher0, Version0
UNION
SELECT arp64.DisplayName0, arp64.publisher0, arp64.Version0, '64-bit' as 'Bitness', Count(Distinct sys.ResourceID) AS 'Count'
FROM v_GS_ADD_REMOVE_PROGRAMS_64 arp64
JOIN v_R_System sys on arp64.ResourceID=sys.ResourceID
WHERE (arp64.publisher0 like @ParamPublisher) and sys.Operating_System_Name_and0 like '%workstation%'
GROUP BY DisplayName0, Publisher0, Version0
ORDER BY Publisher0, DisplayName0, Version0

For Servers:

DECLARE @ParamPublisher nvarchar(15)
SET @ParamPublisher = 'Publisher_Name'

SELECT arp32.DisplayName0, arp32.Publisher0, arp32.Version0, '32-bit' as 'Bitness', Count(Distinct sys.ResourceID) AS 'Count'
FROM v_GS_ADD_REMOVE_PROGRAMS arp32
JOIN v_R_System sys on arp32.ResourceID=sys.ResourceID
WHERE (arp32.Publisher0 like @ParamPublisher) and sys.Operating_System_Name_and0 like '%server%'
GROUP BY DisplayName0, Publisher0, Version0
UNION
SELECT arp64.DisplayName0, arp64.publisher0, arp64.Version0, '64-bit' as 'Bitness', Count(Distinct sys.ResourceID) AS 'Count'
FROM v_GS_ADD_REMOVE_PROGRAMS_64 arp64
JOIN v_R_System sys on arp64.ResourceID=sys.ResourceID
WHERE (arp64.publisher0 like @ParamPublisher) and sys.Operating_System_Name_and0 like '%server%'
GROUP BY DisplayName0, Publisher0, Version0
ORDER BY Publisher0, DisplayName0, Version0

This report will display software title, publisher, version, application bitness, and count. If you need a report that shows both workstations and servers, simply remove the ‘sys.Operating_System_Name_and0’ from each ‘where’ clause. Note, each query has a pair of ‘where’ clauses for both 32-bit and 64-bit.

Some examples for @ParamPublisher could be the following:

  • ‘Oracle%’ – show me publishers that begins with the word ‘Oracle’.
  • ‘%Adobe%’ – show me publishers that contain the word ‘Adobe’ in the publisher name.
  • ‘Microsoft’ – show me all that have ‘Microsoft’ as an exact match.

Sample Output using ‘Oracle%’:

Count Software by Prompting for Publisher Name

Count Software by Prompting for Publisher Name