Understanding and troubleshooting usage metering data imported from Microsoft Endpoint Configuration Manager (SCCM)

Understanding and troubleshooting usage metering data imported from Microsoft Endpoint Configuration Manager (SCCM)

Summary

This article describes usage metering data that FlexNet Manager Suite imports from Microsoft Endpoint Configuration Manager (also known as ConfigMgr, or SCCM), and suggests some SQL queries that can be used to investigate and troubleshoot problems with imported data.

Queries used to extract usage data from ConfigMgr

Usage metering data is obtained from two primary views in the ConfigMgr database:

  1. v_GS_CCM_RECENTLY_USED_APPS
  2. MonthlyUsageSummary

The following query illustrates how data is obtained from the v_GS_CCM_RECENTLY_USED_APPS view.

Usage data for both installer evidence and file evidence returned by this query is imported. Usage data for files is only processed for files that are shown by the query with a FileInstallationStatus of "Installed".

(The query contains some example filters at the end which can be uncommented and modified as appropriate to return specific data that may be of interest.)

 

-- Run this query against the ConfigMgr database
SELECT DISTINCT
    csd.Name00,
    csd.MachineID,
    sf.FileId,
    rua.ExplorerFileName0,
    rua.FileVersion0,
    rua.FileDescription0,
    rua.CompanyName0,
    rua.FileSize0,
    FileInstallationStatus = CASE WHEN si.ClientId IS NOT NULL THEN 'Installed' ELSE 'Not Installed' END,
    MsiDisplayName = ISNULL(NULLIF(msiDisplayName0, ''), ProductName0),
    MsiPublisher = ISNULL(NULLIF(msiPublisher0, ''), CompanyName0),
    MsiVersion = ISNULL(NULLIF(msiVersion0, ''), ProductVersion0),
    ProductCode = ISNULL(ProductCode0, ''),
    LastUserName0,
    rua.LastUsedTime0
FROM v_GS_CCM_RECENTLY_USED_APPS rua
    JOIN Computer_System_DATA csd
        ON csd.MachineID = rua.ResourceID
    JOIN SoftwareFile sf
        ON sf.FileName = rua.ExplorerFileName0 
        AND sf.FileVersion = rua.FileVersion0 
        AND sf.FileDescription = rua.FileDescription0 
        AND sf.FileSize = rua.FileSize0 
    LEFT OUTER JOIN SoftwareProduct sp
        ON sp.ProductId = sf.ProductId 
        AND sp.CompanyName = rua.CompanyName0 
    LEFT OUTER JOIN SoftwareInventory si
        ON si.ClientId = rua.ResourceID 
        AND si.FileId = sf.FileId 
        AND si.ProductId = sp.ProductId 
WHERE
    -- csd.Name00 = 'name of computer' AND
    -- rua.ExplorerFileName0 LIKE '%name of executable%' AND
    -- rua.CompanyName0 LIKE '%name of software publisher%' AND
    -- rua.LastUsedTime0 > GETDATE()-90 AND
    1=1

 

 

The following query illustrates how data is obtained from the MonthlyUsageSummary view. 

 

-- Run this query against the ConfigMgr database
SELECT
    csd.Name00,
    csd.MachineID,
    ExternalFileID = m.FileID,
    sf.FileName,
    sf.FileVersion,
    sf.FileDescription,
    sp.CompanyName,
    sf.FileSize,
    FileInstallationStatus = CASE WHEN si.ClientId IS NOT NULL THEN 'Installed' ELSE 'Not Installed' END,
    mu.FullName,
    StartDate = LEFT(m.TimeKey, 4) + '/' + RIGHT(m.TimeKey, 2) + '/01',
    m.LastUsage,
    m.UsageTime,
    m.UsageCount,
    m.TSUsageCount
FROM dbo.MonthlyUsageSummary AS m
    JOIN Computer_System_DATA csd ON csd.MachineID = m.SystemItemKey
    JOIN dbo.v_metereduser AS mu ON mu.MeteredUserID = m.MeteredUserID
    JOIN SoftwareFile sf
        ON sf.FileId = m.FileID
    LEFT OUTER JOIN SoftwareProduct sp
        ON sp.ProductId = sf.ProductId 
    LEFT OUTER JOIN SoftwareInventory si
        ON si.ClientId = m.SystemItemKey
        AND si.FileId = sf.FileId 
        AND si.ProductId = sf.ProductId 
WHERE
    -- csd.Name00 = 'name of computer' AND
    -- sf.FileName LIKE '%name of executable%' AND
    -- sp.CompanyName LIKE '%name of software publisher%' AND
    -- m.LastUsage > GETDATE()-90 AND
    1=1

 

 

What if usage is not recognized when expected?

Some reasons for why usage of an application may not be recognized by FlexNet Manager Suite based on data imported from ConfigMgr are:

  1. The Application Recognition Library (ARL) does not contain an appropriate rule to recognize installer or file evidence details associated with the usage.
  2. The application the usage data is associated with is not recognized as currently installed.

Running a query like the following and inspecting the assessment details in the last 4 columns can help to give insight into these situations when usage is recognized based on file evidence (a similar query would be possible to write for assessing installer evidence too):

 

-- Run this query against the FlexNet Manager Suite compliance database
SELECT
    ic.ComputerName,
    ic.InventoryAgent,
    ic.InventoryDate,
    ife.FileName,
    ife.FileVersion,
    ife.Company,
    ife.Description,
    RawLastUsedDate = u.LastUsedDate,
    isd.IsUsed,
    RecognizedLastUsedDate = isd.LastUsedDate,
    'Is file installed' = CASE WHEN iife.ExternalID IS NULL THEN 'No' ELSE 'Yes' END,
    'Is file linked to application for usage' = CASE WHEN stfe.EvidenceExistenceRuleID IN (1 /* required */, 2 /* not for recognition */, 4 /* at least one */) THEN 'Yes' ELSE 'No' END,
    'Linked application' = st.fullname,
    'Is application recognized as installed' = CASE WHEN isd.SoftwareTitleID IS NOT NULL THEN 'Yes' ELSE 'No' END
FROM
    ImportedInstalledFileEvidenceUsage u
    JOIN ImportedComputer ic
        ON u.ComplianceConnectionID = ic.ComplianceConnectionID 
        AND u.ExternalID = ic.ExternalID 
    JOIN ImportedFileEvidence ife
        ON ife.ComplianceConnectionID = u.ComplianceConnectionID 
        AND ife.ExternalFileID = u.ExternalFileID 
    LEFT OUTER JOIN ImportedInstalledFileEvidence iife
        ON iife.ComplianceConnectionID = ife.ComplianceConnectionID 
        AND iife.ExternalFileID = ife.ExternalFileID 
        AND iife.ExternalID = ic.ExternalID 
    LEFT OUTER JOIN ImportedFileEvidenceMapping ifem
        ON ifem.ComplianceConnectionID = iife.ComplianceConnectionID 
        AND ifem.ExternalFileID = iife.ExternalFileID 
    LEFT OUTER JOIN SoftwareTitleFileEvidence stfe
        ON stfe.FileEvidenceID = ifem.FileEvidenceID 
    LEFT OUTER JOIN SoftwareTitle st
        ON st.SoftwareTitleID = stfe.SoftwareTitleID 
    LEFT OUTER JOIN InstalledSoftwareData isd
        ON isd.ComplianceComputerID = ic.ComplianceComputerID 
        AND isd.SoftwareTitleID = stfe.SoftwareTitleID 
WHERE
    --ic.ComputerName = 'name of computer' AND
    --ife.FileName like '%name of file%' AND
    --ife.Company like '%name of software publisher%' AND
    --st.FullName like '%name of software application%' AND
    1=1

 

 

If relevant evidence recognition rules are not currently configured in the ARL, you can configure rules yourself or send evidence details to Flexera with a request for rules to be added to the published ARL.

Also consider that for usage to be detected it must meet the minimum usage period and other conditions configured on the "Usage" tab of the application record:.User-added image
The following query can help to verify whether these data thresholds have been met in relation to usage recorded based on file evidence (a similar query would be possible to write for assessing installer evidence too):

 

-- Run this query against the FlexNet Manager Suite compliance database
SELECT
    ic.ComputerName,
    ic.InventoryAgent,
    ic.InventoryDate,
    ife.FileName,
    ife.FileVersion,
    st.FullName,
    u.LastUsedDate,
    MinimumUsageDate = DATEADD(m, -(st.UsagePeriod * 30), GETDATE()),
    st.IsMonitoringSessions,
    u.NumberOfSessions,
    st.UsageSessions,
    st.IsMonitoringActiveTime,
    u.ActiveTimeInSeconds,
    st.UsageActiveTime
FROM
    ImportedInstalledFileEvidenceUsage u
    JOIN ImportedComputer ic
        ON u.ComplianceConnectionID = ic.ComplianceConnectionID 
        AND u.ExternalID = ic.ExternalID 
    JOIN ImportedFileEvidence ife
        ON ife.ComplianceConnectionID = u.ComplianceConnectionID 
        AND ife.ExternalFileID = u.ExternalFileID 
    JOIN ImportedFileEvidenceMapping ifem
        ON ifem.ComplianceConnectionID = ife.ComplianceConnectionID 
        AND ifem.ExternalFileID = ife.ExternalFileID 
    JOIN SoftwareTitleFileEvidence stfe
        ON stfe.FileEvidenceID = ifem.FileEvidenceID 
        AND stfe.EvidenceExistenceRuleID IN (1 /* required */, 2 /* not for recognition */, 4 /* at least one */)
    JOIN SoftwareTitle st
        ON st.SoftwareTitleID = stfe.SoftwareTitleID 
    JOIN InstalledSoftwareData isd -- SoftwareTitle is recognized as installed
        ON isd.ComplianceComputerID = ic.ComplianceComputerID 
        AND isd.SoftwareTitleID = stfe.SoftwareTitleID 
WHERE
    --ic.ComputerName = 'name of computer' AND
    --ife.FileName LIKE '%name of file%' AND
    --ife.Company LIKE '%name of software publisher%' AND
    --st.FullName LIKE '%name of software application%' AND
    1=1

 

 

 

Labels (1)
Was this article helpful? Yes No
100% helpful (2/2)
Comments

@pikeda wrote:

If the file evidence record is not in the ARL, you can assign it to an application yourself (in the 'License Compliance' > 'Unrecognized Evidence') page or you can send us the output from the query and request it be added to our content library.

@pikeda 

How do I request file evidence to be added to the content library? We have an issue where Alteryx Designer 2020.1 seems to have the proper file evidence, but v2020.4 doesn't ( guessing because it's new).

I am trying to avoid having to add the file evidence manually, as I don't want it overlapping with the ARL updates.

Thanks! 

Hello @johnksilverwood

Please find the KB article below on how to raise a Content request

https://community.flexera.com/t5/Data-Platform-Knowledge-Base/How-to-raise-Content-Requests-through-...

For the field Version* Please select ARL as it would be most appropriate. 

Once submitted, one of our support members will pick it up from the queue to assist.

Regards,

Allen

Hello - two things on this: 

1. in the last FNMS query shared, this line throws an issue for me - I have to comment it out to get the query to work: 

-- 'Is file linked to application for usage' = CASE
--WHEN stfe.EvidenceExistenceRuleID IN (1 /* required */, 2 /* not for recognition */, 4 /* at least one */) NULL THEN 'Yes' ELSE 'No' END,

2. When File Installation Status = Not Installed as per SCCM, we see FNMS also shows not installed for this file evidence, which we are assuming is why the metering data in SCCM is not feeding over to the Application / devices in FNMS Last Used Date data (using the installed File Evidence as link) . Any suggestion/guidance as to how to correct this? It's called out as point #2 above as possible cause if issue but I missed guidance on how to fix. We were also thinking of contacting our SCCM support team with this analysis and get their further review. 

@elewis - thanks for noting the problem with the query. I've updated this article with a change which I believe should correct that.

On the 2nd point - FlexNet Manager Suite tracks usage associated with recognized installations of applications - the usage details are properties of an installation. If software is not currently recognized as installed then the installation can't be flagged as "used". I'm not sure if I fully followed your query, but hopefully that helps.

@ChrisG Thanks for updating the query.

The query did not word for me because of a missing comma.

MinimumUsageDate = DATEADD(m, -(st.UsagePeriod * 30), GETDATE())

should be 

MinimumUsageDate = DATEADD(m, -(st.UsagePeriod * 30), GETDATE()),

 Just in case someone else had this as well. 

Thanks.

Thanks for noting that typo @joaopereira. I've fixed it.

Version history
Revision #:
5 of 5
Last update:
‎Nov 29, 2021 06:26 PM
Updated by:
 
Contributors