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 local administrators group will give them full control over the device. You shouldn’t be surprised to see unwanted softwares installed on laptops when every user is a member of local administrators group.
CMPivot is a new in-console utility that was launched with SCCM 1806 and it provides access to 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.
We will run the CMPivot query on a device collection that consists of Windows 10 devices and find the local administrator accounts on every device. The devices have to be online in order to retrieve the local administrator account on them. In case the device is offline or not contactable, you would get to know about it in the query output.
I have got 4 Windows 10 devices in my collection and I have manually created an account called Test and added this account to local administrators group on one of my machine. In your production, you can run the SCCM CMPivot query directly and skip this step of creating a local admin account. This is only for testing purpose that I have done. Let’s see if my CMPivot query lists this Test account when I run find local administrator accounts CMPivot query.
Find Local Administrator Accounts with SCCM CMPivot Query
So let’s find local administrator accounts using SCCM CMPivot query on Windows 10 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 member of administrators group but their name doesn’t contain Administrators or Domain admins. This makes sense because domain admins group is present on every domain joined computer. 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 got just few results. When you run the same query in your production, you will surely see many devices in the output.
When you have got many devices in the 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 the Export option and from the drop-down select Results to file. You also have an option to copy the results to clipboard. With this, you can easily export local administrator accounts to a file and send it via email.
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.
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.