SQLTeam.com | Weblogs | Forums

Application State custom report

Hello,
I've been tasked with supplying the above custom report. We've gone through the built-in reports, but they don't give exactly what they need. Now I found this code elsewhere on the Net:

select distinct
aa.ApplicationName,
ae.AssignmentID,
aa.CollectionName as 'Target Collection',
ae.descript as 'Deployment Type Name',
s1.netbios_name0 as 'Computer Name',
s1.Distinguished_Name0 as 'OU'
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

It does seem perfect, but I don't have the vAppDTDeploymentResultsPerClient view to extract from in the first place. I'm not SQL savvy enough to try and create a view as I've never done it. Has anyone ever handled a request like this? Is there a better way?

Thanks,
Jeff

Be careful with code found on the Internet!
vAppDTDeploymentResultsPerClient is a view that depends on one or more underlying tables (or other views). The poster of this code would know what that is. OTOH since it is central to what you are doing, the question would be where are you storing the ApplicationName, AssignmentID, AppEnforcementState and ResourceID columns? Where do you expect to get that data from?