SCCM 2012 brought a lot of great new features to the fold. One of the most notable is the Application Catalog which allows users to install/request software through a web interface.
Awesome, right?!…… Yes, but what if we want to track how many requests are coming through the App Catalog? Or, get the install status of these requests? There are no out of box reports provided that give us this amount of detail, so, we have to create our own.
Should be easy enough, but, this is where it gets complicated. There are two types of requests that come through the App Catalog, those that require approval, and those that do not. These requests are tracked in two different locations, UserAppModelSoftwareRequest and v_UserAppRequests respectively. This means that if we want to report on every request we need to combine the data in these two locations. Additionally, we want to join some other tables to get some supplemental data such as Username, machine name, install state, name of the application, etc. This other information can be gathered from the below locations:
- User Info: v_R_User
- Machine Info: v_R_System
- Install State: vAppDeploymentResultsPerClientUser
- Application Info: fn_ListApplicationCIs(1033)
So now that we know where all our information is located, we can combine it to our desired output. The below query does just that.
Declare @tempTable table (ReqID nvarchar(256), UserResourceID int, MachineResourceID int, ModelName nvarchar(512), DisplayName nvarchar(512), RequestDate DateTime) Insert Into @tempTable select app.RequestID as 'ReqID', (select top 1 ResourceID from v_r_user where Unique_User_Name0 = umr.UniqueUserName) as 'UserResourceID', umr.MachineResourceID, app.AppID as 'ModelName', (select DisplayName from fn_ListApplicationCIs(1033) where ModelName = app.AppID AND IsLatest = 1) as 'DisplayName', app.CreationTime as 'RequestDate' from UserAppModelSoftwareRequest app join UserMachineRelation umr on app.RelationshipResourceID = umr.RelationshipResourceID Insert Into @tempTable select app.RequestGuid as 'RecID', (select ResourceID from v_r_User where Unique_User_Name0 = app.Unique_User_Name0) as 'UserResourceID', sys.ResourceID as 'MachineResourceID', app.ModelName, app.DisplayName, app.LastChanged as 'RequestDate' from v_UserAppRequests app join v_r_System sys on app.Netbios_Name0 = sys.Netbios_Name0 where CurrentState = 4; WITH Combined as (select distinct(app.ReqID), app.UserResourceID, app.MachineResourceID, app.ModelName, app.DisplayName, app.RequestDate, dep.EnforcementState, 'Status' = CASE WHEN dep.EnforcementState >= 1000 and dep.EnforcementState < 2000 THEN 'Success' WHEN dep.EnforcementState >= 2000 and dep.EnforcementState < 3000 THEN 'InProgress' WHEN dep.EnforcementState >= 3000 and dep.EnforcementState < 4000 THEN 'RequirementsNotMet' WHEN dep.EnforcementState >= 4000 and dep.EnforcementState < 5000 THEN 'Unknown' WHEN dep.EnforcementState >= 5000 and dep.EnforcementState < 6000 THEN 'Error' END from @tempTable app join vAppDeploymentResultsPerClientUser dep on app.UserResourceID = dep.ResourceID and dep.Descript = app.DisplayName)
First, we declare a temp table and insert our requests from each respective location. Then, using this temp table as our base dataset, we join the other listed tables to gather our supplemental data. At this point we have all the data we need and we just need to decide how we want to present it. If you want to just get an overview of the requests and their install statuses you can add this:
Select 'Application Requests', (select count(*) from Combined) as 'Total', (SUM(CASE WHEN Status = 'Success' then 1 else 0 end)) as 'Success', (SUM(CASE WHEN Status = 'InProgress' then 1 else 0 end)) as 'InProgress', (SUM(CASE WHEN Status = 'RequirementsNotMet' then 1 else 0 end)) as 'RequirementsNotMet', (SUM(CASE WHEN Status = 'Error' then 1 else 0 end)) as 'Error', (SUM(CASE WHEN Status = 'Unknown' then 1 else 0 end)) as 'Unknown' from Combined
Otherwise, if we want the detailed info for each request you can simply use:
Select * from Combined