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:.
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