Combined Application Deployment Status Report

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s