Troubleshooting SCCM last-used-date reporting

Troubleshooting SCCM last-used-date reporting

Summary

Below is a list of the most common reasons for why application usage data from SCCM doesn't get reflected in FNMS.

Synopsis

Below is a list of the most common reasons for why application usage data from SCCM doesn't get reflected in FNMS.

Discussion

1. In SCCM, the last-used-date of an application is often associated with an .exe file. However if that file is not recognized as being installed on the device then FNMS will not import it. This query can be used to determine if that's the case. After the where clause, make sure to uncomment the line that contains the search filter you want to use -
-- Run against the SCCM database
SELECT 
distinct
Name00,
MachineID as 'external ID',
sf.FileId ,
rua.ExplorerFileName0,
rua.FileVersion0,
rua.CompanyName0,
rua.FileDescription0,
rua.lastusedtime0,
case when si.ClientId is not null then 'Installed'
else 'Not Installed' end as 'File Installation Status'
FROM Computer_System_DATA csd
join v_GS_CCM_RECENTLY_USED_APPS rua
on rua.ResourceID = csd.MachineID 
join SoftwareFile sf
on sf.FileName = rua.ExplorerFileName0 
and sf.FileVersion = rua.FileVersion0 
and sf.FileDescription = rua.FileDescription0 
and sf.FileSize = rua.FileSize0 
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 
-- rua.lastusedtime0 > getdate()-90 -- and
-- name00 = 'name of computer' -- and
-- rua.ExplorerFileName0 like '%name of executable%' -- and
-- rua.companyName0 like '%name of software publisher%' -- and
-- rua.lastusedtime0 > getdate()-90 -- filter 
-- order by name00, ExplorerFileName0


2. The most common reasons for why a last-used-date value doesn't show up in FNMS is because the imported .exe file from SCCM is -
a.) Not recognized in the ARL
b.) Is recognized in the ARL but not linked to a software application
c.) Is recognized in the ARL and linked to an application, but that application is not recognized as being installed on the relevant device

Columns 10, 11 and 12 in the query below pertain to these three scenarios. Again, make sure to uncomment the line that contains the search filter you want to use.
select 
distinct
ic.ComputerName,
ic.InventoryAgent,
ic.InventoryDate,
ife.FileName,
ife.FileVersion,
ife.Company,
ife.Description,
u.LastUsedDate,
case when iife.ExternalID is null then 'not installed'
else 'installed' end as 'installation status',
case when ifem.FileEvidenceID > 0 then 'no'
else 'yes' end as 'linked to ARL file evidence',
st.fullname as 'linked to software title',
case when isd.SoftwareTitleID is not null then 'installed'
else 'not installed' end as 'installation status',
isd.isused,
isd.lastuseddate
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 InstalledFileEvidence infe
on infe.FileEvidenceID = ifem.FileEvidenceID 
and infe.ComplianceComputerID = ic.ComplianceComputerID 
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%'
--ife.company like '%name of software publisher%'
--st.fullname like '%name of software application%'
--ifem.fileevidenceid > 0 --
--ifem.fileevidenceid < 0 --
--isd.softwaretitleid is not null and isd.lastuseddate is not null

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.


3. If all of the requirements in the previous step have been met, then check to see if the last-used-date period and minimum usage instance requirements have been met for your application. These settings can be found in the usage tab of the software application record -

User-added image

Here's the query to verify whether those data thresholds have been met -
select 
distinct
ic.ComputerName,
ic.InventoryAgent,
ic.InventoryDate,
ife.FileName,
ife.FileVersion,
st.fullname as 'application name',
u.LastUsedDate as 'last used date from file evidence',
getdate() - (st.UsagePeriod * 30)  as 'usage date threshold',
case when st.IsMonitoringSessions = 1 then 'true' 
else 'false' end as 'usage session monitoring enabled',
u.numberofsessions 'number of usage sessions',
st.UsageSessions as 'minimum number of usage session required',
case when st.IsMonitoringActiveTime = 1 then 'true'
else 'false' end as 'usage time monitoring enabled',
u.ActiveTimeInSeconds 'amount of usage time',
st.usageactivetime as 'minimum amount of usage time required'
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 ImportedInstalledFileEvidence iife
on iife.ComplianceConnectionID = ife.ComplianceConnectionID 
and iife.ExternalFileID = ife.ExternalFileID 
and iife.ExternalID = ic.ExternalID 
join importedfileevidencemapping ifem
on ifem.ComplianceConnectionID = iife.ComplianceConnectionID 
and ifem.ExternalFileID = iife.ExternalFileID 
join InstalledFileEvidence infe
on infe.FileEvidenceID = ifem.FileEvidenceID 
and infe.ComplianceComputerID = ic.ComplianceComputerID 
join SoftwareTitleFileEvidence stfe
on stfe.FileEvidenceID = ifem.FileEvidenceID 
join SoftwareTitle st
on st.SoftwareTitleID = stfe.SoftwareTitleID 
join InstalledSoftwareData isd
on isd.ComplianceComputerID = ic.ComplianceComputerID 
and isd.SoftwareTitleID = stfe.SoftwareTitleID 
join SoftwareTitleEx stex
on stex.SoftwareTitleID = st.SoftwareTitleID 
where
--ic.ComputerName = 'name of computer' -- and
--ife.filename like '%name of file%'
--ife.company like '%name of software publisher%'
--st.fullname like '%name of software application%'
--ifem.fileevidenceid > 0 --
--ifem.fileevidenceid < 0 --
--isd.softwaretitleid is not null and isd.lastuseddate is not null


Labels (1)
Was this article helpful? Yes No
No ratings
Version history
Revision #:
1 of 1
Last update:
‎Jun 08, 2018 08:01 AM
Updated by: