For query assistance, please see the following Best Practices guide
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');