Collecting local group membership/local admin details via Compliance Settings in ConfigMgr

Hi all! I wrote this some time ago back before my environment rebuild and content migration. As a result, some of this is not necessary (for instance, Report Builder worked out of the box on Server 12 R2 for me), but thought this was worth sharing with you guys.

I was asked to create a report to show what accounts and groups were inside of the Administrators group on all of our client systems. I found a post by Sherry Kissinger back with SCCM’07 I believe, which lead me to the post below, updated with logging for SCCM2012.. Shout out to Sherry for this post, please reference it for the MOF for extending hardware inventory and further information: https://mnscug.org/blogs/sherry-kissinger/244-all-members-of-all-local-groups-configmgr-2012 .

This was written for a CAS running Configuration Manager 2012 SP1 on a Windows Server 2008 R2 host.

Preparing the console host for report builder 3.0

Please note that I only had to do this when running SQL ’08 on Server ’08 R2. Leaving instructions here for reference.  Worked fine with Server ’12 R2 and SQL ’16 out of the box. The first thing we want to do is get SQL Server 2008 R2 Report Builder 3.0 working. By default, you’re going to get a message saying whatever MP you’re connected to is missing the click-to-run application. You will want to do this on your MP, indeed, as we’ll use it to build our report later. Contrary to the message, you need to do this on any client running the console that you intend to edit reports from in the future as well.

  • Install .NET Framework 3.5.1
  • Install Report Builder 3.0
  • Open Notepad as an administrator and open the console config
    • Path: C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\Microsoft.ConfigurationManagement.exe.config
  • Under “ReportBuilderMapping”, change the last two (of four) lines from 2_0_0_0 to 3_0_0_0 and save.
  • Launch the SCCM console and go to Monitoring -> Reporting -> Reports, then verify that clicking “Create Report” successfully launches Report Builder 3.0

Creating the configuration item

lgm_1

We need to create a custom WMI namespace to hold the information we’re looking to obtain. We do this by creating a configuration item.

  • Assets and Compliance -> Compliance Settings -> Configuration Items -> Create Configuration Item

lgm_7

lgm_3
This script was written by Sherry Kissinger and can be found via the link to her blog I mentioned earlier.

I’m not currently enforcing any compliance rules with this since I am looking to monitor, not remediate with this setting. This should work as long as WMI isn’t broken, in which case, you’ll have bigger issues with the client than just compliance settings.

lgm_6

Creating the configuration baseline

Local Group Membership is the only configuration item/baseline in use in my environment right now. This can contain all of your configuration items in the future, I have created one for top-level items (company-wide) and one for each site with their own SCCM admin as I use RBAC and allow the site admins to do their own thing with daily operations in general. This allows me to scope this out to keep it out of the hands of less-experienced admins. Note that you can include other configuration baselines in a configuration baseline, so you can create additional baselines for settings/configuration items that only apply to subsets of your All ConfigMgr Clients collection.

  • Assets and Compliance -> Compliance Settings -> Configuration Baselines -> Create Configuration Baseline
  • Add your configuration item and any other desired items (software updates, other configuration baselines, config items)
  • Deploy the newly created configuration baseline to your desired collection. I originally set our schedule to be simple, run every 1 days. I found that there was a dire need for this data to be current when Software Inventory ran (we schedule for once daily, but that time can vary with randomization and laptops), so have since changed to every 6 hours. When deployed, it’s my understanding that the clients will run it immediately, then your schedule will take effect.

Configuring the Hardware Inventory Classes

We now need to populate the new namespace with data. This data is gathered by the configuration item and submitted to the management point during Hardware Inventory. Please reference Sherry’s post for the MOF, you’ll use it to add the cm_LocalGroupMembers class. Afterward, you’ll just need to make sure the class is enabled in your client settings policy.

  • Administration -> Client Settings -> Client Settings – ABC -> Hardware Inventory -> Set Classes -> LocalGroupMembers (cm_LocalGroupMembers)

Building the report

We’re collecting the data now, we just need to create the report to view it. You can also create a subscription to this report. Note: I did this on the MP directly (or wherever your SQL is hosted)… if you create the report from a client, from my understanding you’ll have to import the sql server certificate and such… easier to just create on the MP.

lgm_9

  • Monitoring -> Reporting -> Reports -> Create Report
  • The report is created and opens in Report Builder 3.0
  • Right click Datasets, Add Dataset. On the Query tab, click “Use a dataset embedded in my report.”
  • Select the AutoGen datasource
  • Enter the following as a text query

[code language=”powershell”]select sys1.netbios_name0
,lgm.name0 [Name of the local Group]
,lgm.account0 as [Account Contained within the Group]
, lgm.category0 [Account Type]
, lgm.domain0 [Domain for Account]
, lgm.type0 [Type of Account]
from
v_gs_localgroupmembers0 lgm
join v_r_system_valid sys1 on sys1.resourceid=lgm.resourceid
where lgm.name0 = ‘Administrators’
order by sys1.netbios_name0, lgm.name0, lgm.account0
[/code]

  • Click “refresh fields”. You’ll be prompted for credentials, I used my reporting services account
  • The “Fields” tab will populate with the localgroupmembers0 fields. OK out of this dialog.

Let’s setup the report to display the data now.

lgm_2

  • Click “Table or Matrix”, then Dataset1, then Next
  • Drag all available fields to Values
  • (next, next, finish). Save your newly created report.

I created this report on my CAS to report across my 3 primaries.

lgm_4

You may have to play with the report to get the display to your liking, but the above steps gave me the following results from the console.

lgm_8

That’s it. I recently built a new hierarchy from the ground up and performed a content migration, which meant extending hardware inventory again and recreating all my custom reports. Still working with ConfigMgr CB v1607, Server ’12R2, and SQL 2016.

lgm_5

 

4 Thoughts on “Collecting local group membership/local admin details via Compliance Settings in ConfigMgr

  1. Hi Chris,
    Thanks for posting query for report building.
    I followed sherry-kissinger’s link and instruction but struggle to find meaningful report.
    I used your query to get the report. It gave me the Built-in Administrator account and Domain Admins Group.
    Is there a way to drill down to who’s in Domain Admins Group? Your screenshot seems to suggest it is possible.
    cheers,
    John

    1. Hi John,

      It sounds like your “Domain Administrators” group is a member of the “Administrators” group on one or more of your clients? The information harvested from clients will not contain the membership of the Domain Administrators group but you should be able to pull the membership for Domain Administrators from AD Users and Computers or “dsget group “Domain Administrators””.

  2. Hi Chris,
    Great post. Any idea of how I could modify the report to be scoped to a collection? I need to be able to point this to specific collections and being n SQL newbie I’m not sure where to begin.

    Cheers

    1. Thanks John! I totally understand where you’re coming from. Most of my SSRS work was done via trial-and-error along with intense Googling. Prajwal has a site full of great resources and covered this very task with one of his posts, linked here: https://prajwaldesai.com/how-to-add-collection-parameter-in-sccm-report/

      Hope it helps! Not sure how complex your environment is, but you’ll want to create this report on your top level site server to ensure you have visibility to all device collections.

Leave a Reply

Your email address will not be published. Required fields are marked *