Deployment Status SQL

Question

Hi Team.

I need to group and submit a report of all teams that are in compliant, in progress, error and unknown status, in sql can be done? The filter can be the title of the display e.g. the AssignmentName.

I was reviewing the native sccm reports but none of them meet the requirements.

Thanks for your help.

Answer 1

@Rafael Aguilar
How about this query:
select
vrs.Name0,
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
sn.StateName as LastEnforcementState
from v_CIAssignment a
join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
join v_R_System vrs on vrs.ResourceID = assc.ResourceID

Answer 2

Hi @Rafael Aguilar ,

Have you tried with Compliance 9 - Overral health and compliance report? It gives you the following with more details:

For more reports: https://learn.microsoft.com/en-us/mem/configmgr/sum/deploy-use/monitor-software-updates#recommended-software-updates-reports

Regards,

---

Youssef Saad | New blog: https://youssef-saad.blogspot.com
Please remember to ** “Accept answer” ** for useful answers, thank you!

Answer 3

@Rafael Aguilar
Thank you for posting in Microsoft Q&A forum.
Check if this query meet your requirements:

Select  
Vaa.AssignmentName as 'DeploymentName',  
Right(Ds.CollectionName,3) as 'Stage',  
Li.Title as 'SUGroupName',  
CASE when Vaa.DesiredConfigType = 1 Then 'Install' when vaa.DesiredConfigType = 2 Then 'Uninstall' Else 'Others' End as 'DepType',  
Ds.CollectionName as 'CollectionName',  
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose',  
Ds.DeploymentTime as 'AvailableTime',  
Ds.EnforcementDeadline as 'RequiredTime',  
Ds.NumberTotal as 'Target',  
Ds.NumberSuccess as 'Success',  
Ds.NumberInProgress as 'Progress',  
Ds.NumberErrors as 'Errors',  
Ds.NumberOther as 'Others',  
Ds.NumberUnknown as 'Unknown',  
case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then '0' Else (round(Ds.NumberSuccess/ convert (float,Ds.NumberTotal)*100,2)) End as 'Success%',  
DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays',  
DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays',  
DateDiff(D,Ds.CreationTime, GetDate()) as 'CreatedDays',  
Vaa.CreationTime as 'CreationTime',  
Vaa.LastModificationTime as 'LastModifiedTime',  
Vaa.LastModifiedBy as 'LastModifiedBy'  
from v_DeploymentSummary Ds  
left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID  
left join v_AuthListInfo LI on LI.ModelID = Ds.ModelID  
Where Ds.FeatureType = 5  

---

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 4

Thank you AllenLiu-MSFT

I am trying to run your query but supplying the devices.

I'm not very experienced in SQL but I'm doing this:

JOIN v_R_System m ON m.ResourceID = uc.ResourceID and isnull(m.Obsolete0, 0) = 0

INNER JOIN (SELECT CI_ID, ResourceID, StateID, StateType, LastEnforcementStatusMsgID FROM v_UpdateState_Combined) uc ON uc.CI_ID = aci.CI_ID

But I cannot combine the attributes to extract the hostname.

Thanks for your help.

Answer 5

For the moment I have not managed to insert in the consultation to bring me the list of equipment because I have to make joints.

I need to join v_R_System's resourceID with other views.

I'm not very experienced in SQL

Thank you