EDR Query to find all local admins (Windows)

Hi Jacob,

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"

works for me 

Caveat: domain accounts are shown as 'local' on domain controllers.

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

Hi Jaime Tyrer

See my reply to Troden and see if fetches the results you are looking for.

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'

hello, I add AND u.type = "roaming" but I do not get results from domain users who are in the Administrators group

Hi Jacob,

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

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

To find domain accounts only you could add

AND  u.type = "roaming"

To find domain accounts only you could add

AND  u.type = "roaming"

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

Hi Jaime Tyrer

See my reply to Troden and see if fetches the results you are looking for.

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'

hello, I add AND u.type = "roaming" but I do not get results from domain users who are in the Administrators group

Hi Jacob,

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

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