SCCM User based deployment SQL Query

Question

Hi all,

Currently we are using below query to get deployment status for device based, can someone help to change this query as user based

SELECT
SYS.Name0 AS 'Machine Name',
CASE
WHEN CAS.EnforcementState = '1000' THEN 'Success'
WHEN CAS.EnforcementState = '1001' THEN 'Already Compliant'
WHEN CAS.EnforcementState = '1002' THEN 'Simulate Success'
WHEN CAS.EnforcementState = '2000' THEN 'In progress'
END AS 'Status',
--CAS.EnforcementState AS ' Status',
CAS.LastModificationTime AS 'Last Status Date/Time',
CAS.Descript AS 'Advert ID'

FROM v_R_System SYS
JOIN vAppDeploymentResultsPerClientMachine CAS ON SYS.ResourceID = CAS.ResourceID

Where
CAS.Descript LIKE '%' and Name0 IN ('XXXX',
'YYYY')

Answer 1

@Sengo Marimuthu
Thank you for posting in Microsoft Q&A forum.
We can replace "v_R_System" to "v_R_User" and replace "vAppDeploymentResultsPerClientMachine" to "vAppDeploymentResultsPerClientUser".

SELECT  
us.Name0 AS 'User Name',  
CASE  
WHEN CAS.EnforcementState = '1000' THEN 'Success'  
WHEN CAS.EnforcementState = '1001' THEN 'Already Compliant'  
WHEN CAS.EnforcementState = '1002' THEN 'Simulate Success'  
WHEN CAS.EnforcementState = '2000' THEN 'In progress'  
END AS 'Status',  
--CAS.EnforcementState AS ' Status',  
CAS.LastModificationTime AS 'Last Status Date/Time',  
CAS.Descript AS 'Advert ID'  
FROM v_R_User us  
JOIN vAppDeploymentResultsPerClientUser CAS ON us.ResourceID = CAS.ResourceID  
Where   
CAS.Descript LIKE '%' and Name0 IN ('XXXX',  
'YYYY')  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

do we have a enforcement state full details logged somewhere for user based deployments