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');

Anonymous