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
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
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.
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.
- 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]
join v_r_system_valid sys1 on sys1.resourceid=lgm.resourceid
where lgm.name0 = ‘Administrators’
order by sys1.netbios_name0, lgm.name0, lgm.account0
- 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.
- 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.
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.
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.