The Sophos UK Sales engineering team has been getting familiar with live discover. In the work they explored group policy and provided the following queries:
Deleted security groups -
Variable to specify the number of days to checkWindows
/* Deleted Security Groups */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 'Deleted Groups'FROM sophos_windows_eventsWHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND source = 'Security' AND eventid IN('4730', '4734', '4758');
Locked Accounts -
/* Locked Accounts */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 'Locked Account'FROM sophos_windows_eventsWHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND source = 'Security' AND eventid = 4740;
New Security Groups -
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 Group'FROM sophos_windows_eventsWHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND source = 'Security' AND eventid IN ('4727', '4754', '4731');
New User Accounts -
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_eventsWHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND source = 'Security' AND eventid = 4720;
Unlocked Accounts -
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 'Unlocked Account'FROM sophos_windows_eventsWHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND source = 'Security' AND eventid = 4767;
User Account was disabled -
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 'Disabled Account'FROM sophos_windows_eventsWHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND source = 'Security' AND eventid = 4725;
User Account was enabled-
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 'Enabled Account'FROM sophos_windows_eventsWHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND source = 'Security' AND eventid = 4722;
User password reset-
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 'user password reset'FROM sophos_windows_eventsWHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND source = 'Security' AND eventid IN ('4723','4724');
User Added to Security Group-
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.MemberName') AS 'User/Group Added', JSON_EXTRACT(data, '$.EventData.TargetUserName') AS 'Group Changed'FROM sophos_windows_eventsWHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND source = 'Security' AND eventid IN ('4728','4732','4756');
User Removed from Security Group-
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.MemberName') AS 'User/Group Added', JSON_EXTRACT(data, '$.EventData.TargetUserName') AS 'Group Changed'FROM sophos_windows_eventsWHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND source = 'Security' AND eventid IN ('4729','4733','4757');