Get the Deployment Status of ConfigMgr Applications Query

Query Type 1




select distinct

aa.ApplicationName,

ae.AssignmentID,

aa.CollectionName as 'Target Collection',

ae.descript as 'Deployment Type Name',

s1.netbios_name0 as 'Computer Name',

ci2.LastComplianceMessageTime,

ae.AppEnforcementState,

case when ae.AppEnforcementState = 1000 then 'Success'

when ae.AppEnforcementState = 1001 then 'Already Compliant'

when ae.AppEnforcementState = 1002 then 'Simulate Success'

when ae.AppEnforcementState = 2000 then 'In Progress'

when ae.AppEnforcementState = 2001 then 'Waiting for Content'

when ae.AppEnforcementState = 2002 then 'Installing'

when ae.AppEnforcementState = 2003 then 'Restart to Continue'

when ae.AppEnforcementState = 2004 then 'Waiting for maintenance window'

when ae.AppEnforcementState = 2005 then 'Waiting for schedule'

when ae.AppEnforcementState = 2006 then 'Downloading dependent content'

when ae.AppEnforcementState = 2007 then 'Installing dependent content'

when ae.AppEnforcementState = 2008 then 'Restart to complete'

when ae.AppEnforcementState = 2009 then 'Content downloaded'

when ae.AppEnforcementState = 2010 then 'Waiting for update'

when ae.AppEnforcementState = 2011 then 'Waiting for user session reconnect'

when ae.AppEnforcementState = 2012 then 'Waiting for user logoff'

when ae.AppEnforcementState = 2013 then 'Waiting for user logon'

when ae.AppEnforcementState = 2014 then 'Waiting to install'

when ae.AppEnforcementState = 2015 then 'Waiting retry'

when ae.AppEnforcementState = 2016 then 'Waiting for presentation mode'

when ae.AppEnforcementState = 2017 then 'Waiting for Orchestration'

when ae.AppEnforcementState = 2018 then 'Waiting for network'

when ae.AppEnforcementState = 2019 then 'Pending App-V Virtual Environment'

when ae.AppEnforcementState = 2020 then 'Updating App-V Virtual Environment'

when ae.AppEnforcementState = 3000 then 'Requirements not met'

when ae.AppEnforcementState = 3001 then 'Host platform not applicable'

when ae.AppEnforcementState = 4000 then 'Unknown'

when ae.AppEnforcementState = 5000 then 'Deployment failed'

when ae.AppEnforcementState = 5001 then 'Evaluation failed'

when ae.AppEnforcementState = 5002 then 'Deployment failed'

when ae.AppEnforcementState = 5003 then 'Failed to locate content'

when ae.AppEnforcementState = 5004 then 'Dependency installation failed'

when ae.AppEnforcementState = 5005 then 'Failed to download dependent content'

when ae.AppEnforcementState = 5006 then 'Conflicts with another application deployment'

when ae.AppEnforcementState = 5007 then 'Waiting retry'

when ae.AppEnforcementState = 5008 then 'Failed to uninstall superseded deployment type'

when ae.AppEnforcementState = 5009 then 'Failed to download superseded deployment type'

when ae.AppEnforcementState = 5010 then 'Failed to updating App-V Virtual Environment'

End as 'State Message'

from v_R_System_Valid s1

join vAppDTDeploymentResultsPerClient ae on ae.ResourceID=s1.ResourceID

join v_CICurrentComplianceStatus ci2 on ci2.CI_ID=ae.CI_ID AND

ci2.ResourceID=s1.ResourceID

join v_ApplicationAssignment aa on ae.AssignmentID = aa.AssignmentID

where ae.AppEnforcementState is not null and aa.ApplicationName='$ApplicationName'

order by LastComplianceMessageTime Desc

Coutesy Link : https://smsagent.wordpress.com/tag/configmgr-application-deployments/

Query Type 2

select  MIN(cit.CI_ID) as CI_ID, MIN(cit.CI_ID) as ParentID, MIN(co.SiteID) as TargetCollectionID, cia.AssignmentID, ci.ModelID as PolicyModelID,
         cm.MachineID as ResourceID, 0 as UserResource, MIN(cil.DisplayName) as Descript, MIN(cia.StartTime) as StartTime,
         MIN(cia.LastModificationTime) as LastModificationTime, MAX(ccs.ComplianceState) as ComplianceState, 
         MAX(dbo.fn_GetAppState(ccs.ComplianceState,ccs.EnforcementState,cia.OfferTypeID,1,ccs.DesiredState,ccs.IsApplicable)) as EnforcementStateValue,
         EnforcementStateName = CASE
         when (MIN(dbo.fn_GetAppState(ccs.ComplianceState,ccs.EnforcementState,cia.OfferTypeID,1,ccs.DesiredState,ccs.IsApplicable)) between 1000 and 1999) then 'Success'
         when (MIN(dbo.fn_GetAppState(ccs.ComplianceState,ccs.EnforcementState,cia.OfferTypeID,1,ccs.DesiredState,ccs.IsApplicable)) between 2000 and 2999) then 'In Progress'
         when (MIN(dbo.fn_GetAppState(ccs.ComplianceState,ccs.EnforcementState,cia.OfferTypeID,1,ccs.DesiredState,ccs.IsApplicable)) between 3000 and 3999) then 'Requirements Not Met'
         when (MIN(dbo.fn_GetAppState(ccs.ComplianceState,ccs.EnforcementState,cia.OfferTypeID,1,ccs.DesiredState,ccs.IsApplicable)) between 4000 and 4999) then 'Unknown'
         when (MIN(dbo.fn_GetAppState(ccs.ComplianceState,ccs.EnforcementState,cia.OfferTypeID,1,ccs.DesiredState,ccs.IsApplicable)) between 5000 and 5999) then 'Error'
          else 'NULL'
         --    
         end,        
         MIN(cia.OfferTypeID) as OfferTypeID, MIN(ccs.CIVersion) as Revision
         from CI_CIAssignments cia
         INNER JOIN CI_AssignmentTargetedCIs cit ON cia.AssignmentID = cit.AssignmentID
         INNER JOIN Collections co on co.CollectionID = cia.TargetCollectionID
         INNER JOIN CI_ConfigurationItems ci ON ci.CI_ID = cit.ActualCI_ID  -- ci = intent 
         INNER JOIN CI_LocalizedProperties cil on cit.CI_ID = cil.CI_ID
         INNER JOIN vCollectionMembers cm ON cia.TargetCollectionID = cm.CollectionID
         inner join vAppDeploymentResultsPerClientMachine vAD on Vad.CI_ID = cit.CI_ID
         LEFT JOIN CI_CurrentComplianceStatus ccs ON ccs.ModelID = ci.ModelId and ccs.ItemKey = cm.MachineID
         where cia.AssignmentType=2 and cia.IsTombstoned=0 and cm.IsClient=1 --and vad.EnforcementState is not NULL
         group by cia.AssignmentID, ci.ModelID, cm.MachineID