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.