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:
- v_GS_CCM_RECENTLY_USED_APPS
- 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:
- The Application Recognition Library (ARL) does not contain an appropriate rule to recognize installer or file evidence details associated with the usage.
- 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
on - edited on by
ChrisG
