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:
- Find Recently Used Applications using SCCM CMPivot Query
- Use CMPivot Query to Find WSUS Server Details in SCCM
- Easily Find Dot Net Version using SCCM CMPivot Query
- CMPivot Query to Find TPM Status and Details
- Find Windows 11 Versions using CMPivot Query
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.
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.
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.
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.
Thank you very much for this. Worked great!
Thank you very much for the post. This saved my day.
Glad to hear that.
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!
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
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.
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.
Nice and simple. Love it when that happens. Thanks muchly.
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.
can we add detected machines automatic to a ne collection
Yes a query does that.
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.
Is this error with CMPivot query ?.
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
yes i have ran the CMPivot query and below is the error message i got on some servers
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.
Ok, can you log this question in the forums. I haven’t seen this issue before.
Thanks for share this information with us!
I am getting this below error message, Any suggest please
Bad input ‘!contains ‘Administrator’ and Name !contains ‘Domain Admins”
Probably those quotes are causing the issues. Check them and run the query again.
Thank you very much, very easy to set up and works perfectly
Thanks Cedric.