SOLVED SQL Query for "User" Based Deployment Status

[zcaa0g]

Hello, I largely tried to avoid user-based deployments in favor of device-based deployments, but I am trying to get the deployment status of an application that is deployed to a user-based collection.

I worked with the below code to try to get results from a user-based deployment, but no success. Does anyone happen to have an SQL query for a user based deployment status of an application model package or have an idea to get the one below working? Thanks.

SELECT distinct
vrs.Name0 [Computer Name], vgos.Caption0 [OS],vrs.User_Name0 [User Name],
IIf([EnforcementState]=1001,'Installation Success',
IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Installation Success',
IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress', IIf([EnforcementState]>=3000 And [EnforcementState]<4000,'Requirements Not Met ', IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown', IIf([EnforcementState]>=5000 And [EnforcementState]<6000,'Error','Unknown')))))) AS Status
FROM dbo.v_R_User AS vrs
INNER JOIN (dbo.vAppDeploymentResultsPerClient
INNER JOIN v_CIAssignment
ON dbo.vAppDeploymentResultsPerClient.AssignmentID = v_CIAssignment.AssignmentID)
ON vrs.ResourceID = dbo.vAppDeploymentResultsPerClient.ResourceID
INNER JOIN dbo.fn_ListApplicationCIs(1033) lac
ON lac.ci_id=dbo.vAppDeploymentResultsPerClient.CI_ID
INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS vgws
ON vgws.ResourceID=vrs.resourceid
INNER JOIN v_FullCollectionMembership coll
ON coll.ResourceID = vrs.ResourceID
INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS vgos
ON vgos.ResourceID = vrs.ResourceID
WHERE lac.DisplayName= 'Test App'
and CollectionName = 'Test Users'

 

[Prajwal Desai]

@Garth - Any idea ?.

 

[Garth]

There are a large number of problems with this query starting with v_r_user.resourceID != to computer details.

Why use so many IIF statements and not a Case statement?

why use a brackets within an inner join? aka what I'm, i missing as to why you did this.

 

[Kush Gupta]

Even I am also looking SQL Query to pull user based deployment status. Much appreciation If someone can help me?

 

[Garth]

Even I am also looking SQL Query to pull user based deployment status. Much appreciation If someone can help me?

Have you looked at the built-in reports for this?

 

[Kush Gupta]

Yes I am using build report but I am looking SCCM SQL query to create custom report for user deployment.

 

[Garth]

Yes I am using build report but I am looking SCCM SQL query to create custom report for user deployment.

Again what is wrong with using the query from the built-in reports? aka why re-invent the wheel?