Find Local Administrator Accounts with SCCM CMPivot Query

In this post, I will show you how to find local administrator accounts using SCCM CMPivot Query. We will run a CMPivot query against a device collection in SCCM to find the local administrator accounts.

Finding local administrator accounts has always been a challenge in corporate environments. Adding the users to the local administrators group will give them full control over the device. When every user is a member of the local administrators group, you shouldn’t be shocked to find unwanted software installed on laptops.

CMPivot is a new in-console utility that was launched with SCCM 1806, and it provides access to the real-time state of devices in your environment. It immediately runs a query on all currently connected devices in the target collection and returns the results. You can filter and group this data in the tool. By providing real-time data from online clients, you can more quickly answer business questions, troubleshoot issues, and respond to security incidents. You can read more about CMPivot in the Microsoft documentation.

I have some good examples of CMPivot queries for SCCM administrators listed below:

We will run the CMPivot query on a device collection that consists of Windows 10 devices. You can also run the query on Windows 11 computers to get the local administrator accounts. Remember that when you run the CMPivot query, the devices have to be online to retrieve the local administrator account on them. The query output will let you know if the device is unavailable or unreachable.

I manually created an account called Test and added it to the local administrators group on one of my Windows 10 devices. I have four of these devices in my collection. In your production, you can run the SCCM CMPivot query directly and skip this step of creating a local admin account. I’ve only done this for testing purposes. Let’s check to see if the CMPivot query I ran to find local administrator accounts returns the Test account.

Local Administrators on computer
Local Administrators on computer

Find Local Administrator Accounts with SCCM CMPivot Query

Let’s find local administrator accounts using an SCCM CMPivot query on Windows 10 and 11 devices.

  • Launch the Configuration Manager console.
  • Navigate to Assets and Compliance > Overview > Device Collections.
  • Right click Windows 10 device collection and click Start CMPivot.
Find Local Administrator Accounts with SCCM CMPivot Query
Find Local Administrator Accounts with SCCM CMPivot Query

The CMPivot tool launches. Use the below SCCM CMPivot query to find local administrator accounts. Enter the query and click “Run Query.”

Administrators | where Name !contains 'Administrator' and Name !contains 'Domain Admins'

In the above query, we are finding the local administrator accounts that are members of the administrators group, but their names don’t contain Administrators or Domain admins. This makes sense because every computer that has joined a domain has the domain admins group installed. Hence, excluding these two in our query will give us the exact local administrator accounts.

After running this query, in the output, you can see that it has listed the Test account with the device name. The principal source is local, which means the account is created locally on the machine. Since I have a smaller lab setup, I have only gotten a few results. When you run the same query in your production, you will surely see many devices in the output.

Find Local Administrator Accounts with SCCM CMPivot Query
Find Local Administrator Accounts with SCCM CMPivot Query

Export the CMPivot Query Output

When you have got many devices in the CMPivot query output, and you have been asked to export the entire list, you can easily export the data. In the top-right corner of the CMPivot tool, you have an Export option. Click on the Export option and from the drop-down, select Results to File. The results can also be copied to the clipboard. With this, you can easily export local administrator accounts to a file and send it via email.

Export Local Administrator Accounts with SCCM
Export Local Administrator Accounts with SCCM

24 Comments

  1. I have 150 servers and i ran this, it only showed 19 completed. 132 failure. I clicked on the 132 failure report, inside only got 11 servers.
    I also ensure that all SID entries in 150 servers were housekept. Anyone an advise?

  2. Avatar photo Aashina Sethi says:

    Thank you so much. This is great, helped me save a ton of time.

  3. Avatar photo Vasi Moldo says:

    Thank you very much for this. Worked great!

  4. Avatar photo Litovchenko Vitaly says:

    Thank you very much for the post. This saved my day.

  5. Avatar photo Matt Pierce says:

    Prajwal,
    Once again, thanks for the helpful tips. This query works great and I was able to edit it to our environment and find local and domain admin accounts.

    To enhance this as an export for management to review, how would I go about adding in the code for a new column showing Full Name and/or First Name and Last Name. This way I don’t have to look it up and add that myself.

    Thanks again Prajwal!

  6. Avatar photo Eduardo Alonso says:

    Hello,

    Thanks for your webpage, as a noob with SCCM administration, i find it very VERY useful.

    But with this topic I have been stuck for a while, and cannot find a solution.

    I am using CMPivot to find all local admins on my computers.

    The thing is that from 895 clients, 332 are offline (that is fine) and 522 provide failure, and only works on 10 of them.

    When i checked the failures, most of them have the following error:

    “Failed to compare two elements in the array”

    I cannot find how to solve this issue. Maybe someone would help me to find a solution?

    Thank you in advance.

    Eduardo

    1. Hi Eduardo,
      I’m not sure how to help, but I also just used this query today and am getting the “Failed to compare two elements in the array” message on about 100 of our computers…my own being one of them.

      Hoping this will bump the thread so we can get an answer!

      Thanks.

    2. Avatar photo Rob Van Orden says:

      I have found that means that there is a SID entry, in the local admins group. here is some background I found, about the error.

  7. Nice and simple. Love it when that happens. Thanks muchly.

    1. Might be worth mentioning that my scan of a 1,690 member collection gets to almost done, then never completes. However, I’m still able to export the data to CSV. Just don’t stop the query before export.

  8. can we add detected machines automatic to a ne collection

  9. Avatar photo uday kumar says:

    getting the below error on few servers :

    The term ‘get-localgroupmember’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

      1. Avatar photo uday kumar says:

        Yes i have ran the CMPIVOT query on one of the collection and on some servers it worked fine and on some servers it failed with below error message :

        The term ‘get-localgroupmember’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again

      2. Avatar photo uday kumar says:

        yes i have ran the CMPivot query and below is the error message i got on some servers

      3. Avatar photo uday kumar says:

        Yes i have ran the CMPivot query on a collection , and got the error on some of the servers :

        The term ‘get-localgroupmember’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

        1. Ok, can you log this question in the forums. I haven’t seen this issue before.

  10. Avatar photo Cosme Cabello says:

    Thanks for share this information with us!

  11. I am getting this below error message, Any suggest please

    Bad input ‘!contains ‘Administrator’ and Name !contains ‘Domain Admins”

    1. Probably those quotes are causing the issues. Check them and run the query again.

  12. Thank you very much, very easy to set up and works perfectly

Leave a Reply

Your email address will not be published. Required fields are marked *