query to create a collection based on the other collection deployment status as success . the deployment is package deployment.
Question
query to create a collection based on the other collection deployment status as success . the deployment is package deployment.
Lets say i have deployed a package A to collection A and i also have collection named B created with other deployment B. But collection B should get the machines added dynamically depending on the success rate of A
Answers ( 2 )
Try this one, get the advertisement ID of that particular package deployed and change accordingly. The below query will give all machines which have been success for given advertisement. You can get that limit to Collection also.
select sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client from sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID WHERE AdvertisementID = ‘*******’ and (LastStatusMessageID = 10009 or LastStatusMessageID = 10008)
This is the sample query for metering ..try to create one for your needs
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_SYSTEM inner join SMS_MonthlyUsageSummary on SMS_R_SYSTEM.ResourceID = SMS_MonthlyUsageSummary.ResourceID INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFile.MeteredFileID WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) > 100 AND SMS_MeteredFiles.RuleID = 16777418