Hey Michael, it sounds like you are currently using LDMS queries to try to gather this information.
Have you tried making products in SLM for this? SLM allows you to create criteria for what makes up a product and can sometimes get you closer than simply using LDMS queries.
Here is the main page for SLM if you are not already familiar with it.
Let us know if that helps
I had a good friend of mine help me with this from a SQL point of view, and he came up with the SQL below.
It's not perfect, but it will point you in the right direction. It will query for all instances of the 4 JRE related files found in their default paths. It will then group them based on the path, get the version of java.exe in that path, combine the total "Times run" values for all 4 files in the path and display the latest "Last run" value for the 4 files to show when JRE was last used.
with temp as (SELECT DISTINCT A0.DISPLAYNAME, A0.TYPE, A2.OSTYPE, A1.PATH, A1.VERSION, A1.SCM_SESSIONCOUNT, A1.SCM_LASTSESSIONSTART FROM Computer A0 (nolock) LEFT OUTER JOIN AppSoftware A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn LEFT OUTER JOIN Operating_System A2 (nolock) ON A0.Computer_Idn = A2.Computer_Idn WHERE ((A1.FILENAME = N'java.exe' OR A1.FILENAME = N'javaw.exe' OR A1.FILENAME = N'javaws.exe' OR A1.FILENAME = N'jp2launcher.exe') --AND A0.TYPE LIKE '%server%' --Servers AND A0.TYPE NOT LIKE '%server%' --Workstations AND (A1.PATH LIKE N'%c:\Program Files\Java\JRE%' OR A1.PATH LIKE N'%c:\Program Files\Java\J2RE%' OR A1.PATH LIKE N'%c:\Program Files (x86)\Java\JRE%' OR A1.PATH LIKE N'%c:\Program Files (x86)\Java\J2RE%')) ) SELECT [DISPLAYNAME] , [TYPE] , [OSTYPE] , [SHORTPATH] , (SELECT TOP 1 [VERSION] FROM temp WHERE LEFT([PATH], LEN(B.[SHORTPATH]))=B.[SHORTPATH] AND [DISPLAYNAME]=B.[DISPLAYNAME] AND [PATH] LIKE '%java.exe') AS [VERSION] , [SCM_SESSIONCOUNT] , [SCM_LASTSESSIONSTART] FROM ( SELECT MAX([DISPLAYNAME]) AS [DISPLAYNAME] , MAX([TYPE]) AS [TYPE] , MAX([OSTYPE]) AS [OSTYPE] , SUM([SCM_SESSIONCOUNT]) AS [SCM_SESSIONCOUNT] , MAX([SCM_LASTSESSIONSTART]) AS [SCM_LASTSESSIONSTART] , [SHORTPATH] FROM (SELECT [DISPLAYNAME] , [TYPE] , [OSTYPE] , ISNULL([VERSION], '') AS [VERSION] , ISNULL([SCM_SESSIONCOUNT], '') AS [SCM_SESSIONCOUNT] , ISNULL([SCM_LASTSESSIONSTART], 0) AS [SCM_LASTSESSIONSTART] , ISNULL([PATH], '') AS [PATH] , REVERSE( SUBSTRING( REVERSE([PATH]), CHARINDEX('\', REVERSE([PATH])), LEN([PATH]) ) ) AS [SHORTPATH] FROM temp) AS [A] GROUP BY [DISPLAYNAME], [SHORTPATH]) [B] ORDER BY [DISPLAYNAME], [SHORTPATH], [VERSION]
As mentioned, not perfect, but still usable.
CAUTION: Use at own risk - script provided "as is" with no guaranties! It's a heavy query for large installations!