The VMware Carbon Black Tech Zone is live! Checkout this great resource: Mastering Carbon Black Audit & Remediation.

Determine local administrator accounts

Description: Retrieves a list of local administrator accounts.

What The Data Shows: Finds local accounts that are in the administrator group.

SQL: 

SELECT username, groupname, type, u.UID, g.GID, Description, comment 
FROM users u
JOIN user_groups ug ON ug.UID = u.UID
JOIN groups g ON g.GID = ug.GID
WHERE g.GROUPNAME = "Administrators"
AND u.type = "local";
16 Comments
esullivan
Carbon Black Employee
Status changed to: Approved

thank you for your contribution, @cjohnston!

We are approving your submission, but wanted to note that we added the closing quotation mark and comma to the end of the query to make it complete.

Thank you again for submitting our first customer-generated query!

princemanto2580
New Contributor

Is this for Carbon Black Response?

jnelson
Carbon Black Employee

@princemanto2580 you can run this query in the Live Query (beta) tab within CB Response if:

  1. You have Carbon Black EDR 7.2, or greater
  2. The endpoint(s) you want to query have Carbon Black EDR Windows sensor 7.1.0
  3. Live Query has been enabled

Once on the tab click "Run new query" in the upper right, and then click on the "SQL" tab in the popup. Paste in the SQL above into the box, choose the sensor group or sensor(s) to query, and click "Run". 

princemanto2580
New Contributor

I have one environment in Clustered mode, but in master, I can't see this option "Live Query (beta)" in UI. Can you assist with this, how to configured and make it functional, please? 

jnelson
Carbon Black Employee

If you go to page 169 of this User Guide: https://community.carbonblack.com/t5/Documentation-Downloads/VMware-Carbon-Black-EDR-7-4-User-Guide/... you will see the instructions on how to enable it.

wmorse
New Contributor II

This works great to find the Admins, I thought I could try and use it for other local groups like "Remote Desktop Users" to see what users are in that group but it comes back with no match. Any suggestions on what I could try to query the Remote Desktop Users group on a machine?

jnelson
Carbon Black Employee
Status changed to: Under Review

@wmorse Could you please provide the query you used and the results of:

select * from user_groups;

and

select * from groups;

jnelson
Carbon Black Employee
Status changed to: Approved
 
wmorse
New Contributor II

Here are 2 examples that I found posted that I tried changing the Group name and got "No Match"


SELECT username, groupname, type, u.UID, g.GID, Description, comment
FROM users u
JOIN user_groups ug ON ug.UID = u.UID
JOIN groups g ON g.GID = ug.GID
WHERE g.GROUPNAME = "Remote Desktop Users"
AND u.type = "local";

 

SELECT ug.uid,
g.groupname,
u.username,
u.directory
FROM user_groups ug
LEFT JOIN groups g ON g.gid = ug.gid
LEFT JOIN users u ON ug.uid = u.uid
WHERE g.groupname like '%Remote Desktop Users%';

 

If I run just  select * from user_groups;  I do get a list of the local groups and Remote Desktop Users is one of them

jnelson
Carbon Black Employee

@wmorse I just added a local account to the Remote Desktop Users group and ran your first query and I got the results I expected. If you look at the user's UID does it show up with a GID of 555 in:

select * from user_groups;