Application Metering vs. Software Catalog Entries
Application Metering is extremely useful for our clients' managers and software license specialists. A big part of licensing is understanding:
- how many licenses you own for a particular software release
- how many places that particular software release is installed (sometimes it's assigned to a computer and sometimes to a user)
- If installed, how many people actually use it
Often, you have different licenses for different releases. Microsoft is classic for that: you license Visio 2007 Professional differently than Visio 2007 Standard - the costs are different, the terms may be different. So it's important to know who is using which version so you can manage your licenses properly.
App Metering includes some reports on application usage. Unfortunately, they only show usage by executable. What our end users need is to know which Software Catalog entry is being used where and by whom.
This subject has been tackled before in Connect:
1. "Installed vs Used report in the real world"
This is no use as the solution only works on Altiris 6. We only use Altiris 7.
2. "Reporting on different app versions in Application Metering"
This is better, but there is no resolution. It really doesn't cover what our customers want: to see the results by Software Catalog entry name.
To resolve this in 7.0, you'll need to change the SQL behind some of the App Metering reports. These are found under Reports > All Reports > Software > Application Metering
In SQL, you need to join vFile to vSoftwareComponent; you do this via ResourceAssociation. That's the magic bit: ResourceAssociation simply records many-to-many relationships between other tables or views. In this case, the relationship between vFile and vSoftwareComponent is where a file is a child and a software component is the parent. In SQL, you join ResourceAssociation where child = file and parent = software component.
In the relevant App Metering reports (see list below), there is always a vFile record. To join vFile to vSoftwareComponent, add something like this:
LEFT OUTER JOIN ResourceAssociation ra ON ra.ChildResourceGuid = fd.Guid -- Relationship between file and software component -- fd is an alias for vFile elsewhere in the query -- The file is the child in this relationship LEFT OUTER JOIN vSoftwareComponent sc ON sc.guid = ra.ParentResourceGuid -- The software component is the parent
Note that ‘fd’ should be already defined as a reference to vFile. Also note that you should use "LEFT OUTER JOIN" because not all files are associated with software resources. Of course, if you're only interested in reporting on software defined in the catalog, then use a normal join, and you won't see files that don't have a catalog entry.
In the select clause, next to fd.[Name], add something like:
ISNULL(sc.[Name], 'None defined') [Software Component],
(and reflect this in the GROUP BY clause).
The resulting SQL looks like this (lines with --<< are the ones I've added) - this is the code for the Application Resource History report:
DECLARE @v1_TrusteeScope nvarchar(max) SET @v1_TrusteeScope = N'%TrusteeScope%' IF ('%Start Date%' <= '%End Date%') BEGIN SELECT fd.[Name] [Application Name], ISNULL(sc.[Name], 'Not defined') [Software Component], ---------<< COUNT(DISTINCT CAST(ams.[_ResourceGuid] as varchar(40))) [Count of Computers], CAST(AVG(ams.[Average CPU Usage]) as bigint) [Average CPU Usage], CAST(AVG(ams.[Peak Memory]) as bigint) [Average Peak Memory], SUM(ams.[Run Count]) [Run Count], ROUND(SUM(ams.[Total Run Time]),2) [Total Run Time (sec)], ROUND((SUM(ams.[Total Run Time]) / SUM(ams.[Run Count])),2) [Average Run Time (sec)] FROM dbo.vAMMonthlySummary ams -- JOIN dbo.AeXAMSummaryDateRanges sdr -- ON ams.[Month Year] = sdr.[Month Year] JOIN dbo.vFile fd ON fd.Guid = ams.FileResourceGuid LEFT OUTER JOIN ResourceAssociation ra -----------------<< ON ra.ChildResourceGuid = fd.guid -------------------<< LEFT OUTER JOIN vSoftwareComponent sc ------------<< ON sc.Guid = ra.ParentResourceGuid --------------------<< WHERE 1 = 1 AND ams._ResourceGuid IN ( SELECT DISTINCT sm.[ResourceGuid] FROM dbo.ScopeMembership sm WHERE sm.[ScopeCollectionGuid] IN ( SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope) ) ) AND ams.[Last Start] BETWEEN '%Start Date%' AND '%End Date%' AND fd.[Name] LIKE '%Application%' GROUP BY fd.[Name], sc.[Name] -----------------<< ORDER BY COUNT(DISTINCT CAST(ams.[_ResourceGuid] as varchar(40))) DESC END ELSE BEGIN SELECT 'Please choose a Start Date which is before the End Date.' [Application Name], 'Not defined' [Software Component], ---------<< 0 [Count of Computers], 0 [Average CPU Usage], 0 [Average Peak Memory], 0 [Run Count], 0 [Total Run Time (sec)], 0 [Average Run Time (sec)] END
This same logic can be applied to other reports such as:
- Drill Down to Application Usage by Computer
- Concurrent Usage
- Application Usage
- Application Usage by Computer
- Installed versus Used (the drill down to this already does it)
- Applications by First and Last Start
- Applications by Last Stop
- Denial Events by Application
To make the appropriate changes to the SQL code for a built-in report:
- Find the report in the SMP Console
- In the tree on the left, right-click the report, or on the drop down menu at the top left of the report frame, select "Clone".
- The cloned report by default is called "Copy of ...". I recommend you rename it to something relevant to you, and keep a consistent naming convention.
- In the report frame, top-right corner, click "Edit". You may have to scroll right to see the edit button.
- By default, the first thing you see is the Data Source > Parameterized Query tab. Do your code editing here.
- When you're done, test it using the "Results" tab, then again using the "Preview" button. Click "Save Changes" when you're finished.
- If you need to change a drill down report, clone the main report first, run it (don't edit it), then right-click on a result row and open the drill down. In the top-left corner of the drill down report frame, select "Clone" from the drop-down menu. Once you've finished editing the drill down, edit the main report and update the Drill Down tab to point to the new cloned drill down report.