→ 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: 
aplrich
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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]

View solution in original post

5 REPLIES 5
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper III
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
Lansweeper Alumni
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
Engaged Sweeper III
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
Lansweeper Alumni
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]