Query Exchange

 View Only
Expand all | Collapse all

Determine local administrator accounts

Soumyajit Dhara

Soumyajit DharaMay 16, 2021 04:30 AM

Jon Nelson

Jon NelsonDec 17, 2021 07:34 PM

  • 1.  Determine local administrator accounts

    Posted May 21, 2019 11:23 AM

    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";

    #ITHygiene
    #Community
    #Windows


  • 2.  RE: Determine local administrator accounts

    Posted Jun 06, 2019 09:02 PM

    thank you for your contribution, !

    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!



  • 3.  RE: Determine local administrator accounts

    Posted May 16, 2021 04:30 AM

    Is this for Carbon Black Response?



  • 4.  RE: Determine local administrator accounts

    Broadcom Employee
    Posted May 16, 2021 08:08 PM

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



  • 5.  RE: Determine local administrator accounts

    Posted May 17, 2021 09:41 AM

    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? 



  • 6.  RE: Determine local administrator accounts

    Broadcom Employee
    Posted May 18, 2021 04:05 PM

    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/tac-p/98754#M3182 you will see the instructions on how to enable it.



  • 7.  RE: Determine local administrator accounts

    Posted Dec 16, 2021 03:49 PM

    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?



  • 8.  RE: Determine local administrator accounts

    Broadcom Employee
    Posted Dec 17, 2021 07:21 PM

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

    select * from user_groups;

    and

    select * from groups;



  • 9.  RE: Determine local administrator accounts

    Broadcom Employee
    Posted Dec 17, 2021 07:34 PM


  • 10.  RE: Determine local administrator accounts

    Posted Dec 17, 2021 07:36 PM

    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



  • 11.  RE: Determine local administrator accounts

    Broadcom Employee
    Posted Dec 17, 2021 07:46 PM

     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;



  • 12.  RE: Determine local administrator accounts

    Posted Dec 17, 2021 07:53 PM

    Yes GID 555. 

    In the 2nd query I left out "local" statement to see if I could get all users to try and pick up any AD accounts. Is that possible? 



  • 13.  RE: Determine local administrator accounts

    Broadcom Employee
    Posted Dec 20, 2021 06:07 PM

     please email me at njon@vmware.com so we can set up a time to review as I think it may be easier to troubleshoot 



  • 14.  RE: Determine local administrator accounts

    Posted Feb 23, 2022 05:06 AM

    Hi,

    I tried it and it works great, Thank you!!

    I am not a coder so seeking help, is it possible to modify it to try few common passwords on the identified admin account or even all account?


    Thanks

     



  • 15.  RE: Determine local administrator accounts

    Broadcom Employee
    Posted Feb 23, 2022 03:46 PM

     That is not possible with Audit and Remediation.



  • 16.  RE: Determine local administrator accounts

    Posted Feb 24, 2022 12:05 PM

     Thanks 

    Is it possible with other CB product?

    Also it would be very helpful if you can direct me towards online resource which explains difference between all CB products/components/solutions.



  • 17.  RE: Determine local administrator accounts

    Broadcom Employee
    Posted Feb 28, 2022 06:19 PM

     I am sorry, but no CB products are capable of testing passwords.

    For the details on the rest of the Carbon Black offerings, you can check out the Product Path tab on this page: https://carbonblack.vmware.com/