February 22, 2022

Leveling Up with osquery for Workloads: Locating local administrator accounts (windows) - Part 2

This blog series is intended for readers that have a basic understanding of SQLite and have an osquery test environment. If neither of these things are true for you, please take a moment to read the Audit and Remediation Best Practices Guide before reading the rest of the blog.  

Audit and Remediation provide direct access to osquery functionality within the VMware Carbon Black Cloud console to enable security, compliance, and IT teams to query over 2,000 individual attributes across endpoints and workloads. osquery can help teams with gathering information at scale across environments for IT and help desk operations, compliance and M&A reporting, incident response, and security investigations.  

Audit and Remediation allow administrators to ask questions about the environment across hardware, software, and network metadata at scale. VMware Carbon Black Cloud Workload customers also have access to the full Audit and Remediation capabilities. In this blog series, we’ll be laying out relevant queries for VMware Carbon Black Cloud Workload customers to use to achieve a variety of use cases. All queries are available in the VMware Carbon Black Cloud console, or in the VMware Carbon Black User Exchange.    

Requirements:  

This blog series is intended for readers that have a basic understanding of SQLite and have an osquery test environment. If neither of these things are true for you, please take a moment to read the Audit and Remediation Best Practices Guide before reading the rest of the blog.  

Resources  

Locating Local Windows Admin Accounts  

Local administrator accounts get created for a variety of reasons, from trying to overcome remote management obstacles, shadow IT, or trying to bypass security policies. If these accounts are left on endpoints, then they can be an attack vector for malicious actors or insiders. In some cases, these local administrator accounts share passwords across the enterprise, so if the credentials are compromised, they can be used for lateral movement. Help desk analysts, security teams, system administrators, and auditors need the ability to identify these accounts with speed and confidence across their Windows environments.  

In this blog post, we will construct a query that allows us to identify local administrator accounts across all Windows endpoints.  This query can also be modified to look for users in any other local group, such as the Remote Desktop Users group. 

We are going to be making use of three different tables in this query:  

  • users  

  • groups   

  • user_groups  

Let's take a look at an example of the data in each table. Since there are a lot of columns in two of these tables, I have switched the output to line mode, and truncated the output to save space:  

osquery> .mode line  
osquery> select * from users;  
        uid = 500  
        gid = 544  
 uid_signed = 500  
 gid_signed = 544  
   username = Administrator  
description = Built-in account for administering the computer/domain  
  directory =  
      shell = C:\Windows\system32\cmd.exe  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-500  
       type = local  

        uid = 1001  
        gid = 544  
 uid_signed = 1001  
 gid_signed = 544  
   username = bit9se  
description =  
  directory = C:\Users\bit9se  
      shell = C:\Windows\system32\cmd.exe  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-1001  
       type = local  

        uid = 503  
        gid = 581  
 uid_signed = 503  
 gid_signed = 581  
   username = DefaultAccount  
description = A user account managed by the system.  
  directory =  
      shell = C:\Windows\system32\cmd.exe  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-503  
       type = local  

        uid = 501  
        gid = 546  
 uid_signed = 501  
 gid_signed = 546  
   username = Guest  
description = Built-in account for guest access to the computer/domain  
  directory =  
      shell = C:\Windows\system32\cmd.exe  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-501  
       type = local     

osquery> select * from groups;  
       gid = 579  
gid_signed = 579  
 groupname = Access Control Assistance Operators  
 group_sid = S-1-5-32-579  
   comment = Members of this group can remotely query authorization attributes and permissions for resources on this computer.  

       gid = 544  
gid_signed = 544  
 groupname = Administrators  
 group_sid = S-1-5-32-544  
   comment = Administrators have complete and unrestricted access to the computer/domain  

       gid = 551  
gid_signed = 551  
 groupname = Backup Operators  
 group_sid = S-1-5-32-551  
   comment = Backup Operators can override security restrictions for the sole purpose of backing up or restoring files    

osquery> select * from user_groups;  
  uid = 500  
  gid = 544  

  uid = 1001  
  gid = 544  

  uid = 1001  
  gid = 555  

  uid = 503  
  gid = 581  
 
  uid = 501  
  gid = 546 

In looking at the data in the users table there is no indication that any of the accounts are administrator accounts unless you know that 544 is the UID (user ID) for the Administrators group.  

If we look at the groups table, there is no way for us to identify which accounts are in any of the groups. This is where the user_groups table comes into play, as it is a list of the UIDs associated with specific GIDs (group IDs).  

To pull all of this data together in one query we are going to use two JOIN statements. Let's start out with a very basic query against the users table only using the columns that would be of interest, and focusing on local accounts:  

osquery> select username, uid, gid, uuid from users where type = 'local';  
username = Administrator  
     uid = 500  
     gid = 544  
    uuid = S-1-5-21-2643456277-1205808317-3239813547-500  

username = bit9se  
     uid = 1001  
     gid = 544  
    uuid = S-1-5-21-2643456277-1205808317-3239813547-1001  

username = DefaultAccount  
     uid = 503  
     gid = 581  
    uuid = S-1-5-21-2643456277-1205808317-3239813547-503  

username = Guest  
     uid = 501  
     gid = 546  
    uuid = S-1-5-21-2643456277-1205808317-3239813547-501  

username = WDAGUtilityAccount  
     uid = 504  
     gid = 513  
    uuid = S-1-5-21-2643456277-1205808317-3239813547-504  

 

Now we need to use the first JOIN to the user_groups table to identify which GIDs are associated with each account. Since we are joining to a table that has the same column names as our first table, we need to identify which table and column we are using. This is easier to do using aliases:  

osquery> select username, u.uid, ug.gid, uuid  
    ...>     from users as u  
    ...>     join user_groups as ug  
    ...>       using(uid)  
    ...>     where type = 'local';  
username = Administrator  
     uid = 500  
     gid = 544 
    uuid = S-1-5-21-2643456277-1205808317-3239813547-500  

username = bit9se  
     uid = 1001  
     gid = 544  
    uuid = S-1-5-21-2643456277-1205808317-3239813547-1001  

username = bit9se  
     uid = 1001  
     gid = 555  
    uuid = S-1-5-21-2643456277-1205808317-3239813547-1001  

username = DefaultAccount  
     uid = 503  
     gid = 581  
    uuid = S-1-5-21-2643456277-1205808317-3239813547-503  

username = Guest  
     uid = 501  
     gid = 546  
    uuid = S-1-5-21-2643456277-1205808317-3239813547-501  

Now we can use our second JOIN on the groups table to get the name and description for the groups:  

osquery> select username, u.uid, groupname,ug.gid, description, uuid  
    ...>     from users as u  
    ...>     join user_groups as ug  
    ...>       using(uid)  
    ...>     join groups  
    ...>       using(gid)  
    ...>     where type = 'local';  
   username = Administrator  
        uid = 500  
  groupname = Administrators  
        gid = 544  
description = Built-in account for administering the computer/domain  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-500  

  username = bit9se  
        uid = 1001  
  groupname = Administrators  
        gid = 544  
description =  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-1001  

   username = bit9se  
        uid = 1001  
  groupname = Administrators  
        gid = 555  
description =  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-1001  

   username = DefaultAccount  
        uid = 503  
  groupname = System Managed Accounts Group  
        gid = 581  
description = A user account managed by the system.  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-503  

   username = Guest  
        uid = 501  
  groupname = Guests  
        gid = 546  
description = Built-in account for guest access to the computer/domain  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-501 

Lastly, we need to limit the results to only include accounts in the Administrators group, which we can do by adding an additional WHERE clause:  

osquery> select username, u.uid, groupname,ug.gid, description, uuid  
    ...>     from users as u  
    ...>     join user_groups as ug  
    ...>       using(uid)  
    ...>     join groups  
    ...>       using(gid)  
    ...>     where type = 'local'  
    ...>       and groupname = 'Administrators';  
   username = Administrator  
        uid = 500  
  groupname = Administrators  
        gid = 544  
description = Built-in account for administering the computer/domain  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-500  

   username = bit9se  
        uid = 1001  
  groupname = Administrators  
        gid = 544  
description =  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-1001  

   username = bit9se  
        uid = 1001  
  groupname = Administrators  
        gid = 555  
description =  
       uuid = S-1-5-21-2643456277-1205808317-3239813547-1001 

This query is available on the VMware Carbon Black User Exchange.  

By changing the last query to include any other group you are interested in, you can easily query for those groups as well.  

In this blog post, we have shown the power of osquery to identify local administrator accounts. Help desk analysts, security teams, system administrators, and auditors can then use the queries in this post to gain a better understanding of where these accounts persist in their Windows environments. In case you missed our last query feature, check out “Identifying and Contextualizing Windows Login Failures” here and stay tuned for our next blog. 

To learn more about how Audit and Remediation capabilities can help you gather data at scale across your environments, please visit:  

  1. Carbon Black Cloud Workload TestDrive  

  1. VMware Carbon Black Tech Zone: Mastering Audit & Remediation  

  1. Query Exchange (Carbon Black User Exchange)  

 

 

Filter Tags

Carbon Black Cloud Audit and Remediation Blog

Jon Nelson

Read More from the Author

Jon is a seasoned computer security professional with over 25 years of experience. He has worked for VMware Carbon Black for 5 years in New York City and the San Francisco Bay Area focusing on large enterprise accounts and security strategy as a Principal Solution Engineer. Jon had come from RSA where he was a Senior Security Systems Engineer in a pre-sales role whose clients included the top tier financials and pharmaceuticals in the New York Metro area. Prior to RSA Jon worked at the Defense Cyber Investigations Training Academy in Linthicum, MD where he taught computer forensics, network investigations, and malware analysis. Before that, Jon was a Corporal in the Pennsylvania State Police where he conducted proactive and reactive computer crime investigations for over 13 years before retiring in 2010.