REQUEST: Total Software installs grouped by Custom Field

[aplrich]

Could someone help with a report that would show total number of software installs grouped by Custom Field? E.G

So the output would have the report headers
Customfieldname | Software 1 | software 2 | software 3
and then the values
Customfield value | total count | total count | total count

[Daniel_B]

You can add any column to this report. Only you'll need to add additional columns to both the Select part and the Group by part of the Query in order to count installations per software and per operating system. In this case, add tblOperatingsystem to your report and select the Caption field. Please find the modified example report below:

Select Top 1000000 tblAssetCustom.Custom2,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblOperatingsystem.Caption As OS,
  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
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1
Group By tblAssetCustom.Custom2,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  tblOperatingsystem.Caption
Order By tblAssetCustom.Custom2,
  Software

[aplrich]

Perfect - i'm mostly there now. I don't know if what i'm about to ask is feasible but...

Is it possible to list the operating system in the same column as the softwarename? So in essence, just count it as normal software as per the rest we're counting? Is it possible to show the contents of two tables in one column? In essence I want to include OS's into my count.

[Daniel_B]

The built-in report Software: All installed software is already close to what you are looking for. Just add the custom field to the report and include it in grouping. Save the result as a new report.

Select Top 1000000 tblAssetCustom.Custom2,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  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 tblAssetCustom.Custom2,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher
Order By tblAssetCustom.Custom2,
  Total Desc

[aplrich]

this is a great start, thanks for this. Another question if you don't mind.

Would it be easier if instead of having columns for software, we had the report as follows

Customfield | Software Name | Count

In this way, we'd have a row for each installed software instance in each custom field.

So the values might be

Customvalue1 | MS Office | 10
Customvalue1 | Windows 2003 | 10
Customvalue2 | MS Office | 3
Customvalue2 | Windows 2003 | 3

thanks again

[Daniel_B · Accepted solution]

You need to use one subquery for every software which you want to count. The report below is an example for two software names ("Java 7%" and "Citrix Receiver%"). It needs some adaption to your exact needs:

Select Distinct Top 1000000 tblAssetCustom.Custom2 As
  [Custom field column name],
  tCountCitrix.Installations As [Citrix Receiver installations],
  tCountJava.Installations As [Java 7 installations]

From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype

  Left Join (Select tblAssetCustom.Custom2,
    Count(tblSoftware.AssetID) As Installations
  From tblAssetCustom
    Inner Join tblSoftware On tblAssetCustom.AssetID = tblSoftware.AssetID
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like 'Citrix Receiver%'
  Group By tblAssetCustom.Custom2) tCountCitrix On tCountCitrix.Custom2 =
    tblAssetCustom.Custom2

  Left Join (Select tblAssetCustom.Custom2,
    Count(tblSoftware.AssetID) As Installations
  From tblAssetCustom
    Inner Join tblSoftware On tblAssetCustom.AssetID = tblSoftware.AssetID
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like 'Java 7%'
  Group By tblAssetCustom.Custom2) tCountJava On tCountJava.Custom2 =
    tblAssetCustom.Custom2

Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Order By [Custom field column name]