Hi Guys,

Im trying to get all users that are local admins of my network using sccm12.

How its possible?

Thank you.

Sherry's method ver.2 is great and it really works (http://myitforum.com/cs2/blogs/skissinger/archive/2010/04/25/report-on-all-members-of-all-local-groups.aspx)

I just want to add that when designing a report you must use v_GS_CM_LocalGroupMembers view instead of v_gs_localgroupmembers0 in the original post (at least that turned out to be in my case).

the steps or menues have changed in 2012, as i am not so familiar with sccm in general, i can not "map" this config to 2012 menues. can you give me hint how to?

thank you!

Klaus

In the CM12 console, on the left bottom, select "Assets and Compliance"

then on the left, select "Compliance Settings"

In there are Baselines and Configuration Items.  Now you should be able to follow the blog entry for "report on all members of all local groups" to create the Configuration Item, and then add that CI to a Baseline, and then assign that baseline to a collection.

To do the hardware inventory part, save the mof snippet in notepad to a file that ends in .mof (otherwise the import can't detect it).  Then bottom left, "Administration".  Then on the left, "Client Settings".  Right-click edit "Default Client Settings", on the left of that, Hardware Inventory.  "Set Classes", and then Import that mof snippet you just

Question about duplicating this in SCCM 2012. I'm creating a Configuration Baseline and in the "Specify settings for this operation system", I'm assuming the "Setting Type" should be 'Script' and the "Data type" should be 'String'. Is that correct?

I did change it to a 'String' value and deployed it. Now I'm getting a Non-Compliant report back. Can't quite see where the Non-Compliance is...

Sorry to revive an old thread, but I've got this working well except for one problem, some of our user accounts have special characters in the names (like ! and ~ and #) and the reports exclude these accounts.  Can this be tweaked to show them? 

Also, a request; the query to run this report on a specific collection. I have many many computers and need to adjust the scope of the report.

Thanks!

• Edited by Puck-Stopper Tuesday, October 30, 2012 2:17 PM

Hi,

I tried running the same steps in SCCM 2012 Sp1, but it does now Work. The following were performed

1. Created a baseline with the script

2. Deploy with a simple schedule for 1 day to the collections

3. Run machine policy and hardware inventory cycle

Regards,

Vinod

• Proposed as answer by vinod7 Tuesday, May 21, 2013 6:34 AM
• Unproposed as answer by vinod7 Tuesday, May 21, 2013 6:34 AM

Hi,

I got it. The following Post were used to get it work in SCCM 2012

http:// mnscug.org / blogs / sherry-kissinger / 244-all-members-of-all-local-groups-configmgr-2012

Thanksfull to Sherry for this wonderfull article.

regards,

Vinod

• Proposed as answer by Sherry KissingerMVP Tuesday, May 21, 2013 11:40 AM

Hi,

I got it. The following Post were used to get it work in SCCM 2012

http:// mnscug.org / blogs / sherry-kissinger / 244-all-members-of-all-local-groups-configmgr-2012

Thanksfull to Sherry for this wonderfull article.

regards,

Vinod

• Proposed as answer by Sherry Kissinger Tuesday, May 21, 2013 11:40 AM

Hello

Thanks for this useful post.

I follow every lines frol the post http://mnscug.org/blogs/sherry-kissinger/244-all-members-of-all-local-groups-configmgr-2012 but got stuck while importing the mof file.

When I try to import it i got an erro rmessage saying :

"The MOF file you tried to import could not be compiled. Ensure that the mof file contains Valid data. You can use the command line mofcomp utility to test the data"

running mofcomp -check give me a OK - no error.

running mofcomp localgroupmember.mof give me error :

Microsoft (R) MOF Compiler Version 6.1.7600.16385
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: Localgroupmember.mof
MOF file has been successfully parsed
Storing data in the repository...
An error occurred while creating object 2 defined on lines 6 - 13:
0X80041002 Class, instance, or property 'SMS_Class_Template' was not found.
Compiler returned error 0x80041002

Any help would be appreciate as I don't know how to handle such error. (didn't find something relevant in the forum)

Thx

Edit : the workaround is to add manually an inventory hardware classes while connected to the remote computer WMI repository.

Edit :the manual workaround doesn't seems to work.I got this erros in the Server Inventoryagent.log :

Collection: Class "CM_LocalGroupMembers" does not exist.

:(

• Edited by orlith Monday, July 29, 2013 11:57 AM info
• Proposed as answer by Jazii Tuesday, February 17, 2015 8:58 AM

You do not , ever, ever again, need to mofcomp any .mof file without the -check.  Ever.  don't do that anymore. please... especially NOT on a box you actually care about WMI on, like your primary site server.  Just... Don't.

Ok, now that that is out of the way, I think you have no problems whatsoever.

you say, "on the inventoryagent.log, you get class "cm_localgroupmembers" does not exist"

On the server.

Which... is probably just fine.  did that server ever run the DCM ConfigItem?  if it did, is that server also just so happens to be a Domain controller?  If so, then there are no local groups on a DC, so it can't, and will not run.  It shouldn't be able to, ever.

I'm actually thinking... it's been a few hours now since you posted the above.  Do you have anything in your v_gs_localgroupmembers0 view?  select * from v_gs_localgroupmembers0             I think it's just fine, and you just simply had to wait for a valid client to report.

Hi Sherry

You're right the Dcm is not applied to servers collections, only workstations collection.

Ok for the mofcomp without -check. What could be the issue ? (anyway I run it on my test environment)

You're right again, after 4 hours i got something in my view (v_gs_cm_localgroupmembers), only one computer where there should be 5 (and more than that, the computer on which i enforce/refresh everything doesn't appears)

Then thanks for your answer.

Do you have any idea on what is causing the "import mop issue (unable to compile mop file)" ? 

Is the manual workaround a good one ?

And finally  i don't understand why it could take so long for something to be written in the db when the hardware Inventory is schedule (in test) to run every hours, like the compliance settings, even if i refresh/enforce everything on the client side.

Sounds strange to me :)

thanks a lot

unable to compile the mof: that's because this is NOT SMS 2003 anymore.  It's two versions newer since the need to locally compile the mof--so the framework which used to be there (for SMS2003) is simply never imported on most ConfigMgr 2007, or (in  your case) ConfigMgr 2012 clients.  So... just stop doing it.  There are other implications when you compile a mof (and it succeeds) which might have unintended circumstances.  Like crashing your servers' ability to be a Primary site anymore.  Just sayin' I warned you.  If you must compile a mof, do so in a test environment, on a test system, on a system that you don't mind if you accidentally blow it up.

As for how long things take--only you can answer that, really.  It could be any number of reasons, from the workstation being busy doing a software inventory for 4 hours before it could do the hardware inventory, to backlogs on the MP inboxes, to backlogs on the MP transferring to the primary inboxes, to sql being paused during a backup, to... you get the idea. 

Hi Sherry I have run this baseline on our SCCM 2012 server and it has run on over 1000 clients so far.  What I am wanting to know is how do I get the information from that baseline as a report to give to our security department.

Thanks for any information on this.

I'm curious about this too.  But the report that I used in 2007 did not migrate properly to 2012.  I have no experience at all with Report Builder and cannot seem to figure out how to create a simple report to show the membership of the local admin group on a server.

Any suggestions or examples here would be great. I have done everything else and it's working fine, I just need to get a simple report that I can show to management.

Here is the query that I used in the 2007 report:

select lgm.name0 [Name of the Local Group]
,lgm.Account0 [Account]
,lgm.Category0 [Category of Account]
,lgm.Domain0 [Domain for the Account]
,lgm.Type0 [Type of Account]
from v_gs_localGroupMembers0 lgm
join v_r_system sys1 on sys1.resourceid=lgm.resourceid
where lgm.name0 = 'Administrators'
and sys1.Netbios_Name0 = @computername

I'm trying to reuse that query in the report builder wizard, but I'm not sure what to select at the Arrange Fields step...

EDIT: Figured it out. Needed to put all of the fields in the Values box.

• Edited by dpmaker Monday, December 02, 2013 8:54 PM

Hi,

We can use the following query as follows

SELECT DISTINCT SYS.Netbios_Name0, SYS.User_Name0, LocalAdminMembers.TimeStamp, LocalAdminMembers.Type0 as Object LocalAdminMembers.Account0, LocalAdminMembers.Domain0   FROM fn_rbac_GS_LocalAdminMembers0(@UserSIDs)  LocalAdminMembers JOIN fn_rbac_R_System(@UserSIDs)  SYS ON SYS.ResourceID = LocalAdminMembers.ResourceID   WHERE   SYS.Netbios_Name0 LIKE @variable    ORDER BY SYS.Netbios_Name0

To create a custom report

1. Go to SCCM console Reports Create report

2. Complete the Reporting Wizard. The MS SQL Report Builder will be opened up now

3. Double Click the Table or Matrix which will open to select a new dataset window. Select Create a dataset

4. Select the existing Data source connection and enter the data source credentials

5. Under Design a Query window, Select Edit as text and copy the above query

6. Next arrange the field as per the attached doc

7. Choose the Layout of the Report and complete the wizard

8. Right Click on report, where the empty area of report page and select properties. Go to reference tab, Click on assemblies. 



9. Select UserSIDs under Paramter and edit the properties

10. Go to Default Value and select Specific Values and Add expression. Leave the rest of the tab as default and complete it

11. Select Variable under Parameter and edit the properties

12. Type Computer Name under Prompt field and leave the rest of the tab as default and complete it.

13. Type Computer Name under Prompt field and leave the rest of the tab as default and complete it.

You are done.

Regards,

Vinod

Hi Vinod,

Will it support SCCM 2007 R3

Would like to know, without changing MOF file, can we achieve only creating sccm report, 

Pls advise. 

Will it support SCCM 2007 R3

Would like to know, without changing MOF file, can we achieve only creating sccm report, 

I can't seem to get this to work.

 

Not an error.  How to generate a report.  When I attempt to make the report I can't use any of the SQL listed in the thread.

I'm using SCCM 2012.

Not an error.  How to generate a report.  When I attempt to make the report I can't use any of the SQL listed in the thread.

Sorry for not providing enough details.

I'm using SCCM 2012 and trying to create a report that would be used to view the data that has been gathered.  When I go to create this report it starts Report Builder.  I'm using SQL 2012 with it. 

The initial settings have a datasource that starts with "AutoGen__" and some numbers and what not.  Whenever messing with this connection I was pretty limited and everything was XML.

I discovered a little more after creating a data source directly to the SCCM database.  Then the SQL started working as expected but not the query listed above.

SELECT DISTINCT SYS.Netbios_Name0, SYS.User_Name0, LocalAdminMembers.TimeStamp, LocalAdminMembers.Type0 as Object LocalAdminMembers.Account0, LocalAdminMembers.Domain0   FROM fn_rbac_GS_LocalAdminMembers0(@UserSIDs)  LocalAdminMembers JOIN fn_rbac_R_System(@UserSIDs)  SYS ON SYS.ResourceID = LocalAdminMembers.ResourceID   WHERE   SYS.Netbios_Name0 LIKE @variable    ORDER BY SYS.Netbios_Name0

With this one I get prompted for parameter data.

Then I get  errors about "Incorrect syntac near 'LocalAdminMembers'. Microsoft SQL Server, Error: 102)

Since the prompts have thrown me off I'm not sure what to put there.

Right now I'm attempting to work out parts of it backward with hopes it will start working.

This is starting to help:

select * from v_gs_localgroupmembers0 

I have data.

You are missing a "," after "object" in your query.

BTW it nice you see you are using RBA.

I get:

• Edited by David L. Jenkins Tuesday, February 11, 2014 8:50 PM

Invalid object name 'fn_rbac_GS_LocalAdminMembers0'

Report Builder is the tool I'm using.

The query I'm trying to figure out is the one mentioned in this thread and that was corrected with the comma.

I'm pretty sure this function doesn't exist so that is part of the confusion.

So far the query I have built seems to be working but not very pretty.  It just get's the data I want.

SELECT
 
  v_GS_SYSTEM.Domain0 AS [System Domain]
  ,v_GS_SYSTEM.Name0 AS [System Name]
  ,v_GS_LocalGroupMembers0.Account0 AS [Account]
,v_GS_LocalGroupMembers0.Type0 AS [Group Type]
  ,v_GS_LocalGroupMembers0.Category0 AS [Category]

  ,v_GS_LocalGroupMembers0.Domain0 AS [Domain]
  ,v_GS_LocalGroupMembers0.Name0 AS [Local Group Name]
  
FROM
  v_GS_LocalGroupMembers0
  INNER JOIN v_GS_SYSTEM
    ON v_GS_LocalGroupMembers0.ResourceID = v_GS_SYSTEM.ResourceID

If you are using CM12R2 then that function should exist, if not then you might have a bigger problem. That function is the only way to get RBA to work within SSRS.

BTW you should you v_R_System_Valid instead of v_GS_System.

I'll try V_R_System_Valid.

I'm at SP1 Cumulative Update 3.  I guess I need R2 installed!

Let me get that scheduled.  I'll update this post tomorrow.

I am able to get the info from ConfigMgr12. I applied it to our test collection and it deployed like a champ.

Now I am tasked with getting all that info into a Management friendly report. I tried some of the queries listed above but it seems they are built for getting info from one machine at a time.

Is it possible to run a query on an entire collection? Example I have a collection of just laptops. I will deploy the base configuration to that collection and wait for the HW Inventory CI to populate cm_localgroupmembers. I want to then run a report for that collection to see the data for the entire collection.

I am very SQL n00b, so I am not sure this is even possible.

Some of the stuff I was mentioning above seemed to work out.  I'm messing with reports right now.  You can use the table wizard to make the report just show everything like a spreadsheet. 

Did you say you got the SQL further up in the thread to work?  If so what version of SCCM are you using?

Apologies, I should have posted the version. 

We are running ConfigMgr 2012 R2.

I was able to get dpmaker's query to work, which it looks like it asks for info on one machine. I was not able to get vinod's to work. I got a few SQL errors like the ones you posted above.

David, when I try to run your query, I get a SQL error about the keyword 'AS'.

TITLE: Microsoft SQL Server Report Builder
------------------------------

An error occurred while executing the query.
Incorrect syntax near the keyword 'AS'.

------------------------------
ADDITIONAL INFORMATION:

Incorrect syntax near the keyword 'AS'. (Microsoft SQL Server, Error: 156)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2869&EvtSrc=MSSQLServer&EvtID=156&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I just wonder if we are able to specify a query to a collection based on a Collection ID.

We currently have 1500 desktops and 1900 laptops. I would hate to have to sift through that raw data to find out who has local admin access. I would help if I could find out who on the 10th floor of whatever building has local admin access.

We are only on System Center 2012 SP1 here so I cant use the top sql example as its error-ring out the same as it is for some of the others here.  I did try David Jenkins sql code as an alternative, but Im getting errors on it and was hoping I could get some help here.  Im not very experienced in SQL at all.  The table that was created in our SQL server by the Configuration Item is called dbo.LocalGroupMembers_DATA and in there I have quite a few columns.  When trying to build a report using David's code, I get the errors:

Invalid column name 'Domain0'.
Invalid column name 'Name0'.

In my table, I have columns for Domain00 and Name00 so I suspect that thats why there is a disconnect, but Im not sure what to do about it to make it right.  What do I need to change? As I said, much of this is beyond my SQL knowledge. 

Thanks guys.

Forgot to include the code that Im using:

SELECT
 
  v_R_System_Valid.Domain0 AS [System Domain]
  ,v_R_System_Valid.Name0 AS [System Name]
  ,v_GS_LocalGroupMembers0.Account0 AS [Account]
,v_GS_LocalGroupMembers0.Type0 AS [Group Type]
  ,v_GS_LocalGroupMembers0.Category0 AS [Category]

  ,v_GS_LocalGroupMembers0.Domain0 AS [Domain]
  ,v_GS_LocalGroupMembers0.Name0 AS [Local Group Name]
 
FROM
  v_GS_LocalGroupMembers0
  INNER JOIN v_R_System_Valid
    ON v_GS_LocalGroupMembers0.ResourceID = v_R_System_Valid.ResourceID

so you are saying what you want is...

• Limit to a collectionid
• Limit to only entries in the "Administrators" group (i.e., not Power Users, not Users, not any other group)

add this stuff:

FROM
  v_GS_LocalGroupMembers0
  INNER JOIN v_R_System_Valid
    ON v_GS_LocalGroupMembers0.ResourceID = v_R_System_Valid.ResourceID

join v_fullcollectionmembership fcm on fcm.resourceid=v_r_system_valid.resourceid

where

 fcm.collectionid = 'PRI01234'

and v_gs_localgroupmembers0.Name0 = 'Administrators'

(where you know that PRI01234 is a collectionid for "those machines on the 10th floor of building a"  If in your views it's domain00 and name00 instead of domain0 and name0; just modify those to exactly match whatever it is your database has; so change them to have two zeros instead of just the one.

Hi All,

I enabled Localgroupmembers in CAS server and configured baseline as mentioned by Sherry's SCCM 2012 post, Deployed it in a primary server yesterday. I am in need of SQL query which gives me the result for all the systems which are reporting to CAS. David's query(below) gives me a report for 352 systems(coverage is 1119 system so far).

SELECT
 
  v_GS_SYSTEM.Domain0 AS [System Domain]
  ,v_GS_SYSTEM.Name0 AS [System Name]
  ,v_GS_LocalGroupMembers0.Account0 AS [Account]
,v_GS_LocalGroupMembers0.Type0 AS [Group Type]
  ,v_GS_LocalGroupMembers0.Category0 AS [Category]

  ,v_GS_LocalGroupMembers0.Domain0 AS [Domain]
  ,v_GS_LocalGroupMembers0.Name0 AS [Local Group Name]
 
FROM
  v_GS_LocalGroupMembers0
  INNER JOIN v_GS_SYSTEM
    ON v_GS_LocalGroupMembers0.ResourceID = v_GS_SYSTEM.ResourceID

Thanks and regards,

Shesha

This is great! 

Any chance anyone has a piece of code I can add to the report that filters out disabled accounts or shows enabled or disabled status? 

Thanks!

Hi All,

I executed the configuration described in this article http://mnscug.org/blogs/sherry-kissinger/244-all-members-of-all-local-groups-configmgr-2012.

I have not had any problems and everything is ok.

The only variation --> I Imported "LocalGroupMembers.mof" in "Default Client Policy" but I enable the new class in a "Custom Client Policy" (execUtion every 1 Hour) where I have set the Hardware Inventory because It associated to "custom collection" of some Clients; I didn't enable the class in "default Client Policy" (execution every 1 day).

The baseline is compliance with all clients of "custom collection" (deployment with execution every 1 hour)

I verified on a client of "custom collection" the root\cimv2 with cm_localgrupmembers and it's populated.

I verified the log file called "sccmlocalgroupmembership.log" in Windir%\temp on client of "custom collection".

On DB there are 3 tables created: dbo.localgroupmembers_DATA / dbo.localgroupmembers_HIST /  SCCM_Ext.LocalGroupMembers_DATA_DD

On DB there are 3 Views created: dbo.v_GS_localgroupmembers0  /   dbo.v_HS_localgroupmembers0  / SCCM_Ext.vex_GS_LocalGroupMembers0

Everything seems ok, but after 1 week the tables and the views on DB aren't populated (zero records).

Do you have some ideas because my views and tables is not populated?

Thanks

Have you confirmed that WMI has be populated on one of the computers with the info?

Reset your DCM from hourly to ever 4 hours.

Hi Garth,

thanks a lot for your reply.

I can confirme that WMI has be populated; I verified the class with tool wbemtest on a client; I can view the class CM_LocalGroupMembers populated with users of local groups.

The baseline "WMI Framework For Local Groups with Logging" is compliance 100% with all client of "Custom Collection"; I change the deployment every 4 hours.

I'm afraid that this setting of schedule not resolved.

Have you another ideas in mind?

I verified all but I don't understand where's the problem.

Ivan

This blog post will help you find the problem.

http://be.enhansoft.com/post/2013/07/25/Troubleshooting-Inventory-Flow.aspx

Hi Experts

I followed Sherry's blog and everything ok. But 1 newbie question. How can see reports? I need report builder or something else?

Thanks

Tell me.

What sql query I'll use. Above quere's not working.

Tnx

• Edited by machado_mn Wednesday, December 24, 2014 1:54 AM

Tell me.

What sql query I'll use. Above quere's not working.

Tnx

HI

Yes I checked on client PC's %Windir%\temp\SCCMLocalGroupMembers.log 

12/24/2014 12:49:18 PM - Script Started
12/24/2014 12:49:18 PM - Not a Domain Controller, Continuing
12/24/2014 12:49:18 PM - Cleaned cm_localgroupmembers, if it existed.
12/24/2014 12:49:18 PM - Found 18 Local Groups
12/24/2014 12:49:19 PM - Found a total of 7 Names within those 18 groups
12/24/2014 12:49:19 PM - Starting to populate cm_localgroupmembers
12/24/2014 12:49:19 PM - Completed populating cm_localgroupmembers
12/24/2014 12:49:19 PM - Script Finished

SQL query error is: Incorrect syntac near 'LocalAdminMembers

Can u post step by step SQL query guide in here.

Thanks

HI

Yes I checked on client PC's %Windir%\temp\SCCMLocalGroupMembers.log 

12/24/2014 12:49:18 PM - Script Started
12/24/2014 12:49:18 PM - Not a Domain Controller, Continuing
12/24/2014 12:49:18 PM - Cleaned cm_localgroupmembers, if it existed.
12/24/2014 12:49:18 PM - Found 18 Local Groups
12/24/2014 12:49:19 PM - Found a total of 7 Names within those 18 groups
12/24/2014 12:49:19 PM - Starting to populate cm_localgroupmembers
12/24/2014 12:49:19 PM - Completed populating cm_localgroupmembers
12/24/2014 12:49:19 PM - Script Finished

SQL query error is: Incorrect syntac near 'LocalAdminMembers

Can u post step by step SQL query guide in here.

Thanks

Did u import the mot edit?

Where exactly are u getting the error?

Yes imported.

This error coming create a custom SCCM report with this code:

SELECT DISTINCT SYS.Netbios_Name0, SYS.User_Name0, LocalAdminMembers.TimeStamp, LocalAdminMembers.Type0 as Object LocalAdminMembers.Account0, LocalAdminMembers.Domain0   FROM fn_rbac_GS_LocalAdminMembers0(@UserSIDs)  LocalAdminMembers JOIN fn_rbac_R_System(@UserSIDs)  SYS ON SYS.ResourceID = LocalAdminMembers.ResourceID   WHERE   SYS.Netbios_Name0 LIKE @variable    ORDER BY SYS.Netbios_Name0

Are you CM07 or CM12?

Did u confirm that the data is within resource explorer?

Sorry to revive an old thread, but I'm working on getting this set up now, and I can confirm I do not have this function.  I am running 2012 R2 with SQL 2012 SP1.  Is this something that can be fixed with CU3?

If you are using CM12R2 then that function should exist, if not then you might have a bigger problem. That function is the only way to get RBA to work within SSRS.

This is working great for me. Any idea on how to add an OU column? Preferably the object path.

Erm... this data comes from the membership that happens to reside in local groups on individual clients, such as workstations and domain member servers.

There isn't an OU to reference--that information is not known to the local workstation.

What I suspect you mean is... "I see that somehow, some way, in the local group called "Administrators", there's a group that just so happens to be called "GoofyAdmins", and is in the domain "Mydomain"--what OU is that group contained in, in Active Directory on the "MyDomain" domain?

that's not what this routine is for--there's no call back or link to LDAP to get the OU for where that group happens to be on your domain.  this is all local-to-the-workstation information.

So no, it's not there.  what you could do is extract those domain groups, and using for favorite LDAP lookup / parser (posh? some commercial thing you have?) take that list and query your ldap on your domain for where a group with that name lives on your AD.

Updates link for the blog and report - http://myitforum.com/myitforumwp/2013/03/21/all-members-of-all-local-groups-inventory-for-configmgr-2012/