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

  • 'On a computer the SID for a local administrator will always begin with S-1-5- and end with -500. (That’s why the administrator SID-and other SIDS, such as SIDs for the Guest account-are considered well-known.) For example, you might have a SID that looks like this:

    S-1-5-21-1559272821-92556266-1055285598-500

    As you can see, our SID starts with S-1-5- and ends with -500. If we can find a SID that fits that pattern, then we’ve found our local administrator account.'

    With that bit of knowledge, we can modify the existing Sophos Users Query, adding a WHERE clause as shown below.

    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'

    I also have some queries that look for security events for similar information.
    -- NEW USER ACCOUNTS
-- VARIABLE     Number of days to check    STRING

SELECT
   source, 
   eventid,
   CAST(datetime(time, 'unixepoch') AS TEXT) AS 'Change Made',
   JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS 'Who Made The Change',
   JSON_EXTRACT(data, '$.EventData.TargetUserName') AS 'New User'
FROM sophos_windows_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS')
   AND source = 'Security'
   AND eventid = 4720;
    and a simple user activity query
    -- User Activity from desktop
-- VARIABLE:   $$User Name$$                             STRING

SELECT
   CAST( replace(datetime(spj.time,'unixepoch'),' ','T') AS TEXT)Date_Time, -- add the T to help excel understand this is a date and time
   CAST( users.username AS TEXT) User_Name,
   CAST( (SELECT processname FROM sophos_process_journal spj2 WHERE spj2.sophosPID = spj.parentSophosPID) AS TEXT) Parent_Process_Name,
   CAST( spj.processname AS TEXT) Process_Name,
   CAST( spj.cmdline AS TEXT) CmdLine,
   
   -- SHOW a pretty bar whre the size depends on the execution duration, Each █ is 1 hour
   CASE CAST( (CASE spj.eventType WHEN 0 THEN strftime('%s', 'now') - spj.processStartTime ELSE MAX(spj.endtime) - spj.processStartTime END)/60/60 AS INT)
      WHEN 0 THEN '│'
      ELSE printf('%.' || CAST( CAST(SQRT(CAST( CASE spj.eventType WHEN 0 THEN strftime('%s', 'now') - spj.processStartTime ELSE MAX(spj.endtime) - spj.processStartTime END AS INT)/60/60) AS INT) AS TEXT) ||'c', '█')
   END Execution_Duration,
   
   CAST( CASE spj.eventType WHEN 0 THEN strftime('%s', 'now') - spj.processStartTime ELSE MAX(spj.endtime) - spj.processStartTime END AS TEXT) Seconds,
   CAST( spj.sophosPid AS TEXT) SophosPID,
   CAST( spj.parentSophosPID AS TEXT) Parent_SophosPID,
   CAST( spj.SHA256 AS TEXT) SHA256,
   CAST( CASE spj.eventType WHEN 0 THEN 'Process Running' ELSE 'Process Stopped' END AS TEXT)ProcessStatus,
   CAST( spj.pathname AS TEXT) PathName
FROM sophos_process_journal spj
   LEFT JOIN users ON uuid LIKE sid
WHERE
   -- SEARCH AND FILTER CRITERIA
   spj.time > strftime('%s','now','-1 days') AND
   users.username LIKE '$$User Name$$' AND
   Parent_Process_Name LIKE 'explorer.exe'
GROUP BY SophosPID
Comment

  • 'On a computer the SID for a local administrator will always begin with S-1-5- and end with -500. (That’s why the administrator SID-and other SIDS, such as SIDs for the Guest account-are considered well-known.) For example, you might have a SID that looks like this:

    S-1-5-21-1559272821-92556266-1055285598-500

    As you can see, our SID starts with S-1-5- and ends with -500. If we can find a SID that fits that pattern, then we’ve found our local administrator account.'

    With that bit of knowledge, we can modify the existing Sophos Users Query, adding a WHERE clause as shown below.

    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'

    I also have some queries that look for security events for similar information.
    -- NEW USER ACCOUNTS
-- VARIABLE     Number of days to check    STRING

SELECT
   source, 
   eventid,
   CAST(datetime(time, 'unixepoch') AS TEXT) AS 'Change Made',
   JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS 'Who Made The Change',
   JSON_EXTRACT(data, '$.EventData.TargetUserName') AS 'New User'
FROM sophos_windows_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS')
   AND source = 'Security'
   AND eventid = 4720;
    and a simple user activity query
    -- User Activity from desktop
-- VARIABLE:   $$User Name$$                             STRING

SELECT
   CAST( replace(datetime(spj.time,'unixepoch'),' ','T') AS TEXT)Date_Time, -- add the T to help excel understand this is a date and time
   CAST( users.username AS TEXT) User_Name,
   CAST( (SELECT processname FROM sophos_process_journal spj2 WHERE spj2.sophosPID = spj.parentSophosPID) AS TEXT) Parent_Process_Name,
   CAST( spj.processname AS TEXT) Process_Name,
   CAST( spj.cmdline AS TEXT) CmdLine,
   
   -- SHOW a pretty bar whre the size depends on the execution duration, Each █ is 1 hour
   CASE CAST( (CASE spj.eventType WHEN 0 THEN strftime('%s', 'now') - spj.processStartTime ELSE MAX(spj.endtime) - spj.processStartTime END)/60/60 AS INT)
      WHEN 0 THEN '│'
      ELSE printf('%.' || CAST( CAST(SQRT(CAST( CASE spj.eventType WHEN 0 THEN strftime('%s', 'now') - spj.processStartTime ELSE MAX(spj.endtime) - spj.processStartTime END AS INT)/60/60) AS INT) AS TEXT) ||'c', '█')
   END Execution_Duration,
   
   CAST( CASE spj.eventType WHEN 0 THEN strftime('%s', 'now') - spj.processStartTime ELSE MAX(spj.endtime) - spj.processStartTime END AS TEXT) Seconds,
   CAST( spj.sophosPid AS TEXT) SophosPID,
   CAST( spj.parentSophosPID AS TEXT) Parent_SophosPID,
   CAST( spj.SHA256 AS TEXT) SHA256,
   CAST( CASE spj.eventType WHEN 0 THEN 'Process Running' ELSE 'Process Stopped' END AS TEXT)ProcessStatus,
   CAST( spj.pathname AS TEXT) PathName
FROM sophos_process_journal spj
   LEFT JOIN users ON uuid LIKE sid
WHERE
   -- SEARCH AND FILTER CRITERIA
   spj.time > strftime('%s','now','-1 days') AND
   users.username LIKE '$$User Name$$' AND
   Parent_Process_Name LIKE 'explorer.exe'
GROUP BY SophosPID
Children
  • in reply to Karl_Ackerman

    Thank you for this, it's very useful!

    However, using the WHERE clause to query for a SID like that seems to only be netting me any locally created users that are added to the administrators group, like the built in admin account. What I'm going for is the ability to find domain user accounts that have been added to that local admin group.

    So, staff member A somehow convinces someone to add their unprivileged staff AD account to the local admin group on their computer. They can now install whatever they want, etc. That's what I'm working on tracking down.

    I think I need to figure out how to correctly query the type column in the users table, as I see this in the osquery documentation here:

    users

    Local user accounts (including domain accounts that have logged on locally (Windows))

    type  TEXT  Whether the account is roaming (domain), local, or a system profile

Unfiltered HTML