if you are working on the Java upgrade using SCCM Configmgr, you will have to cautious to apply the correct version of Java on the existing Client (can be desktop or server) to make sure the dependent applications works. For this to happen,you need to identify the clients that have JAVA installed (can be 32bit or 64bit) and deploy the Java application to the respective collection.
Inventory that comes from the client add and remove programs is stored in 2 locations in WMI SMS_G_System_ADD_REMOVE_PROGRAMS and SMS_G_System_ADD_REMOVE_PROGRAMS_64. You will have to query one of these as per the needs.
If you are using SCUP or third party patching ,you can simply create software update group and deploy it to collection (without any JAVA filter ) and client go through the software update scan and install the required updates.
In this blog post , I posted 2 Collections to list all clients in your infra that are managed by SCCM that are less than Java 8 Update 92 (is the latest version at the time of writing this blog post) using sub Selected Queries. Sub selected query used to get list of All JAVA Version clients and exclude them from Clients that are with Java 8 update 92.
Create new collection ,Limit the collection ,select Query based ,edit and past the following the query for 32bit and 64bit Java Versions.
SCCM Collection for Java < JRE 1.8 Update 92_X86:
Both the Collection Queries excludes the windows server 2003 if you have any for safer side due to EOL for server 2003 .
If you wanted to get the clients that are lower version as per your need, make changes to the bold letters in the query below.
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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 like "Java%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Java %SE Development%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Java(TM) %SE Development%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Java %SDK%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Java%Updater%" and SMS_G_System_OPERATING_SYSTEM.Caption not like "%2003%" and SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID not in (select resourceID 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 8 Update 92")
SCCM Collection for Java < JRE 1.8 Update 92_X64:
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_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "Java%" and SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName not like "Java %SE Development%" and SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName not like "Java(TM) %SE Development%" and SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName not like "Java %SDK%" and SMS_G_System_OPERATING_SYSTEM.Caption not like "%2003%" and SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID not in (select resourceid from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Java 8 Update 92 (64-bit)")
Hi Eswar, Iam looking for sccm query for Java installed and unused last 90 days. Can provide that
you can fetch the device list for java installed but for unused data, you need to create a metering usage report or something like this https://www.systemcenterdudes.com/sccm-java-inventory-and-metering/
I created a collection with just the statements you have provided into my environment, and limited them by All Systems, so that I reach every workstation that has a client running.
I would like to target a specific version of java and create a collection of just that version, so I feel like I am missing a step to do this process like you have described. I am instead seeing every installation of java with a version that is (potentially) newer than the version I am targeting.
if that is the case, you may have to create collections based on what you want to install. If you using scup managing 3rd party upadtes, this job would be lot more easier than managing using application deployment.
This is going to be a VERY expensive SQL transaction and depending on the collection update frequency, could significantly impact SQL/console performance during the updates.
I understand the need for collections like these but consider removing the LIKE/NOT LIKE statements and replace with explicit EQUALS/DOES NOT EQUAL instead. Far less impactful to SQL.
Just food for thought.
I Concur with you ,if you use LIKE condition in Queries ,it may affect (depends on the number of clients support) the console and will have impact on the database as well but , if i do not use LIKE ,i may not get correct results in my case what i am expecting .For ex: you have clients in infra that are with different versions of Java installed like Java 6 update 55,Java 7,java 8,java development etc and i use explicit conditions what you suggested ,i may not get correct results . My collection should contains only clients that are with JAVA installed but should not be the version that am installing and exclude Terms that contains JAVA Development,JAVA updater etc.
Probably, we can use Sub selected query to get all JAVA Clients and resourceID should not be in 'JAVA 8 update 92' in this case ,this again go back to original Query.
I think i can remove couple of LIKE conditions by eliminating javaupdater,liveupate to make this query works little faster