Solved

SCCM SQL Query - List computers with multiple version of Java

Posted on 2013-01-06

1 solution

2,002 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

Comment

Question by:Szuromi

[X]

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

Join The Community

2 Comments

LVL 17

Accepted Solution

by:Kent Dyer

Kent Dyer earned 250 total points

ID: 387495842013-01-06

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

Author Closing Comment

by:Szuromi

ID: 389037552013-02-18

Thanks.

Featured Post

Question has a verified solution.

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

What would data loss cost your business?

Article by: Christian

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.

Superb Internet
IT Administration
Ransomware
Storage Software
Databases
Cloud Computing, Cloud Services, *Backups, *Online Backup Services, *Backup Software

A guide to IT post-mortem reporting

Article by: Shawn

Postmortem reporting allow us to examine mistakes in a way that focuses on the situational aspects of a failure’s mechanism and the decision-making process of individuals proximate to the failure. Read our guide on how to handle IT post-mortem repor…

Connecting To SQL Server From Oracle Using Heterogeneous Services Generic Gateway

Video by: Steve

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

Executing a SQL Script from a Unix Shell Script and Passing Parameters

Video by: Steve

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.