Comparing AD Group Member Count & SCCM Collection Membership

Recently there was some concern that our SCCM 2012 R2 collections that referenced/queried Active Directory security groups weren't updating or syncing properly.

A simple way I wanted to confirm if this was the case, was to compare the number of assets in a particular AD group with its corresponding SCCM collection.

After some quick research, I discovered a relatively easy PowerShell method to count the number of members in the Active Directory group.


I opened an elevated Powershell window (aka, right-click & Run As Admin...) and ran the following:

PS C:\Windows\system32>  $users = Get-ADGroupMember -Identity 'ADGroupName'
PS C:\Windows\system32>  $users.Count
160
PS C:\Windows\system32>  $users |Export-Csv -path C:\temp\psexport.csv

The first line sets the $users variable.
The second line returns the member count.
The third line exports the members & info as a CSV to the path specified.


So I got the count of members in the AD group, which was 160, and went ahead and exported the information to a CSV file to open within Excel.

Next, I opened the SCCM collection to show the members (can also right click the collection name and click Show Members).  The collection was showing a count of 106 members in the collection information.

On the device membership list screen, do Ctrl + A to select all members, then Ctrl + C to copy the list.
Last, paste the copied data into Excel.


Finally, I used the "Ablebits Data Merge Two Tables" wizard tool to compare the two excel spreadsheets using the computer name as the key.

I had the SCCM info as the main table, and the AD info as the lookup table.



Using the tool's result column (MTW Status), I looked for all non-matching entries - i.e. entries that are in AD group that are not in the SCCM collection.

I found 54 assets that are in the AD group but do not show up in the SCCM collection.  In fact, these assets did not appear to be in SCCM at all.

Further investigation revealed these computers to be dead accounts in Active Directory, so they were inactive altogether and could actually be deleted from Active Directory since they were either disposed or no longer on the domain, etc.

In the end, we proved that SCCM was updating its membership based on the active computer accounts in the AD security group, as expected.



Sources:


Computer in AD Not Appearing in SCCM:
https://social.technet.microsoft.com/Forums/en-US/5923a17c-3994-4bd0-8811-ce2f2e8593f0/computer-in-ad-not-appearing-in-sccm?forum=configmanagerdeployment


How To Export a Collection to CSV in SCCM 2012:
https://community.spiceworks.com/topic/259637-how-to-export-a-collection-to-csv-in-sccm-2012


Powershell Count Members of an AD Group:
http://blogs.microsoft.co.il/skepper/2016/05/31/powershell-count-members-of-a-ad-group/


Ablebits Data Merge Two Tables in Excel Tool:
https://www.ablebits.com/excel-lookup-tables/index.php