Skip to main content

CM All Members of All Local Groups - Powershell

"Back in the Day", --> Here <-- a vbscript was created to allow for ConfigMgr (version 2012 at the time was the version I was using) to be able to custom inventory the members of Local Groups. This was mostly in response to manager-type requests to know "what individuals or groups are inside the local Administrators group". That has been working fine for years... but times change, and it may be more palatable to use Powershell.  Powershell is more widely used and understood instead of vbscript. Additionally, if your company requires it, you can sign powershell scripts so you know they are tested and trusted internally.

Update on 2023-06-04.  I found that because of language localizations, the 'Administrators' group would be in Cyrillic language, and was difficult to easily sql-query.  So I determined to update the script to include the SIDs of the local group, as well as the SIDs of the the user or groups inside.  While doing so... I found that there were some devices (now that I was testing in production, instead of my 2 client lab, lol), that *if*, for example, there were orphaned IDs in the local Administrators group (or any other group, like Remote Desktop Users), the powershell command of get-localgroupmembers would error.  After some research--https://github.com/PowerShell/PowerShell/issues/2996 , this is a known issue... which apparently was promised to be 'fixed gradually', as of September 2022... but hasn't been fixed yet... and the bug has been closed.  The conclusion in that thread by people experiencing this error was to "work around the problem" by using [ADSI] instead of get-localgroupmembers.  So that's what Sherry did--a quite major re-write of the code. 

I strongly suggest that you test this in your lab environments thoroughly. Don't just blindly trust this. It is definitely a work in progress, and may have so many flaws that you'll break something, horribly. Test. Test. Test.  I also suggest you read the scripts; I did try to over-explain and add comments everywhere; but as with anything you might randomly find on the internet--I suggest you read it through first.  Know what it is and what it is trying to do, and/or test it interactively on a standalone lab box.  "Trust, but verify".

--> 2023 Version <-- is a zip file containing 2 ps1 scripts (renamed from .ps1; in case your anti-malware flags and blocks script files), a mof file to be imported.  Additionally, the zip contains the script where get-localgroupmembers is used instead of ADSI... in case, one day, Microsoft Powershell's cmd for 'get-localgroupmembers' is actually fixed to not fail (not holding my breath, it's been an issue apparently since 2017 and it's 2023 now, so...)

--> 2021 Version<-- is the version from 2021, which is a zip file containing 2 ps1 scripts (renamed from .ps1; in case your anti-malware flags and blocks script files), a mof file to be imported, and a basic sql query to get you started.  Just keeping it here for historical purposes.

How to use the attached... If you are familiar with CM Configuration Items, and the concept of "script + mof edit", the below is over-explained. If you are already familiar with the concepts, just download the attachments and set it up in your lab for testing; and once you are comfortable, deploy it as you like.

  1. In your CM console, go to Assets and Compliance, then Compliance Settings, then Configuration Items.
  2. Create a Configuration Item. When prompted, give it a name (Name is up to you and your standards. For the purposes of this information, I'm calling the Configuration Item "Inventory Staging for Local Group Members with Logging"
  3. This is a "Windows Desktop and Servers" type; you *do* want to check the box for "This configuration item contains applications settings.
    1. Add a description if you like; perhaps the link to this blog, or the date you added this, and what manager-type wanted this information; whatever might be useful 2 years down the road when the person that comes after you is trying to figure out what this is for and why.
  4. Next.
  5. Detection Methods, select "Use a custom script to detect this application". That script will be the one in the attachment, labeled "ApplicabilityForTheCI.Rename-to-ps1". What that does is it checks the client to see if it's a Domain Controller. If it *is* a Domain Controller, then the Configuration Item is NOT APPLICABLE, and it won't run the script inside. The script itself also does a check, and bails; and hopefully you will also on purpose not EVER target your domain controller(s) with this... but mistakes happen. The more places to ensure that a DC won't be asked these types of questions, the better you'll feel about having this in your environment. You certainly don't want your DCs to try to do this.
  6. Next
  7. Settings, New... Give it a Name (any name), and a description.
    1. Setting Type = Script
    2. Data Type = String
    3. Add Script...Script Language=Powershell and copy and paste in the script contained in the attachment labeled "MainScript-ADSIMethod.Rename-To-ps1"
      • Optionally... Sign the script according to your company standards
      • Optionally... Change the logging location from %temp% to the CM client log folder (it's within the script, just comment/uncomment the correct lines
      • Optionally... Turn off a local log file completely, according to your company standards.
  8. within the Settings area, at the top change from "General" to "Compliance Rules".
    1. New...Rule Type = Existential, and you want the default choice of "The specified script returns at least one value".
  9. Ok. Ok. Hit Next/Next/Next however many times until it's done and saved.
  10. In your CM Console, go to Assets and Compliance, then Compliance Settings, Configuration Baselines
  11. Create Configuration Baseline, give it a name and description; again--according to your own standards, and try to leave a good description for the person coming after you to know what this is for and why.
  12. Add, Configuration Items, and find the one you created above. Assuming you called it exactly what I called it, it'll be called "Inventory Staging for Local Group Members with Logging". Click Add, then OK.
  13. Don't hit the next OK yet. Select that name in the middle, and you want to "Change Purpose" from Required to Optional. NOW hit OK.
  14. If you don't yet have a collection of Test devices, go make a collection of test workstations and/or Server clients. Once you have a collection of devices (ideally, ones to which you have rights to look at their %temp% or cm logs remotely), Deploy this baseline to that collection. Frequency to run is up to you, but I would suggest no more frequently than every 3 days--honestly, this inventory staging isn't that important. Every 7 days is most likely fine.

  15. TEST TEST TEST

    On those test devices, trigger policy refreshes, and when the baseline appears, have it run. Depending upon which log location you set, you can check that log location for the log file. Additionally, you can use your favorite WMI browser (WMIExplorer?) to check root\cimv2\cm_localgroupmembersV2 and see if what will be reported, matches reality.

  16. Once you have confirmed it does what you want it to do, you will want to setup ConfigMgr to be able to inventory this custom WMI Class. NOTE!!!  If you have previously used this routine, the WMI Class is NEW, "CM_LocalGroupMembersV2". Every environment is different, so I can't predict what you may or may not need to do, in your environment for this customization. In general, in your CM Console, go to Administration, Client Settings, right-click "Default Client Settings", Properties, then Hardware Inventory. From the attachment, have the "ToBeImported-ADSI-v2-Method.mof" available. Set Classes... then Import the .mof.
  17. MONITOR your <server, CM installed location>\Logs\dataldr.log and confirm the mof is imported successfully, and the view is created.
  18. On those test clients (remember, you have NOT deployed this yet to most devices); wait a bit, then policy refresh. Then do a Hardware Inventory action. Monitor the client's inventoryagent.log, and hopefully you'll see the wmi query for select...from cm_localgroupmembersv2. Wait a bit for your server to process that inventory, then using SQL (or I suppose, resource explorer) to check that box' inventory--confirm the values were reported.
  19. Once you've confirmed that all the sections work--from the CI/Baseline, to inventory, then you can deploy that Baseline to the devices you want to report; that's up to you of course. all workstations? all workstations and all servers (but NOT Domain Controllers)? Just that <insert annoying internal team that always tries to bypass the rules and puts a random local user in the local Administrators group, because "they need it" (even when every company policy says to never do that, so they need to get yelled at by upper management, and you have to tell upper management who they need to yell at, using this routine)> ?

Caution... because of the bug with get-localgroupmembers, please, please read through the description of the issue https://github.com/PowerShell/PowerShell/issues/2996 . This issue affects orphaned accounts left behind... but MAY ALSO affect completely legitimate accounts or groups, for AAD joined devices.  So before you think "great, I'll use this to clean up orphaned entries left behind..." DO YOUR RESEARCH.  In that link, for example, ganlbarone says "Local admin groups on azure machines "look like" broken sids.. Even though they really arent".

Sample SQL to get you started... This would be "show me users and groups which are in the local Administrators group, where it's not "Domain Admins", nor the legitimate local Administrator account, even if it has been renamed.
select
s1.netbios_name0 as 'ComputerName'
,lgm.Account0 as 'Account or GroupName'
,lgm.sid0 as 'SID of the Account or GroupName, if known'
,lgm.Category0 as 'Category'
,lgm.Domain0 as 'Domain or Local ComputerName, if Associated with this Account'
,lgm.Enabled0 as 'if local account, is it enabled'
,lgm.name0 as 'Name of the Local Group on this device'
,lgm.Groupsid0 as 'SID of the Local Group on this device'
,lgm.Type0 as 'Type of account'
from v_GS_LocalGroupMembersV20 lgm
join v_r_system s1 on s1.resourceid=lgm.ResourceID
Where lgm.GroupSID0 = 'S-1-5-32-544'  --This is Administrators group, regardless of groupname
and right(lgm.sid0,4) <> '-512' -- Ending in -512 means it's the Domain Admins group (so it's valid)
and right(lgm.sid0,4) <> '-500' -- Ending in -500 means it's the local Administrator account (so it's valid)

Localization issues

On 2021/12/02, someone did test this in their environment, and found an issue-->  SCCM Query for local Admin - Microsoft Q&A    When I was testing in the super small lab, the only devices involved had en-US localization.  The tester in the thread  Paolo Bragagni , found that because of different localization, instead of an objectclass of "User", he would get 'Utente'  (Utente is Italian for User)

Their solution was to slightly modify a section of the script, to look for "either one" of those ObjectClass results.  

Note this only affected the ability to report on locally disabled user accounts; other elements of the script worked without modification.  There may be a better way to work around localization issues; but this "worked for them".

 if ( ($ReturnedValues.PrincipalSource -eq 'Local') -and (($ReturnedValues.ObjectClass -eq 'User') -or ($ReturnedValues.ObjectClass -eq 'Utente'))) {

------------
2023-05-31: Another Localization issue, the 'name' of Administrators would come back localized... which was fine for European-based languages (mostly), but if they were other types...it would be in SQL; but would be difficult to query against (as a European language speaker / coder), so SIDs of the Group and SIDs of the account/groups instead are attempted to be gathered.

Localization Update(s)

To address the localization issues, Sherry Kissinger modified the script slightly on 2021/12/07.  The attachment has been modified, to no longer look for the words of "local", nor "User", when checking for whether or not a user account was enabled or disabled.

To address the localizations issues (in 2023), Sherry Kissinger majorly modified the script to use [ADSI] and gather the SIDs as well.

CMCB, ConfigMgr

  • Created on .