SCCM ConfigMgr report for local admins and local group members

 

I had a requirement to generate report to list members (users/groups) of local administrators group on servers for auditing purpose. Finding the users/groups who are member of  local administrator group manually or scripting is tedious task on all servers .If you are managing the devices with configuration manager ,you can leverage Configmgr tool to get this task done so easily .

By default ,Configmgr do not have inbuilt solution /provide any report to get members of local administrator group ,but you we can achieve this using custom solution . The  only solution that i have tried earlier and seen people using ,is a solution that was provided /blogged by Sherry Kissinger .

Solution that was provided by Sherry was to create configuration item/configuration baseline with vbscript ,deploy this to collection ,import mof file into client agent settings to pull custom wmi changes that made by script,run report to get the required information.

If you search online with subject line ,you will mostly hit TechNet forum/blogs that refer to the following links.

http://myitforum.com/cs2/blogs/skissinger/archive/2010/04/25/report-on-all-members-of-all-local-groups.aspx

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

http://mnscug.org/images/Sherry/WMIFrameworkForLocalGroupswithLogging.zip

I have tried this solution very long ago for some of my customers which worked fantastic , but i did not blog about this as there are already posts available online.

I started to follow above blogs few days ago for my task, but for some reason these URL’s not active .So during my online search,i found few other blogs that talk about this solution .

I tried importing the cab file from sherry blog into configuration baseline, but for some unknown reason ,importing of cab file that did not succeeded on both Configmgr 2012 and Configmgr Current branch 1610. Both environments have the following error.

 

image

I am not the only one facing issue while importing the cab file, there are lot more people who posted about it on TechNet for solution.

So i started creating configuration items ,configuration baseline and do changes to client agent settings (MOF file) ,generate report .

I am attaching the configuration baseline cab file here for you to download ,extract ,import into your configmgr 2012 or configmgr current branch 1610 and simply deploy to your required collection, import MOF file into client agent settings for hardware inventory.

If you see any issues while Importing the cab file into configuration baseline ,please follow the steps illustrated below how to implement this solution step by step.

In this blog post, i will help you  how to create configuration item ,configuration baseline with the script that sherry provided ,do MOF changes in client settings ,wait for hardware inventory and create SQL query to run report.

There are 2 vbscripts out there online 1) Get members of local administrators group ONLY (WIN32_localadmins) 2)Get members from all local groups on the machine (cm_localgroupmembers)

Script 1 will get you the information about users/members who are member of administrators group ONLY and script 2 will get you members of all locally created groups.

Have attached both scripts in the download section for your reference in case you don't want all groups information.

image

Note: This task can be achieved in 2 ways ,either by deploying script as package or deploying the script using baseline method ,but Pre-requisite ,is recurring deployment, or Recurring DCM Baseline/CI

Steps in brief:

1. Import the MOF file into default client agent settings but do not select the changes in default client agent settings. You can select these changes on custom client agent settings to deploy to collection .

2. Create configuration item,configuration baseline and deploy to collection on recurring basis.

3.Run SQL query /report to get members of local administrators group.

Note: Should i go with configuration item or as package ? I would strongly suggest you go with configuration item and make it recurring instead of scheduling it for 1 time. Why should i make it recurring ?

Since the script that is used in the configuration item will create the instance in wmi “cm_localgroupmembers ” and query local groups with its members 1 time per script run ,which means if you run the configuration item 1 time ,it will query  local groups and members and pipe the information into cm_localgroupmembers  ,but if any changes happened after the compliance item run ,they wont appear in cm_localgroupmembers . For any addition or deletion of users/groups from local groups ,you must schedule it on recurring basis.

In this post, i will go with configuration baseline method.

Before we start the steps, download the files that are required to create baseline,MOF file ,reports etc from here

Step 1: Copy the MOF file from download section to your SCCM server,import the MOF file into default client agent settings—>Hardware Inventory in your SCCM server (CAS if you have else primary site )  ,de-select the settings  in default client agent settings for localgroupmembers .

Go to your custom client agent settings and select localgroupmembers that you want to get local members information.

If you do not have any custom client agent settings in your environment ,you can enable this settings in default client agent settings.

image

monitor dataldr.log for the changes .

with this change ,there will be a SQL view created and can be used for reporting which is : v_gs_localgroupmembers0. The Information which is stored SQL views that start with V_GS comes from inventory.

image

Step 2: From configuration manager console, assets and compliance , compliance settings right click configuration item ,create new ,type Name ,description

image

click next (leave default OS settings) ,next, on settings page ,add new with following information.

Name: WMI Framework for cm_localgroupmembers

Setting Type: Script

Date Type: String

Edit the script ,select vbscript ,paste the content from the SCCMLocalGroupMembers.vbs file .This is script 2 what i referred above. If you want only members of local admin group ,select localadmins.vbs

image

Click ok, click next ,on the compliance rules ,click new with the following information

Name: cm_localgroupmembers

Selected setting: select the setting that you created above

Rule type: existential

Setting comply rule: This specified script does not return any values

image

Click Ok ,next next to see the summary page.

Create a new baseline ,select the configuration item that we created above ,deploy it to collection .

Wait for client to receive new client device settings and configuration baseline to create wmi instance followed by client inventory .

On client machine after the policy ,assigned configuration baseline is compliant.

image

Logging information by script:

image

output of the script into SCCMLocalGroupMembers.log in C:\windows\temp folder:

image

SQL Queries:

Now we have sufficient information about the local users ,member of all local groups which is stored in SQL view ‘v_gs_localgroupmembers0’ .

We can create variety of SQL queries depends on the requirement .

Query 1: List all clients with members of the local Administrators group:

select sys1.netbios_name0
,lgm.name0 [Name of the local Group]
,lgm.account0 as [Account Contained within the Group]
,lgm.domain0 [Domain for Account]
, lgm.type0 [Type of Account]
from v_gs_localgroupmembers0 lgm
join v_gs_workstation_status ws on ws.resourceid=lgm.resourceid
join v_r_system sys1 on sys1.resourceid=lgm.resourceid
where lgm.name0='Administrators'
order by sys1.netbios_name0, lgm.name0, lgm.account0

Query 2: List members of the local Administrators group on specific client:

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_gs_workstation_status ws on ws.resourceid=lgm.resourceid
join v_r_system sys1 on sys1.resourceid=lgm.resourceid
where lgm.name0='Administrators'
and sys1.Name0='clientname'
order by sys1.netbios_name0, lgm.name0, lgm.account0

Query 3: List all clients with members of the local Administrators group excluding certain users or group  :

This will be helpful in case, you have applied some of the policies through GPO who should be member in local administrator group on all the clients for ex: domain admins or some other AD sec groups.

'Domain Admins','wintelMonitoring','WintelAdmins','eskonr'

declare @PC nvarchar (255);set @PC='computername'
select sys1.netbios_name0
,lgm.name0 [Name of the local Group]
,lgm.account0 as [Account Contained within the Group]
,lgm.domain0 [Domain for Account]
, lgm.type0 [Type of Account]
from v_gs_localgroupmembers0 lgm
join v_gs_workstation_status ws on ws.resourceid=lgm.resourceid
join v_r_system sys1 on sys1.resourceid=lgm.resourceid
where lgm.name0='Administrators' -- and sys1.name0=@pc
and lgm.account0 not in ('Domain Admins','wintelMonitoring','WintelAdmins','eskonr')
order by sys1.netbios_name0, lgm.name0, lgm.account0

 

Hope it helps!

43 Responses to "SCCM ConfigMgr report for local admins and local group members"

    1. I doubt if the script posted by sherry would also bring disabled accounts. I havent looked at the script for disabled accounts.
      If you want the disabled accounts information as well then script must be edit to pipe the information to wmi and let inventory collect this information and send to site server for database reporting.

      Thanks,
      Eswar

      Reply
  1. Hi Eswar! Great work on this! We've been running it in our environment for quite a while now! Now of course we are being asked to expand the report a bit and I was curious if this could\should be done through the addition of items in the MOF or through other means... We are asked to also provide the Description of the Local Account, whether it is Locked Out (True\False), the Last Login, when the last time the password was changed for the account and if the Password is Expired. Thank you for your thoughts!

    Reply
    1. if you want to get information for local accounts with its status etc, you can expand the vbscript to write information to wmi and expand the mof file to query this to database.
      I have not looked at it though but you can give a try.
      if you need domain accounts then you can discover it via AD system discovery custom attributes.

      Thanks,
      Eswar

      Reply
  2. Hey Eswar,
    thanks for the script and all the steps.
    I try to deploy the script and no luck so far, also if I run the vbs itself on the target computer I can't find any class populated named as in the script, what should I check first?

    Reply
    1. Anyway, I deployed the baseline and it worked like a charm, still curious why it doesn't work as a pure vbs script though.
      Also for international Windows users: if you want to make a proper select with the WHERE clause you should add N before the international name of your Administrators group, in my case it is
      [...]
      WHERE v_gs_localgroupmembers0.Name0 = N'Администраторы'

      Reply
    2. Did you check the logs on the computer ? did the log file generated ? you should run the script using local admin rights.

      Regards,
      Eswar

      Reply
  3. Hi,

    I followed process you mentioned. Applied baseline to a collection of 21 servers. In SCCMLocalGroupMembers.log files for all of those 21 servers I checked, I can see something similar to below

    5/13/2018 6:20:00 PM - Script Started
    5/13/2018 6:20:00 PM - Not a Domain Controller, Continuing
    5/13/2018 6:20:00 PM - Cleaned cm_localgroupmembers, if it existed.
    5/13/2018 6:20:00 PM - Found 24 Local Groups
    5/13/2018 6:20:00 PM - Found a total of 19 Names within those 24 groups
    5/13/2018 6:20:00 PM - Starting to populate cm_localgroupmembers
    5/13/2018 6:20:00 PM - Completed populating cm_localgroupmembers
    5/13/2018 6:20:00 PM - Script Finished

    But all SQL queries are returning local groups and their members for only 1 server. I checked SQL views manually and they have data for only this one server. Its been more than a week since I configured it. Client inventory settings are set to run per day. Any idea?

    Reply
    1. Is the inventory extended in client settings correctly ? can you login to one of the client PC and see if there is any WMI namespace created and see cm_localgroupmembers ?
      If the namespace exists in WMI,what does the inventoryagent.log tell you ? anything about this inventory information ?

      Regards,
      Eswar

      Reply
  4. Whenever i try to import the mof file into the client settings hardware inventory classes it errors:

    The following classes for which you are trying to import settings do not exist. Import the required class definitions and then try to import the settings again. LocalGroupMembers (cm_LocalGroupMembers)

    Reply
    1. Do you have CAS ? Where are you importing the client settings ? Can you send screenshot if possible to see more.

      Regards,
      Eswar

      Reply
  5. Awesome report, thanks!
    Is there a way to expand the groups? I mean, if i report a list of groups that are members of the local Administrators group, can I report the expanded users list? ex: instead of AD\GroupA, report shows UserA, UserB (which are the members of AD\GroupA). Thanks!

    Reply
  6. Hi Eswar,

    I've deployed this with success in my environment, but at the time I did it, the option "disabled" was not active in the hardware inventory classes because we didn't need it. Now, my manager asked me for that info. I'v turned it on, waited for the next cycle, I can see the CI running in clients, but I still get the "NULL" on the db tablev_gs_localgroupmembers0.

    What should I do? Deploy the baseline again?

    Thank you so much for the info here, pure gold Sir.

    Cheers!

    Reply
    1. If you have made any changes to the client agent settings ,it takes time for client to pick and load into wmi.
      For compliance baseline ,you can try to trigger baseline on the client manually ,check if it is compliant ,if so ,try to run the inventory action ,monitor the results in inventoryagent.log and then on SCCM database.
      if you have deployed the baseline onetime ,you will not get updated results . what this baseline does it ,when you run ,it pipe the information into wmi and inventory agent will pick this information and send it to site server.

      Regards,
      Eswar

      Reply
  7. Thank you for the great work you do regarding SCCM... I do have a question as I am a bit lost..

    After the baseline configuration shows as compliant, how do I retrieve that information whether by sql query or report in SCCM or other wise? I don't quite get your next step that says "Logging information by Script".. many thanks!!

    Reply
    1. Hi Erik,
      SQL query is given in this blog post. http://eskonr.com/2017/03/sccm-configmgr-report-for-local-admins-and-local-group-members/ you can use this to customize your requirements.
      Logging information by script means ,when the script run on the client, it will log the information into SCCMLocalGroupMembers.log in C:\windows\temp folder which is stated in the blog post as
      "output of the script into SCCMLocalGroupMembers.log in C:\windows\temp folder:"

      Regards,
      Eswar

      Reply
      1. Hello Eswar,
        thanks for your effort in this blog, very appreciated.
        However, I was unable to locate the scripts for option
        1) Get members of local administrators group ONLY (WIN32_localadmins)
        Everything I found was related to option 2) Get members of all groups

        Could you please have a look and re-add the scripts/files which are needed for option 1 ?
        Many thanks
        Christian

        Reply
        1. it looks like i did not added the script as solution 2 is most widely used and it provide lot of information . I am providing the script below for win32_localadmins. save it as .vbs .

          On Error Resume Next
          Dim wbemCimtypeString
          wbemCimtypeString = 8
          Set oLocation = CreateObject("WbemScripting.SWbemLocator")

          Set oServices = oLocation.ConnectServer(,"root\cimv2")
          set oNewObject = oServices.Get("WIN32_localadmins")
          oNewObject.Delete_

          ' Create data class structure
          Set oDataObject = oServices.Get
          oDataObject.Path_.Class = "WIN32_localadmins"
          oDataObject.Properties_.add "Account" , wbemCimtypeString
          oDataObject.Properties_("Account").Qualifiers_.add "key" , True
          oDataObject.Properties_.add "Domain" , wbemCimtypeString
          oDataObject.Properties_.add "Type" , wbemCimtypeString
          oDataObject.Properties_.add "Name" , wbemCimtypeString
          oDataObject.Properties_("Name").Qualifiers_.add "key" , True
          oDataObject.Put_

          Dim objGroup, strComputer ,strUserPath ,arrUserBits ,wshNetwork ,Domain,Name , Type1
          Set wshNetwork = WScript.CreateObject( "WScript.Network" )
          strComputer = wshNetwork.ComputerName
          Set objGroup = GetObject("WinNT://" & strComputer & "/Administrators,group")
          Dim objMember
          For Each objMember In objGroup.Members
          strUserPath = Mid(objMember.aDSPath, 9)
          arrUserBits = Split(strUserPath, "/")
          If UBound(arrUserBits) = 2 Then
          strUserPath = arrUserBits(1) & "/" & arrUserBits(2)
          Else
          strUserPath = arrUserBits(0) & "/" & arrUserBits(1)
          End If

          arrUserBits = Split(strUserPath, "/")
          Domain = arrUserBits(0)
          Name= arrUserBits(1)
          If Domain = strComputer Then
          Type1 = "Local"
          Else
          Type1 = "Domain"
          End If

          Set oNewObject = oServices.Get("WIN32_localadmins" ).SpawnInstance_
          oNewObject.Type = Type1
          oNewObject.Domain = Domain
          oNewObject.Account = objMember.Class
          oNewObject.Name = Name
          oNewObject.Put_
          Next

          Reply
  8. Eswar,

    Hoping this can find it's way to people that are running into troubles with this, or needing help with tweaks. I've just got through this myself.

    One thing that I decided to do differently in my environment to get some real reporting back on my baseline was to change my CI a bit. I changed my discovery script to this because I wanted it to tell me if the WMI object exists on the machine running the CI:
    Powershell Script:
    $ClassInfo = Get-WMIObject CM_LocalGroupMembers -ErrorAction SilentlyContinue -ErrorVariable wmiclasserror
    if ($wmiclasserror) { $ClassFound = 0 } else { $ClassFound = 1 }
    $ClassFound

    (sorry, would give a shout out if I remembered where I found this script - I just modified it for the WMI Object I was looking for)

    Then, added the VBScript from Sherry as my remediation. One final thing to note - I did find that in order for the VBScript to run properly on Windows 10, I had to change the TempFolder (line 10 in the VBS script) to manually point to C:\Windows\Temp\, otherwise the logfile didn't want to show up for me, and the WMI objects never got created, even though the script should have been running.

    Thanks!

    Reply
    1. Hi Chad,
      thanks for your suggestion but what is the reason for using discovery script to check if cm_localgroupmembers exist or not ? No matter if wmi class exist or not ,if it doesn't exist ,script will create ,if exist, script will update the existing .Please note that ,script must run on schedule basis otherwise you wont get the changes that made on the client after the script run .You must pipe every time the local admin groups into wmi before you see updated info in SCCM.

      Regards,
      Eswar

      Reply
      1. Eswar,

        The main reason is that I don't like running something if I don't need to. I'd rather not be running the script unless it needs to be run, and I don't need to run it if the WMI object exists. Also, this way I get a little reporting back and am able to easily see if the script is working properly or not right from the baseline without having to go look through the logfile that is created.

        Thanks,
        Chad

        Reply
        1. i believe it should work. there is nothing specific to OS in the vbscript .It just simply collect the groups and users ,pipe into wmi and later hardware inventory agent collect the information, send it to SCCM.
          Give a try on different OS and let me know the results.

          Regards,
          Eswar

          Reply
  9. Hi - I was testing this at home lab. All the configuration went well. When, I run SQL query it is successful but no details of users who are part of Local Admins group. The result from SQL query is blank. Not sure where, I am going wrong.

    Reply
    1. I have updated the MOF file . Please redownload the attachment ,import the MOF file into client settings ,it should work for you this time.

      Regards,
      Eswar

      Reply
      1. Hey, I've added the .MOF file to default client settings, added the .CAB file. Baseline is checking every hour.
        Log file in windows\temp is OK.
        checked via powershell in the WMI class cm_localgroupmembers and data is there.
        did numerous of hardware inventories on my client.
        But i don't get any data when i Query "v_gs_localgroupmembers0" ; it stays empty.
        Am i missing something critical ?

        Reply

Leave a Reply