SCCM 2012 | Collection Query | Auto Add Machines but they should not be present in other collection

sccmsccm-2012sccm-2012-r2

I have three collections SCCM 2012:

Global Domain – Test (I have two machines here already)
Global Domain – 1 (i have 10 machines here already)
Global Domain – 2 (Query should automatically add machine here)

I need your help in creating a query, for the collection "Global Domain – 2".

What the query has to do is,

Find all the machines with the naming convention "SVDS"
Ensure the machine that are already present in the collection "Global Domain – Test" and "Global Domain – 1" are skipped
Add the rest to "Global Domain – 2"

Can you help with the query please ?

My query:

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 where SMS_R_System.SystemOUName = "somthing.com/DOMAIN"

Question is How to exclude the machines which are already member of "Global Domain – Test" and "Global Domain – 1"

Best Answer

Use the Exclude Collections rule (now part of SCCM 2012) and exclude collections "Global Domain - Test" and "Global Domain - 1" from your "Global Domain - 2" device collection.

Adding the 2 exclude rules will exclude any child members, even if they are targeted by the "Global Domain - 2" query.

http://blogs.technet.com/b/configmgrdogs/archive/2012/04/24/include-exclude-collections-rule.aspx

Related Solutions

How to query SCCM for all collections that a user is a member of

Ok, after more MSDN reading than I ever care to do again, here's (a messy) VBScript that I managed to pull together to pull the relevant out of SCCM:

option explicit

DIM strSCCMServer, objSCCM
DIM strUserName, strDomain
DIM strSMSUserID
'Central Site Server
strSCCMServer = "SCCMSERVER01"
'Active Directory domain name
strDomain = "DOMAIN_NAME"
strUserName = InputBox ("Enter User Name")


'Find Site Code
DIM objLoc, Results, Loc
Set objLoc = CreateObject("WbemScripting.SWbemLocator")
Set objSCCM = objLoc.ConnectServer(strSCCMServer, "root\sms")
Set Results = objSCCM.ExecQuery ("SELECT * From SMS_ProviderLocation WHERE ProviderForLocalSite = true")
For each Loc in Results
    If Loc.ProviderForLocalSite = True Then
        Set objSCCM = objLoc.ConnectServer(Loc.Machine, "root\sms\site_" & Loc.SiteCode)
    End If
Next

'Find domain user accounts for strUserName
strSMSUserID = GetUserResourceID(strUserName, strDomain)
If (strSMSUserID = "") Then
    wscript.echo "Error: no account found in " & strDomain & " for userID " & strUserName
    wscript.quit
Else
    wscript.echo strDomain & "\" & strUserName & " = " & strSMSUserID
End If


'Find all direct collection memberships of this account
DIM colCollIDs, objCollResID
Set colCollIDs = objSCCM.ExecQuery ("select * from SMS_CollectionMember_a where ResourceID='" & strSMSUserID & "'")
for each objCollResID in colCollIDs
    DIM instColl
    Set instColl = objSCCM.Get ("SMS_Collection.CollectionID=""" & objCollResID.CollectionID &"""")
    wscript.echo objCollResID.CollectionID & " = " & instColl.Name
next

'Obtain the SMS resource ID for a user account in a domain
Function GetUserResourceID(strUser, strDomain)
    DIM objResID, colResourceIDs
    Set colResourceIDs = objSCCM.ExecQuery ("select ResourceID from SMS_R_User where UserName = '" & strUser &"' AND WindowsNTDomain = '"& strDomain &"'")
    for each objResID in colResourceIDs
        GetUserResourceID = objResID.ResourceID
    next
End Function

SCCM Global Condition WQL Query Type Mismatch (wbemErrTypeMismatch – 0x80041005)

The Global Condition dialog is probably the most unintuitive part of SCCM I've seen so far.

Give this a try:

recreate your Firefox 2 Global Condition the same way, but this time in the WQL Query Where Clause field at the bottom, put: ProductName like "%Firefox%"
In the Requirements tab of your Application's Deployment Type, use the Firefox 2 Global Condition, but change the Rule Type to Existential

Best Answer

Related Solutions

How to query SCCM for all collections that a user is a member of

SCCM Global Condition WQL Query Type Mismatch (wbemErrTypeMismatch – 0x80041005)

Related Topic