Configuration Manager collection automation script problem (ADD-CMDEVICECOLLECTIONQUERYMEMBERSHIPRULE) RRS feed

  • Question

  • I am working on a PowerShell script to help with deployment of software and updates that are segmented by the user's department and their title. I have successfully gotten the creation of the user collections setup and the collection rules. The script is pretty awesome in that it saved me a ton of manual work (creating about 400 collections).

    Now I am trying to create corresponding device collections and I am running into an issue. I have narrowed my issue down to the syntax of my query. The query works great in SCCM but attempting to feed it to the Add-CMDeviceCollectionQueryMembershipRule command results in the following error:

    Add-CMDeviceCollectionQueryMembershipRule : 
    CMPSNoMask = True
    At line:11 char:17
    + ...             Add-CMDeviceCollectionQueryMembershipRule -CollectionName ...
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (Microsoft.Confi...shipRuleCommand:AddDeviceCollec...shipRuleCommand) [Add-CMDeviceCol...yMembershipRule], SmsConnectionException
        + FullyQualifiedErrorId : UnhandledException,Microsoft.ConfigurationManagement.Cmdlets.Collections.Commands.AddDeviceCollectionQueryMembershipRuleCommand

    I dismantled the query piece by piece and it seems that the problem is with the nested query used to find the primary user. Here is the query as it appears in the script:

     $DeviceQuery = "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 LEFT JOIN SMS_UserMachineRelationship ON SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId WHERE SMS_UserMachineRelationship.UniqueUserName IN (SELECT SMS_R_User.UniqueUserName FROM SMS_R_User INNER JOIN $TargetCollection ON $TargetCollection.ResourceID = SMS_R_User.ResourceID) AND (SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1)"           

    The value of $TargetCollection is derived from this line:

    $TargetCollection = "SMS_CM_RES_COLL_$($CMCollection.CollectionID)"

    where $CMCollection is defined by:

    $CMCollection = Get-CMUserCollection -Name $DEPTCollectionName

    My first thought was that the variables were causing the issue but I removed the variables from the query and replaced with only text and the same error occurred. So then I removed the everything after the WHERE and there were no errors and a collection rule was created (just didn't have the important criteria).

    My problem may be with how I built the query so let me explain what my goal is:

    1.) populate a user collection with users who are assigned a licensed application. User collection membership allows the users to "see" the "available" application deployment in Software Center

    2.) automatically populate a device collection with devices for whom the primary user (device affinity) is associated with a user in the user collection created in step one. This collection has a "required" deployment for the same licensed application targeting it. This is done in case the user forgets to install the licensed program or they get a new computer and it works fairly well with the manually generated collections.

    So, my questions are two-fold:

    1.) Why am I getting the error when I try to use this query to create a new query rule via PowerShell

    2.) Is there a better way to create a query that refers back to a user collection and determines the primary devices for those users?

    I appreciate any help anyone can provide.

    Thank you,


    Friday, October 30, 2020 9:02 PM

All replies

  • When you post code, error messages, sample data or console output format it as code, please.  How to Use the Code Feature in a TechNet Forum Post

    You can go back edit your post and fix the formatting - you don't have to create a new one.
        Thanks in advance.

    Live long and prosper!


    Friday, October 30, 2020 9:34 PM