SCCM SQL Query - List computers with multiple version of Java

Posted on 2013-01-06
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…

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?

Question by:Szuromi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 17

Accepted Solution

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,
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 =
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



Author Closing Comment

ID: 38903755

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

IT certifications are a concrete representation of continual learning on the part of the candidate.  Continual learning is necessary for the long term success of an IT professional, but are IT certifications the right path for you?
We asked our MSP customer base what their favorite tools were and how they help them serve clients. We focused our questions on favorite tools in the following categories: >PSA tools >RMM tools >Alert management tools >Communication tools and Mo…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 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