SCCM: Determining installed applications last use time

  • Article

ConfigMgr will definitely be able to let SCCM admins know what is installed in the environment regardless if it is a server or workstation. SCCM by default comes with a set of reports that SCCM admins can utilize to gather information. The one that mentioned here is, "Computers that has a metered program but have not run the program since a specific date". What is so special about this report you may think, but it is pretty interesting to know how this data is derived.

The data set for this is based on Software Inventory and this means gathering file types that are on machines. The last used time is gathered asp art of software metering.

This report will provide the information based on the files inventoried and their respective last used date. Nothing unique right?

Here comes the interesting part, picture the scenario as below.

All of us will know that in order for Microsoft Office to be used, it has to be installed and after installation, it will appear under the machine's add/remove programs. This report mentioned reports based not on a usable application but just the executable file. This means that if you copy Excel.exe from another machine to a desktop, it will take it that a working copy of Excel exists of my machine and report against that. Not very smart and logical, huh?

That would require to report against what has been installed and not used and not any executable files that are related to the application.

To address this, find the query below:

select
 
distinct VRS1.Name0,arp1.DisplayName0,z.ProductName,z.[Last Use],
 
case
 
when VRS1.Obsolete0 = 0  then 'Active'
 
when VRS1.Obsolete0 = 1  then 'inactive'
 
end as  [SCCM Client Status]
 
,z.FileName,substring(OU.System_OU_Name0,15,3) [Site],VRS1.User_Name0,
 
case
 
when d.IsMobile0 = 1 then 'Laptop' 
 
when d.IsMobile0 = 0  and e.SystemRole0 = 'Workstation' then  'Desktop'
 
when d.IsMobile0 = 0  and e.SystemRole0 = 'Server' then  'Server'
 
end as  [Type]
 
from v_Add_Remove_Programs ARP1
 
Left join(Select distinct  VRS.resourceid,VRS.name0,ARP.DisplayName0, mf.ProductName,
 
MF.FileName,MAX(mu.LastUsage)[Last Use]
 
from v_R_System VRS
 
left join v_MonthlyUsageSummary MU on  MU.ResourceID = VRS.ResourceID
 
left join v_Add_Remove_Programs ARP on  vrs.ResourceID = arp.ResourceID
 
left join v_MeteredFiles MF on  MF.MeteredFileID = Mu.FileID
 
where arp.DisplayName0 like '%App Name%' and  mf.FileName = 'App Exe File'
 
Group by  vrs.Name0,vrs.ResourceID,arp.DisplayName0,MF.FileName,mf.productname)Z on z.ResourceID = ARP1.resourceid
 
Left join v_R_System VRS1 on  VRS1.ResourceID = ARP1.ResourceID
 
left join v_RA_System_SystemOUName OU on  ou.ResourceID = VRS1.ResourceID
 
inner join v_GS_PROCESSOR d on  d.ResourceID = VRS1.ResourceID
 
inner join v_GS_SYSTEM e on  e.ResourceID = VRS1.resourceid
 
where arp1.DisplayName0 like '%App Name%' and vrs1.Name0 is not null