Count Software by Prompting for Display Name

Version: SCCM 2012 SP1 CU2

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

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

For Workstations:

DECLARE @varSoftware nvarchar(20)
SET @varSoftware = 'DisplayName'

SELECT arp32.DisplayName0, 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.DisplayName0 like @varSoftware) and sys.Operating_System_Name_and0 like '%workstation%'
GROUP BY DisplayName0, Version0
UNION
SELECT arp64.DisplayName0, 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.DisplayName0 like @varSoftware) and sys.Operating_System_Name_and0 like '%workstation%'
GROUP BY DisplayName0, Version0
ORDER BY DisplayName0, Version0

For Servers:

DECLARE @varSoftware nvarchar(20)
SET @varSoftware = 'DisplayName'

SELECT arp32.DisplayName0, 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.DisplayName0 like @varSoftware) and sys.Operating_System_Name_and0 like '%server%'
GROUP BY DisplayName0, Version0
UNION
SELECT arp64.DisplayName0, 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.DisplayName0 like @varSoftware) and sys.Operating_System_Name_and0 like '%server%'
GROUP BY DisplayName0, Version0
ORDER BY DisplayName0, Version0

This report will display software title, 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 @varSoftware could be the following:

  • ‘Java%’ – show me everything that begins with the word ‘Java’.
  • ‘%Office%’ – show me anything that has the word ‘Office’ in the title.
  • ‘Adobe Air’ – show me all that have ‘Adobe Air’ as an exact match.

Sample Output using ‘%java%’:

Count Software by Prompting for Display Name

Count Software by Prompting for Display Name