Deployment Status SQL

Rafael Aguilar 496 Reputation points
2020-12-18T12:23:18.887+00:00

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.

49487-image.png

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

Thanks for your help.

Microsoft Configuration Manager Updates
Microsoft Configuration Manager Updates
Microsoft Configuration Manager: An integrated solution for for managing large groups of personal computers and servers.Updates: Broadly released fixes addressing specific issue(s) or related bug(s). Updates may also include new or modified features (i.e. changing default behavior).
960 questions
{count} votes

Accepted answer
  1. AllenLiu-MSFT 40,316 Reputation points Microsoft Vendor
    2020-12-23T07:06:18.817+00:00

    @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

    0 comments No comments

6 additional answers

Sort by: Most helpful
  1. Youssef Saad 3,401 Reputation points
    2020-12-18T14:23:37.68+00:00

    Hi @Rafael Aguilar ,

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

    49533-softwareupdatesreports.png

    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!

    0 comments No comments

  2. AllenLiu-MSFT 40,316 Reputation points Microsoft Vendor
    2020-12-21T07:11:03.527+00:00

    @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.

    0 comments No comments

  3. Rafael Aguilar 496 Reputation points
    2020-12-21T11:42:21.11+00:00

    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.


  4. Rafael Aguilar 496 Reputation points
    2020-12-22T18:09:56.173+00:00

    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

    0 comments No comments