SCCM Configmgr SQL Report get list of machines with one Version of application and exclude other versions

I had requirement from team to find out the clients that have one application (example Java) with one version installed and exclude clients that have multiple versions with same application installed.

Lets take simple example . I have clients CLN01,CLN02,CLN03 and CLN04 and they have different versions of Java application installed on them.

PC Name Application Version
CLN01 Java 7 Update 45 8.0.005
CLN01 Java(TM) 6 Update 37 6.0.370
CLN01 Java 7 Update 80 7.0.450
CLN02 Java 7 Update 45 8.0.005
CLN02 Java(TM) 6 Update 37 6.0.370
CLN03 Java 7 Update 80 7.0.450
CLN04 Java 7 Update 45 8.0.005

Now i want to get list of clients that have one version of java installed ,2 versions of Java and 3 versions of Java installed.

If i want one version of Java ,it should only list CLN03 and CLN04 since these 2 clients are installed with only 1 application version,

If i want Java application with 2 different versions installed, it should only list CLN02 and for 3 different versions ,it should only list CLN01.

This scenario can be applicable to other applications (like Microsoft Office,adobe etc)  that allow multiple versions on same computer .

How to get this results using SQL query ?

We will get this done using Subselect query. Full Details https://social.technet.microsoft.com/Forums/en-US/a1d013ac-34fc-4486-9747-56e3d0027d9f/softwareinventory-query?forum=configmanagergeneral#108b932b-e91c-4b09-8abf-7fbf5701c588

SQL Code:

select sub.name0,arp1.DisplayName0,arp1.Version0

from

(

select

vrs.Name0,vrs.ResourceID,

COUNT(*) Total

from v_Add_Remove_Programs ARP

inner join v_R_System vrs on ARP.ResourceID = VRS.ResourceID

where arp.DisplayName0 like 'Java%'

group by vrs.Name0,vrs.ResourceID

having count(vrs.Name0)=1 )Sub

inner join v_Add_Remove_Programs arp1 on arp1.resourceid=sub.ResourceID

where arp1.DisplayName0 like 'Java%'

order by sub.name0,arp1.DisplayName0,arp1.Version0

This query list all clients that have only one version of Java application installed.

If you want to list clients that have 2 versions of Java application installed ,replace the count having count(vrs.Name0)=2 and for 3 versions ,simply change the value to 3 and so on...

You can also use this query to create nice SSRS report and make this count value as dynamic instead of static.

Leave a Reply