book
Article ID: 170321
calendar_today
Updated On:
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