REVIEWED by Sophos
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 check
Windows
/* 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_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS') AND
source = 'Security' AND eventid IN('4730', '4734', '4758');
Locked Accounts -
Variable to specify the number of days to check
Windows
/* 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_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS')
AND source = 'Security'
AND eventid = 4740;
New Security Groups -
Variable to specify the number of days to check
Windows
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_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS')
AND source = 'Security'
AND eventid IN ('4727', '4754', '4731');
New User Accounts -
Variable to specify the number of days to check
Windows
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;
Unlocked Accounts -
Variable to specify the number of days to check
Windows
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_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS')
AND source = 'Security'
AND eventid = 4767;
User Account was disabled -
Variable to specify the number of days to check
Windows
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_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS')
AND source = 'Security'
AND eventid = 4725;
User Account was enabled-
Variable to specify the number of days to check
Windows
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_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS')
AND source = 'Security'
AND eventid = 4722;
User password reset-
Variable to specify the number of days to check
Windows
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_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS')
AND source = 'Security'
AND eventid IN ('4723','4724');
User Added to Security Group-
Variable to specify the number of days to check
Windows
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_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS')
AND source = 'Security'
AND eventid IN ('4728','4732','4756');
User Removed from Security Group-
Variable to specify the number of days to check
Windows
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_events
WHERE time > STRFTIME('%s','NOW','-$$Number of days to check$$ DAYS')
AND source = 'Security'
AND eventid IN ('4729','4733','4757');
