[QueryCorner][May2022] Audit User and Group Activity

Disclaimer: This information is provided as-is for the benefit of the Community. Please contact Sophos Professional Services if you require assistance with your specific environment.

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 PeterM 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 Disclaimer
[edited by: GlennSen at 3:26 PM (GMT -7) on 5 Apr 2023]