Create SCCM device collection based on last logged on users who are members of an AD security group

active-directoryquerysccmsccm-2012wmi

I'm trying to create a device collection in SCCM 2012 which contains only the devices who are used by the users who are members of a certain User AD Security Group. I tried to accomplish this by first making a query for all the users in the group, which worked fine:

select SMS_R_User.UserGroupName, 
SMS_R_User.UserName 
from  SMS_R_User where SMS_R_User.UserGroupName = "Domain\\AD_Group"

Then I created a collection of devices with a query rule where the criteria was that if the last logged on user of the device was part of the subselected values of the first group query I made, then those devices would be added to the collection. The query rule:

select *  from  SMS_R_System 
where SMS_R_System.LastLogonUserName 
in (select SMS_R_User.UserGroupName, SMS_R_User.UserName 
from  SMS_R_User where SMS_R_User.UserGroupName = "Domain\\AD_Group")

However when I try to save the query rule Configuration Manager says that the query is not valid. I'm not exactly sure what I'm doing wrong. Maybe there is a better approach?

Best Answer

Try this one:

SELECT * FROM SMS_R_SYSTEM 
  INNER JOIN SMS_R_uSER 
  ON SMS_r_system.LastLogonUserName = SMS_R_uSER.FullUserName 
WHERE SMS_R_uSER.SecurityGroupName = 'Domain\\AD_Group';

Related Solutions

Powershell show users who are members of a group twice – once directly once indirectly

The following will walk a given parent group (name must be compatible with Get-ADGroupMember syntax) and populate two hashes. One hash will have user sAMAccountName as the key and a array of direct group memberships as the value. The second hash will contain keys of all processed groups.

It will correctly detect, and skip, circular group nestings (e.g. parent is child of child). You can comment out the Write-Host lines for less line noise in the console.

Import-Module ActiveDirectory

function Walk-Group ( $group ) {

    Get-ADGroupMember $group | Group-Object objectClass -ash | Foreach-Object {

        # Add each user to $users hash, add current group to their collection
        if ( $_.user ) {
            foreach ( $u in $_.user.GetEnumerator() ) {
                if ( $users[$u.sAMAccountName] ) {
                    $users[$u.sAMAccountName] += $group
                } else {
                    $users.Add( $u.sAMAccountName, @($group) )
                }
            }
        }

        # Recurse into each child group, skip if group is circular member
        if ( $_.group ) {
            foreach ( $g in $_.group.GetEnumerator() ) {
                if ( $groups[$g.Name] ) {
                    Write-Host "Existing:" $g.Name
                } else {
                    Write-Host "New Group:" $g.Name
                    $groups.Add( $g.Name, $true )
                    Walk-Group $g.Name
                }
            }
        }

    }
}

# Hash to collect user/group info.
$users = @{}

# Hash to collect processed groups.
$groups = @{}

# Root group to walk
Walk-Group "Department-staff"

# Display users with mulitple direct memberships
$users.GetEnumerator() | Where-Object { $_.Value.Count -gt 1 }

SCCM 2012 SP1 – DownloadContentFiles() failed with hr=0x80041013

Perhaps the REV site code was used for a temporary test installation of SCCM 2012? Perhaps not. Institutional knowledge doesn't have any record of the REV it and the migration we performed before I was hired.

This hunch was correct. I got a hold of my predecessor and apparently the first and unsuccessful attempt to migrate from SCCM 2007 to SCCM 2010 used the REV site code. How it managed to sit dormant in WMI all this time and why it got "activated" is a complete mystery to me.

I very carefully re-read the solution in this TechNet post which advised deleting the old namespaces and decided to try that. I kind of hesitate to mark this as an answer even though it did resolve this issue, it indicates that I implicitly approve of it, especially since I couldn't get anyone "official" at Microsoft to confirm whether or not this was a safe approach or what the consequences were for doing this. That being said, make sure you have complete backups of your SCCM server or at least a much more intimate knowledge of WMI before proceeding. You could very easily break everything doing this, especially if like me, you're not familiar with WMI and how deeply SCCM leverages it.

I used wbemtest to connect to the root\sms namespace on our SCCM server. From there I used the [Enum_Instances...] button and search for __NAMESPACE as the superclass. I deleted the entry for the REV site code. I then did the same Enum_Instances for the SMS_ProviderLocation as the superclass and deleted the old site code from that namespace. Re-running the Automatic Deployment Rules and reviewing the PatchDownloader.log showed the successful downloading of each Windows Update.

WBEMTEST __NAMESPACE

WBEMTEST SMS_ProviderLocation

I would greatly appreciate more information about how these namespaces are used by SCCM and exactly how this fixed the issue if anyone has more detailed information.

Best Answer

Related Solutions

Powershell show users who are members of a group twice – once directly once indirectly

SCCM 2012 SP1 – DownloadContentFiles() failed with hr=0x80041013

Related Topic