Browse Live Discover and Response Queries by Category
Under Review

NOTE: There are multiple recommendations covered within this posts comment section to help identify local admins. Please be sure you are using the right query shared for your goal.

EDR Query to find all local admins (Windows)

I am searching for a way to query the local Administrators security group on every device in our environment. This seems like something Live Discover is capable of doing, but I haven't been able to figure out the OSQuery syntax to get it done. Right now I'm left with doing it via SCCM and Powershell, but this is a little unreliable for us. Any chance of seeing a prebuilt query within Live Discover for this? Or can anyone lend me a hand figuring out the query?

EDIT:

I accidentally omitted an important piece. I've been researching on this for the past few days and forgot what the real hangup is.

We want to query both local users and domain users. Basically, we want to see any and all accounts that are added to the local admin group on any machine. So if MYDOMAIN\Jensenj is in the group on one of our computers, we want to know. We'd like to run this periodically to ensure that we don't have any accounts that we don't know about out there in the wild, as sometimes we have staff with admin creds that may add an account to that group (We're working on the root cause there:)

Jacob Jensen2
Parents Comment
  • in reply to j0hnV

    Tried that yesterday, but adding the roaming parameter yields no results. It's like there's a conflict in the relational data somewhere there that it can't reconcile

    I also attempted to match the gid_signed values, as the group ID for the administrators group was 544 in my case. That also yielded no results

Children
  • in reply to JeramyKopacko

    Interesting. I am not seeing the same result where it pulls in roaming at all with your mention of adding "OR u.type = "roaming""

    The JOIN on UG.UID = U.UID misses the domain account as the user_groups table doesn't include the UID for the roaming account. At least in my data set.

    Guess I need to look at reversing the logic for domain accounts or something

  • in reply to Troden

    Try "OR u.type = "roaming"

    The explicit "AND" will only show where both match. 

    In my testing, it returns results, but the username is not published. Seen below:

    You could also consider using one of the queries posted in the comments to identify administrators. Karl Ackerman posted this:

    SELECT
   uid,
   gid,
   uid_signed,
   gid_signed,
   username,
   description,
   directory,
   shell,
   uuid,
   type,
   is_hidden
FROM users
-- A local administrator SID will always begin with S-1-5- and end with -500
WHERE uuid LIKE 'S-1-5-%-500'

  • in reply to Jacob Jensen2

    Hi Jacob,

    Did you ever get this to work, when i add AND u.type = "roaming" I also receive no results 

Unfiltered HTML