Custom Report of Applications pushed to a specific collection

Question

Hi guys!

I am new in SCCM and I am trying to generate a custom report that will return the status (Success, Error and etc) of all the applications pushed to a specific device collection. I did followed this article from Anoop, and by changing it a bit I managed to get the partial result I am expecting, but on the report I also want to add the application name as well with the error code or message in case the status is not success

 

I have done a small research but could not find anything. I would appreciate if anyone could shed some light on how to achieve it.

 

Thank you very much!

 

in progress 0
Leandro Vasconcelos Ferreira 2023-03-28T14:24:40+05:30 2 Answers 12 views Beginner
0

Answers ( 2 )

  2. Debabrata Pati
    1
    2023-03-28T17:32:57+05:30
    Reply

    Pleas follow this blog post https://www.anoopcnair.com/sccm-application-deployment-custom-report-sql-query-configmgr/ and see if it helps.

    Also find the below SQL query.

    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 like ‘%GOOGLE%’
    order by LastComplianceMessageTime Desc

Leave an answer

Sorry, you do not have permission to answer to this question .