Usage tracking data is inconsistent for certain files that are being tracked via Application Metering 8.5 RU2 and below

book

Article ID: 170321

calendar_today

Updated On:

Products

Inventory Solution

Issue/Introduction

Usage tracking data is inconsistent for certain files that are being tracked via Application Metering 8.5 RU2 and below

Cause

This is caused by the same file being assigned to more than one Software Product, and one or more of the Software Products have metering turned off.

Resolution

Files can be assigned to more than one Product, as long as they are all enabled for Application Metering. Otherwise either remove the files from any disabled or off Product for Metering, or delete any Product not being used.

The following query can help identify files that are assigned to more than one Product, by Product name.

DECLARE @tempFileComponentProductTable TABLE (FileGuid UNIQUEIDENTIFIER, CompopnentGuid UNIQUEIDENTIFIER, ProductGuid UNIQUEIDENTIFIER)
INSERT INTO @tempFileComponentProductTable
SELECT distinct se._ResourceGuid [FileGuid], ra1.ChildResourceGuid [CompopnentGuid], ra1.ParentResourceGuid [ProductGuid]
FROM dbo.Inv_Software_Execution se
JOIN dbo.ResourceAssociation ra
ON ra.ChildResourceGuid = se._ResourceGuid
AND ra.ResourceAssociationTypeGuid = 'EABE86D3-AAFD-487A-AF63-5C95D7511AF6' --Software Component Contains File
JOIN dbo.ResourceAssociation ra1
ON ra1.ChildResourceGuid = ra.ParentResourceGuid
AND ra1.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483' --Software Product Contains Software Component
 
SELECT Duplicates.FileGuid, Duplicates.ProductGuid, Product.Name as [Product Name]
FROM vSoftwareProduct Product
JOIN (
       SELECT distinct FileGuid, ProductGuid
       FROM @tempFileComponentProductTable
       WHERE FileGuid in (
              select DuplicatedFiles.FileGuid 
              from (SELECT distinct FileGuid, ProductGuid, count (FileGuid) as [Count]
                           FROM @tempFileComponentProductTable
                           Group by FileGuid, ProductGuid
                           having count (FileGuid) > 1) DuplicatedFiles
              group by DuplicatedFiles.FileGuid
              having count (DuplicatedFiles.FileGuid) > 1
       )
       group by FileGuid, ProductGuid
       having count (ProductGuid) > 1
       ) as Duplicates
ON Duplicates.ProductGuid=Product.Guid
order by Duplicates.FileGuid