→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
GMFDE
Engaged Sweeper III
Sorry, I had to use that subject line because there is an unanswered post from someone else with the subject line I wanted to use and this forum software is set to prevent duplicate posts. Silly really.

To the point. I have been working on a report to find systems with more than one version of Java. It seemed straight forward but isn't working. The report returns 0 results. However, I know from our SCCM environment that we have several hundred systems that qualify. Mgmt has a penchant for LS reporting and doesn't like that they can't filter on the fly in SCCM as they can with LS.

Below is what I have so far...

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblSoftwareUni.softwareName Like '%java 7%' And
tblSoftwareUni.softwareName Like '%java 8%' And tblAssetCustom.State = 1


I searched finished reports for "Multiple versions of software", "Multiple versions" and finally just "version" and "versions" and plowed through several pages of results for unrelated items. If this has already been answered, my apologies for taking up your time.

My thanks in advance.

GMFDE

2 REPLIES 2
RCorbeil
Honored Sweeper II
The problem you're running into is down to the phrasing of your condition. The contents of your WHERE clause is evaluated for each software name. When you say
Where tblSoftwareUni.softwareName Like '%java 7%' And
tblSoftwareUni.softwareName Like '%java 8%'
you're saying "if the current record is both Java 7 and Java 8 then...".

Take a look at this thread. Depending on how you want to report the results, this thread might be useful, too.
GMFDE
Engaged Sweeper III
RC62N wrote:
The problem you're running into is down to the phrasing of your condition. The contents of your WHERE clause is evaluated for each software name. When you say
Where tblSoftwareUni.softwareName Like '%java 7%' And
tblSoftwareUni.softwareName Like '%java 8%'
you're saying "if the current record is both Java 7 and Java 8 then...".

Take a look at this thread. Depending on how you want to report the results, this thread might be useful, too.


That's actually the goal. Java is one of those apps where you can have multiple versions installed on the same machine. In this case, we have about 2100 computers with Java 7 still installed but about half of those also have Java 8 installed as well. My next goal is to uninstall Java 7 from the remaining systems but I need to prioritize those systems that have both. The 'or' statement in this case only evaluates for either Java 7 or Java 8. What I need is in fact what you stated, 'Java 7 AND Java 8'. However, I think I found a way to do this in SCCM reporting. But, for future reference, I would still like to know how to do this.

I did try the code listed in the second post you reference and while it has potential, one of my alternative goals is to create a chart from this data and I don't think I can make that method feed properly for charting.

All of that having been said, thank you for your help.