Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2121
  • Last Modified:

SCCM SQL Query - List computers with multiple version of Java

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
Szuromi
Asked:
Szuromi
1 Solution
 
Kent DyerCommented:
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
 
SzuromiAuthor Commented:
Thanks.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now