[QueryCorner][May2022] Audit User and Group Activity

Purpose

Our first post in the Query Corner is going to target User and Group Activity. If you are unfamilar, there is a great desk aid for remembering Windows Event IDs that impact security on https://www.ultimatewindowssecurity.com/securitylog/encyclopedia/

In this query, we will focus on the following subset of events:

Event ID

Description

4720

User account created

4722

User account enabled

4723

Attempt was made to change an account’s password

4724

Attempt was made to reset an accounts password

4725

User account disabled

4726

User account deleted

4727

Security-enabled global group was created

4728

Member added to a security-enabled global group

4729

Member was removed from a security-enabled global group

4730

Security-enabled global group was deleted

4731

Security-enabled global group was changed

4732

Member was added to a security-enabled local group

4733

Member was removed from security-enabled local group

4734

Security-enabled local was deleted

4740

User account was locked out

4754

Security-enabled universal group was created

4756

Member added to a security-enabled universal group

4757

Member was removed from a security-enabled universal group

4758

Security-enabled universal group was deleted

4767

User account was unblocked


Prerequisites

You may be experiencing results of data that return empty. Often, it is a culprit of a weak auditing policy enabled in Windows. By Default, Windows does not audit for every security event out of the box. You will want to verify that you are auditing for the events above by reviewing Microsoft's Security Auditing Overview documentation. 


Query Focus

It's only right to showcase what  and team are doing on the Incident Response side. They've developed this query in their Sophos Rapid Response query pack, that is publicly available here and the specific query located here for this example. I am sharing the raw file below as well, to save you a click.

/*************************** Sophos.com/RapidResponse ***************************\
| DESCRIPTION                                                                    |
| This collects various event IDs from the Security event log that relate to     |
| user and group changes, including account creation, password resets, adding to |
| groups and account deletion.                                                   |
|                                                                                |
| VARIABLES                                                                      |
| days(string) = how many days back from NOW to search                           |
|                                                                                |
| Version: 1.0                                                                   |
| Author: @AltShiftPrtScn / Elida Leite                                          |
| github.com/SophosRapidResponse                                                 |
\********************************************************************************/

SELECT
   source AS Source,
   eventid AS Event_ID,
   CAST(datetime(time, 'unixepoch') AS TEXT) AS Datetime,
   CASE
    WHEN eventid IN('4730', '4734', '4758') then CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' deleted the security group ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)
    when eventid = 4740 then 'Account Locked'
	WHEN eventid IN('4726') then CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' deleted the user ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)
    when eventid = 4740 then 'Account Locked'
    WHEN eventid IN ('4727', '4754', '4731') then CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' created the new security group ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)
    WHEN eventid = 4720 then CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' created the new user ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)
    WHEN eventid = 4767 then CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' unlocked the user account ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)
    WHEN eventid = 4725 then CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' disabled the user ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)
    WHEN eventid = 4722 then  CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' enabled the user account ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)
    WHEN eventid IN ('4723','4724') then CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' reset/set the password for ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)
    WHEN eventid IN ('4728','4732','4756') then CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' added ' || CHAR(39) || 
		CASE	
			WHEN (Select u.username from users u where JSON_EXTRACT(data, '$.EventData.MemberSid') = u.uuid) == '' then JSON_EXTRACT(data, '$.EventData.TargetSid') ELSE (Select u.username from users u where JSON_EXTRACT(data, '$.EventData.MemberSid') = u.uuid) 
		END 
	|| CHAR(39) || ' to the security group ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)
    WHEN eventid IN ('4729','4733','4757') then CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' removed ' || CHAR(39) || 
		CASE WHEN (Select u.username from users u where JSON_EXTRACT(data, '$.EventData.MemberSid') = u.uuid) == '' then JSON_EXTRACT(data, '$.EventData.TargetSid') ELSE (Select u.username from users u where JSON_EXTRACT(data, '$.EventData.MemberSid') = u.uuid) 
		END 
	|| CHAR(39) || ' from the security group ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)
    ELSE NULL
    END AS Description,
   JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS 'Who_Made_The_Change',
   CAST ( (Select u.username from users u where JSON_EXTRACT(data, '$.EventData.MemberSid') = u.uuid) AS text) Username_Changed,
   JSON_EXTRACT(data, '$.EventData.MemberSid') AS 'User_SID_Changed',
   JSON_EXTRACT(data, '$.EventData.TargetUserName') AS 'Group_Or_User_Name_Changed',
   JSON_EXTRACT(data, '$.EventData.TargetSid') AS 'Group_SID_or_User_SID_Changed',
   'Security EVTX' AS Data_Source,
   'User.02.0' AS Query
FROM sophos_windows_events
WHERE source = 'Security' AND time > STRFTIME('%s','NOW','-$$days$$ DAYS') AND eventid IN ('4726', '4730', '4734', '4758', '4740','4727', '4754', '4731', '4720', '4767', '4725', '4722','4723','4724', '4728','4732','4756', '4729','4733','4757') ORDER BY Datetime DESC

Understanding the code

Let's focus our attention on Line 20. This is where the first CASE expression is used. This is a large portion of the collection. 

WHEN eventid IN('4730', '4734', '4758') then CHAR(39) || JSON_EXTRACT(data, '$.EventData.SubjectUserName') || CHAR(39) || ' deleted the security group ' || CHAR(39) || JSON_EXTRACT(data, '$.EventData.TargetUserName') || CHAR(39)

  • eventID is the field being returned from the Sophos_Windows_Events table. If the case expression is a match, it will proceed from the "THEN" statement to return the proper information. 
  • CHAR(39) is a unique value to return from one of the 256 integer ASCII codes to a character value
  • || is concatenating the difference bites of data as one line
  • JSON_EXTRACT(data, '$.EventData.SubjectUserName') is extracting information from the JSON formatted data field

{"EventData":{"PrivilegeList":"-","SubjectDomainName":"CY","SubjectLogonId":"0x2345b1","SubjectUserName":"Administrator","SubjectUserSid":"S-1-5-21-2385618916-250871166-2127620695-500","TargetDomainName":"CY","TargetSid":"S-1-5-21-2385618916-250871166-2127620695-2107","TargetUserName":"Deleted Users"}}

JSON_EXTRACT(data,'$.EventData.SubjectUserName') = Data.EventData.SubjectUserName = Administrator 

If you were to read JSON_EXTRACT(data, '$.EventData.SubjectUserSid') with the above JSON, what would it return? 

Results


Happy Querying!



added image
[edited by: JeramyKopacko at 5:09 PM (GMT -7) on 31 May 2022]