Video Screencast Help
Symantec Secure Login will be live on Connect starting February 25. Get the details here.

Application Metering vs. Software Catalog Entries

Created: 05 Apr 2011 • Updated: 05 Apr 2011 | 2 comments
Language Translations
haryadoon's picture
+4 4 Votes
Login to vote

The problem

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.

A solution

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%')
  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
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)]

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:

  1. Find the report in the SMP Console
  2. 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".
  3. 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.
  4. In the report frame, top-right corner, click "Edit".  You may have to scroll right to see the edit button.
  5. By default, the first thing you see is the Data Source > Parameterized Query tab.  Do your code editing here.
  6. When you're done, test it using the "Results" tab, then again using the "Preview" button.  Click "Save Changes" when you're finished.
  7. 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.

Comments 2 CommentsJump to latest comment

mitsaoua's picture

Hi haryadoon,

Are the Run Time values in seconds or milliseconds?

Thank you,


Login to vote
jason.f's picture

This doesn't seem to return events where run count = 0, am I seeing that correctly? Is it possible to modify the query to return computers that have the software installed but have never ran it? I've looked into it a little bit and I can't seem to figure out how to do it.

Login to vote