Application Deployment Status
It seems that the built-in application deployment status reports require looking at a separate report for each status – success, waiting, error, unknown, requirements not met (I could be wrong, I didn’t look that hard).
I wanted to build a “complete” report for application deployments. First, I created a function in the secondary database I use (not touching the CM database itself) to display the enforcement state in user friendly text.
-- User Friendly Enforcement State
CREATE FUNCTION dbo.EnforcementState (@EnforcementState INT)
RETURNS VARCHAR (50)
AS
BEGIN
DECLARE @ReturnString VARCHAR (50) =
CASE
WHEN @EnforcementState = 1000 THEN 'Success'
WHEN @EnforcementState = 1001 THEN 'Already Compliant'
WHEN @EnforcementState = 1002 THEN 'Simulate Success'
WHEN @EnforcementState = 2000 THEN 'In progress'
WHEN @EnforcementState = 2001 THEN 'Waiting for content'
WHEN @EnforcementState = 2002 THEN 'Installing'
WHEN @EnforcementState = 2003 THEN 'Restart to continue'
WHEN @EnforcementState = 2004 THEN 'Waiting for maintenance window'
WHEN @EnforcementState = 2005 THEN 'Waiting for schedule'
WHEN @EnforcementState = 2006 THEN 'Downloading dependent content'
WHEN @EnforcementState = 2007 THEN 'Installing dependent content'
WHEN @EnforcementState = 2008 THEN 'Restart to complete'
WHEN @EnforcementState = 2009 THEN 'Content downloaded'
WHEN @EnforcementState = 2010 THEN 'Waiting for update'
WHEN @EnforcementState = 2011 THEN 'Waiting for user session reconnect'
WHEN @EnforcementState = 2012 THEN 'Waiting for user logoff'
WHEN @EnforcementState = 2013 THEN 'Waiting for user logon'
WHEN @EnforcementState = 2014 THEN 'Waiting To Install'
WHEN @EnforcementState = 2015 THEN 'Waiting Retry'
WHEN @EnforcementState = 2016 THEN 'Waiting For Presentation Mode'
WHEN @EnforcementState = 2017 THEN 'Waiting For Orchestration'
WHEN @EnforcementState = 2018 THEN 'Waiting For Network'
WHEN @EnforcementState = 2019 THEN 'Pending App-V Virtual Environment Update'
WHEN @EnforcementState = 2020 THEN 'Updating App-V Virtual Environment'
WHEN @EnforcementState = 3000 THEN 'Requirements not met'
WHEN @EnforcementState = 3001 THEN 'Host Platform Not Applicable'
WHEN @EnforcementState = 4000 THEN 'Unknown'
WHEN @EnforcementState = 5000 THEN 'Deployment failed'
WHEN @EnforcementState = 5001 THEN 'Evaluation failed'
WHEN @EnforcementState = 5002 THEN 'Deployment failed'
WHEN @EnforcementState = 5003 THEN 'Failed to locate content'
WHEN @EnforcementState = 5004 THEN 'Dependency installation failed'
WHEN @EnforcementState = 5005 THEN 'Failed to download dependent content'
WHEN @EnforcementState = 5006 THEN 'Conflicts with another application deployment'
WHEN @EnforcementState = 5007 THEN 'Waiting Retry'
WHEN @EnforcementState = 5008 THEN 'Failed to uninstall superseded deployment type'
WHEN @EnforcementState = 5009 THEN 'Failed to download superseded deployment type'
WHEN @EnforcementState = 5010 THEN 'Failed to updating App-V Virtual Environment'
WHEN @EnforcementState is null THEN 'Unknown'
ELSE Cast(@EnforcementState AS VARCHAR(50))
END
RETURN (@ReturnString)
END
I then define the report, specifying a parameter of AssignmentID. The user sees “Software – Collection (Collection ID)”
-- First, get the assignment ID
SELECT SoftwareName + ' - ' + CollectionName + ' (' + CollectionID + ')' ApplicationCollection, AssignmentID FROM v_DeploymentSummary ds
WHERE ds.FeatureType = 1 AND SoftwareName <> ''
ORDER BY 1
I took the SQL behind the built in report ConfigMgr_XXX > Software Distribution – Application Monitoring > Software Distribution – Application Monitoring – Hidden > Application deployment detailed status and basically replaced the IF – ELSE IF… structure with UNION ALL (removing some fields I didn’t think were all that relevant, adding others).
-- Then pass the assignment ID to the following
DECLARE @PolicyModelID INT
DECLARE @SoftwareName VARCHAR (1000)
DECLARE @CollectionName VARCHAR (1000)
SELECT @PolicyModelID = PolicyModelID, @SoftwareName = SoftwareName, @CollectionName = CollectionID + ' - ' + CollectionName
FROM vDeploymentSummary
WHERE AssignmentID = @AssignmentID
SELECT
@SoftwareName AS SoftwareName
, @CollectionName AS CollectionName
, AppState.UserName
, AppState.MachineName
, s.Full_Domain_Name0
, CASE WHEN AppState.StatusType = 1 THEN 'Success' ELSE 'Waiting' END StatusType
, '' AS RequirementName
, 0 AS RequirementType
, '' AS CurrentReqValue
, NULL AS ErrorCode
, NULL AS HexErrorCode
, AppState.EnforcementState
, CM_Supplemental.dbo.EnforcementState(AppState.EnforcementState) EnforcementStateText
, NULL AS IsDependencyRule
, '' AS UniqueRequirementName
, 0 AS RuleID
FROM vAppDeploymentAssetDetails AppState inner join v_R_System s ON s.ResourceID = AppState.MachineID
WHERE
AppState.PolicyModelID = @PolicyModelID AND
AppState.AssignmentID = @AssignmentID AND
AppState.StatusType in (1, 2) AND
AppState.StatusType = AppState.AppStatusType
UNION ALL
SELECT
@SoftwareName AS SoftwareName
, @CollectionName AS CollectionName
, AppState.UserName
, AppState.MachineName
, s.Full_Domain_Name0
, 'Requirements Not Met' AS StatusType
, AppState.RequirementName
, AppState.RequirementType
, AppState.CurrentReqValue
, '' AS ErrorCode
, '' AS HexErrorCode
, AppState.EnforcementState
, CM_Supplemental.dbo.EnforcementState(AppState.EnforcementState) EnforcementStateText
, AppState.IsDependencyRule
, AppState.UniqueRequirementName
, AppState.RuleID
FROM vAppDeploymentRNMAssetDetails AppState inner join v_R_System s ON s.ResourceID = AppState.MachineID
WHERE
(AppState.PolicyModelID = @PolicyModelID) AND
(AppState.AssignmentID = @AssignmentID) AND
(AppState.StatusType = 3) AND
(AppState.StatusType = AppState.AppStatusType)
UNION ALL
SELECT
@SoftwareName AS SoftwareName
, @CollectionName AS CollectionName
, AppState.UserName
, AppState.MachineName
, s.Full_Domain_Name0
, 'Unknown' AS StatusType
, '' AS RequirementName
, 0 AS RequirementType
, '' AS CurrentReqValue
, NULL AS ErrorCode
, NULL AS HexErrorCode
, 4000 AS EnforcementState
, CM_Supplemental.dbo.EnforcementState(4000) EnforcementStateText
, NULL AS IsDependencyRule
, '' AS UniqueRequirementName
, 0 AS RuleID
FROM
vCIDeploymentUnknownAssetDetails AppState inner join v_R_System s on s.ResourceID = AppState.MachineID
WHERE
AppState.PolicyModelID = @PolicyModelID AND
AppState.AssignmentID = @AssignmentID
UNION ALL
SELECT
@SoftwareName AS SoftwareName
, @CollectionName AS CollectionName
, AppState.UserName
, AppState.MachineName
, s.Full_Domain_Name0
, 'Error' AS StatusType
, '' AS RequirementName
, 0 AS RequirementType
, '' AS CurrentReqValue
, AppState.ErrorCode
, replace (replace (master.dbo.fn_varbintohexstr(AppState.ErrorCode), '0xffffffff', '0x'), '0x00000000', '0x') HexExitCode
, AppState.EnforcementState
, CM_Supplemental.dbo.EnforcementState(AppState.EnforcementState) EnforcementStateText
, NULL AS IsDependencyRule
, '' AS UniqueRequirementName
, 0 AS RuleID
FROM
vAppDeploymentErrorAssetDetails AppState INNER JOIN v_R_System s ON s.ResourceID = AppState.MachineID
WHERE
AppState.PolicyModelID = @PolicyModelID AND
AppState.AssignmentID = @AssignmentID AND
AppState.StatusType = 5 AND
AppState.StatusType = AppState.AppStatusType
System Center Configuration Manager Notes 