Using SSRS to Get Application Status for a Given User

July 10th, 2014 | Posted by John Marcum in ConfigMgr | John Marcum

I’ve recently migrated all of my software deployments from the legacy package and program model into the ConfigMgr 2012 application model. During a lunch and learn that I was presenting to our User Services team explaining how this works and why we moved to it one of the technicians posed a good question. He asked, “How do I check the status of an application that I’ve targeted to a user”? I know how I would do it; I’d check the monitoring node in the console, however I don’t give the console to User Services. I start digging through the built-in reports and found nothing that would answer his question.

That seemingly simple question turned out to be less than simple. In the past I’ve always trained support staff to read execmgr.log and use the built-in reports. The application model isn’t so simple though, frankly application logs on the client still confuse me. I set out to write a report that accepts the user name as input and displays the status of all deployments targeted to that user and/or targeted to that users’ primary devices.

Here’s a simple way for the Support Desk and Computer Technicians to see the status of applications. This report accepts the user name as input and can be filtered on a given application, set of applications of all applications in ConfigMgr.

image

Here’s the SQL I’m using for the report divided by the 3 datasets required.

–Main dataset:

select
all

SMS_AppDeploymentAssetDetails.AppName as ‘App Name’,

CASE WHEN SMS_AppDeploymentAssetDetails.AppStatusType = ‘1’ Then ‘Success’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘2’ Then ‘In Progress’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘3’ Then ‘Requirements Not Met’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘4’ Then ‘Unknown’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘5’ Then ‘Error’
else ‘Unknown’
End as ‘App Status Type’,

CASE WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘1’ Then ‘Compliant’
WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘2’ Then ‘Non-Compliant’
WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘4’ Then ‘Error’
WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘6’ Then ‘Partial Compliance’
else ‘Unknown’
End as ‘Compliance State’,

CASE WHEN SMS_AppDeploymentAssetDetails.EnforcementState  >= 5000 then ‘Error’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 4000 then ‘Unknown’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 3000 then ‘Requirements Not Met’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 2000 then ‘In Progress’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 1000 then ‘Success’
else ‘Unknown’
End As ‘Enforcement State’,

CASE WHEN SMS_AppDeploymentAssetDetails.DeploymentIntent  = ‘1’ Then ‘Install’
WHEN SMS_AppDeploymentAssetDetails.DeploymentIntent  = ‘2’ Then ‘Uninstall’
WHEN SMS_AppDeploymentAssetDetails.DeploymentIntent  = ‘3’ Then ‘Preflight’
else ‘Unknown’
End as ‘Deployment Intent’,

SMS_AppDeploymentAssetDetails.DTName as ‘Deplyoment Type Name’,

CASE WHEN SMS_AppDeploymentAssetDetails.InstalledState  = ‘1’ Then ‘Uninstall’
WHEN SMS_AppDeploymentAssetDetails.InstalledState  = ‘2’ Then ‘Install’
WHEN SMS_AppDeploymentAssetDetails.InstalledState  = ‘3’ Then ‘Unknown’
else ‘Unknown’
End as ‘Installed State’,

CASE WHEN SMS_AppDeploymentAssetDetails.IsMachineAssignedToUser = ‘1’ Then ‘Yes’
Else ‘No’
End as ‘Device Assigned to User?’,
SMS_AppDeploymentAssetDetails.MachineName as ‘Device Name’,

 

CASE WHEN CHARINDEX(‘\’,SMS_AppDeploymentAssetDetails.UserName)>0 THEN SUBSTRING
(SMS_AppDeploymentAssetDetails.UserName,CHARINDEX(‘\’,SMS_AppDeploymentAssetDetails.UserName)+1,255)
ELSE SMS_AppDeploymentAssetDetails.UserName END ‘User Name’

from fn_AppDeploymentAssetDetails(1033) AS SMS_AppDeploymentAssetDetails

where

((SMS_AppDeploymentAssetDetails.UserName like ‘%’ +  @user
or SMS_AppDeploymentAssetDetails.UserName =  ‘(SYSTEM)’)

and

(SMS_AppDeploymentAssetDetails.AppName in (@appname )))

ORDER BY UserName

 

–AppName parameter:

Select

distinct

SMS_AppDeploymentAssetDetails.AppName from fn_AppDeploymentAssetDetails(1033) AS SMS_AppDeploymentAssetDetails

 

–The User parameter is simply a text field.

 

Happy reporting!

You can follow any responses to this entry through the RSS 2.0 Responses are currently closed, but you can trackback.