It is an interesting experience to write SQL query about SCCM Application Catalog Usage Report, as we need to use a function query: (fn_ListLatestApplicationCIs(1033)
from this function, we can get the data of Manufacturer, DisplayName, SoftwareVersion, CI_UniqueID, ModelID, SDMPackageVersion, DateCreated, LastModifiedBy from our SCCM.
Anyway, here is my SQL Query:
select app.RequestID as ‘ReqID’,
(select top 1 ResourceID from v_r_user where Unique_User_Name0 = umr.UniqueUserName) as ‘UserResourceID’,
(select DisplayName from fn_ListApplicationCIs(1033) where ModelName = app.AppID AND IsLatest = 1) as ‘Requested Software’,
umr.UniqueUserName as “User”,
(select Name0 from v_R_System where umr.MachineResourceID = v_R_System.ResourceID) as ‘Machine Name’,
app.CreationTime as ‘RequestDate’
from UserAppModelSoftwareRequest app
join UserMachineRelation umr on app.RelationshipResourceID = umr.RelationshipResourceID
Here are some references:
SCCM Application Statistics :
http://www.nickalmiron.com/?p=10#comment-111
“Functions” in SCCM SQL Reporting (fn_ListLatestApplicationCIs(1033) ) :
https://social.technet.microsoft.com/Forums/en-US/70a95f2b-21d7-4509-9b54-1e9040e99ee9/reporting-on-applications-in-configmgr-2012?forum=configmanagerapps