SQL query to get deployment result using a collection name or ID rather than deployment ID

Rajinder Kumar 1 Reputation point
2021-03-24T21:53:09.577+00:00

Hello Everyone,

I am working as an SCCM Admin and using SCCM 2002 for my client. We are upgrading windows from 1709 to 1909. For that we are using 2 Task sequence - one for pre-cache and other for upgrade. There are 2 separate collections on which the TS are deployed. One TS on one collection, the other TS on the other collection. Now, I have a query which gives the deployment status (success, failure, in progress) using the deployment ID. But almost every other day the assets are changed in the collection (query based collection linked to an AD group - Assets are changed in the AD group and they update in the SCCM collection). However, the deployment status does not show it for the updated Assets, it shows for 50-58 assets. The client is after me to come up with a query which can run collection wise (using collection ID as there is only one deployment on that collection) and not deployment wise (which gives results of older assets in the collection as well). How can I fix this? A little urgency here. I am and will use the query to subscribe it to run on a daily basis.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,798 questions
Microsoft Configuration Manager
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AllenLiu-MSFT 40,316 Reputation points Microsoft Vendor
    2021-03-25T06:27:02.027+00:00

    Hi, @Rajinder Kumar
    Thank you for posting in Microsoft Q&A forum.
    I'd like to confirm something with you for better understanding.
    For the updated Assets, are they actually get the deployment and update the status(success, failure, in progress)?
    We may check it from SCCM console Monitoring\Deployments, select the deployment and click View Status.

    And what is your current sql query for the deployment status?

    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.