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!

17 Responses to "SCCM Configmgr report for local admins and local group members"

  1. 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
  2. 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
  3. 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

Leave a Reply