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.