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
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.
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
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)
{"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?
Happy Querying!