question

RajinderKumar-5487 avatar image
0 Votes"
RajinderKumar-5487 asked SherryKissinger-ECM commented

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

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.

mem-cm-generalsql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AllenLiu-MSFT avatar image
0 Votes"
AllenLiu-MSFT answered SherryKissinger-ECM commented

Hi, @RajinderKumar-5487
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.


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your reply. Only one asset did not show in the collection. Even if I am going to Deployments in the SCCM console and trying to get the data it shows for 48 or 58 assets. I tried using different reports which are available under reporting in SCCM console but get the same data. We would be changing the asset numbers in the same collection and want only the updated assets's data for TS deployment.

0 Votes 0 ·

The current Query that I am using for getting deployment status is

Declare @AdvertID varchar(16)
set @advertID = 'ETS20623' -- Use query below to view monitored advertisements. Put in the deployment ID here.


Select Getdate() as 'Report Date', cas.advertisementid, pkn.Name as 'Package name', coln.Name as 'Collection name',
cas.resourceid, rsys.AD_Site_Name0 as 'AD Site Name', rsys.Netbios_Name0 as 'Asset Number', scan.LastHWScan,
LastState, LastStateName as 'Installation Status', LastStatusTime
from v_ClientAdvertisementStatus cas
inner join (Select advertisementid, adv.collectionid, coll.name from v_Advertisement adv
inner join v_Collection coll on coll.CollectionID=adv.CollectionID

putting the query in 2 parts as not allowing more than 1000 characters

0 Votes 0 ·

where AdvertisementID = @advertID) coln on coln.AdvertisementID = cas.AdvertisementID
inner join (Select advertisementid, adv.PackageID, pkg.name from v_Advertisement adv
inner join v_package pkg on pkg.PackageID = adv.PackageID where AdvertisementID = @advertID) pkn on pkn.AdvertisementID = cas.AdvertisementID
inner join v_R_System rsys on rsys.resourceid = cas.ResourceID
left join v_GS_WORKSTATION_STATUS scan on scan.ResourceID = cas.ResourceID
where cas.advertisementid = @AdvertID

0 Votes 0 ·

I didn't test this; but try adding this to under your "where" section...
and
cas.resourceid in
(
Select resourceid from v_ClientCollectionMembers ccm where ccm.collectionID =
(
Select CollectionID from v_deploymentSummary where OfferID=@AdvertID
)
)

In theory, that should limit the results to only those resource ids which currently happen to be members of the collectionid which is the target of @AdvertID

0 Votes 0 ·