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


If you have same application with multiple versions installed on machines for ex: JAVA ,it allow to have multiple versions with same name like JAVA 6 update 45,JAVA 7 update 65 and so on….

How do you find computers that have single version application installed and exclude computers that have multiple versions with same name Installed ?

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

Example : I have client A,B and C out of which ,A and B has 3 versions of JAVA installed where as C is installed with One Version of JAVA and want get PC C into the Query results.

We will get this done using concept called Subselect query. Full Details

SQL Code:

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





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

You can use this query to create nice SSRS Report and prompt for specific application so you can get information for any application.

Leave a Reply