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 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.