Solved

SCCM SQL Query - List computers with multiple version of Java

Posted on 2013-01-06
2
1,824 Views
Last Modified: 2013-02-18
Hi Everybody,

I plan to uninstall old version of Java (6U37) if computer has both version of Java (6U37 and 7U10). I created the following SQL statement for my collection on SCCM…
select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join     SMS_G_System_ADD_REMOVE_PROGRAMS on     SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId     where (SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Java 7 Update 10")   or
(SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Java(TM) 6 Update 37")

This shows all the computer which has one or the other version of Java but I cannot make it work to show only computer(s) which has both version of Java installed.

Can anybody to help with this SQL statement? Any help would be greatly appreciated?
Thanks.

Emese
0
Comment
Question by:Szuromi
2 Comments
 
LVL 17

Accepted Solution

by:
Kent Dyer earned 250 total points
ID: 38749584
Something like this should do it..  You will probably need to do some tweaking/changes, but you should get the idea.
SELECT sms_r_system.resourceid,
       sms_r_system.resourcetype,
       sms_r_system.name,
       sms_r_system.smsuniqueidentifier,
       sms_r_system.resourcedomainorworkgroup,
       sms_r_system.client.
Count(sms_g_system_add_remove_programs.displayname) as count_of_JAVA
FROM   sms_r_system
       INNER JOIN sms_g_system_add_remove_programs
               ON sms_g_system_add_remove_programs.resourceid =
                  sms_r_system.resourceid
WHERE  ( sms_g_system_add_remove_programs.displayname = "java 7 update 10" )
        OR ( sms_g_system_add_remove_programs.displayname =
             "java(tm) 6 update 37" )
Having COUNT(sms_g_system_add_remove_programs.displayname) > 1

Open in new window

HTH,

Kent
0
 

Author Closing Comment

by:Szuromi
ID: 38903755
Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Learn how ViaSat reduced average response times for IT incidents from 10 minutes to 30 seconds.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question