Software report grouped by software name only

[TimH]

Is there a way to create a report similar to the "All installed software" report where we can group by similar/like software names?

For example, we would like to take this:

Software..................................Version..Publisher...Total
Java 7 Update 10 (64-bit)..7.0.100..Oracle........1
Java 7 Update 11...................7.0.110..Oracle.......1
Java 7 Update 17...................7.0.170..Oracle.......1
Java 7 Update 17 (64-bit)..7.0.170..Oracle........1
Java 7 Update 21...................7.0.210..Oracle.......9
Java 7 Update 21 (64-bit)..7.0.210..Oracle........5

and make it look like this:

Software..Total
Java 7........18

We do not need the version or the publisher. We just want the total number of installs regardless of version or publisher.

Any assistance is appreciated.

Thanks in advance.

[Susan_A]

Lansweeper scans the software names as they are listed in Add/Remove Programs on the client machine itself. If the software vendor is not consistent with naming, you unfortunately have no other choice but to manually clean up the list of software names, e.g. by using Cases as RC62N suggested. To the human eye it's obvious that Java 7 Update 11 and Java 7 Update 17 are both Java 7 installations for instance, but to SQL it's not. It needs to be told.

[TimH]

I need to do the entire list so it's gonna get messy but if that is what I need to do, then so be it.

Thanks RC62N!

[RCorbeil · Accepted solution]

It could get messy if there are lots of titles you want to do that to, but working with your specific request, taking the "All installed software" as a starting point:

SELECT Top 1000000
  CASE
    WHEN Left(tblSoftwareUni.softwareName, 6) = 'Java 7' THEN 'Java 7'
    WHEN Left(tblSoftwareUni.softwareName, 6) = 'Java 8' THEN 'Java 8'
    WHEN Left(tblSoftwareUni.softwareName, 10) = 'Java(TM) 6' THEN 'Java 6'
    ELSE tblSoftwareUni.softwareName
  END As Software,
  Count(tblSoftware.AssetID) As Total
FROM
  tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
WHERE
  tblAssetCustom.State = 1
GROUP BY
  CASE
    WHEN Left(tblSoftwareUni.softwareName, 6) = 'Java 7' THEN 'Java 7'
    WHEN Left(tblSoftwareUni.softwareName, 6) = 'Java 8' THEN 'Java 8'
    WHEN Left(tblSoftwareUni.softwareName, 10) = 'Java(TM) 6' THEN 'Java 6'
    ELSE tblSoftwareUni.softwareName
  END
ORDER BY
  Count(tblSoftware.AssetID) Desc

should do what you're asking for.

The

  CASE
    WHEN Left(tblSoftwareUni.softwareName, 6) = 'Java 7' THEN 'Java 7'
    WHEN Left(tblSoftwareUni.softwareName, 6) = 'Java 8' THEN 'Java 8'
    WHEN Left(tblSoftwareUni.softwareName, 10) = 'Java(TM) 6' THEN 'Java 6'
    ELSE tblSoftwareUni.softwareName
  END

does the substitutions for Java 6, 7 and 8 while the ELSE passes anything else through.

If your list is long, you might consider creating a substitution table and referencing that.