Finding where the domain users group added to the remote desktop users local group

Hi Nandha, 

From the available Sophos Schema, we could leverage the account_activity or authentication_activity tables, which contain relevant information about user account actions and events.

To achieve our goal, we must focus on User Account, Authentication Events and cross-reference User IDs and Group. So when we look into tables like account_activity or authentication_activity track user actions and could potentially reveal group membership or authentication details related to domain users. Then we can track the relationship between users and group activities through event logs, as these might indicate when a user is added to a group like "Remote Desktop Users.

Here is a sample code for you. I did not test this because I did not have a feasible environment to test it.

SELECT
  aa.hostname,
  aa.target_username,
  aa.target_domain,
  aa.logon_process
FROM
  authentication_activity aa
WHERE
  aa.logon_process = 'Remote Desktop Services'
  AND aa.target_username = 'Domain Users'
  AND aa.category = 'authentication_activity';

You might need to refine this based on actual event data available in Central Data Lake. For instance, if you want all the domain users, consider using the wildcard character "%" in Sophos Central when creating this query. Although this is not a perfect query, I believe you have something to start with. 

Mark the answer if you find this helpful. 

Thank You

Adding to the cross reference mentioned in my previous message.   

Actually, we can combine both authentication data and cross-referencing event logs to get a more comprehensive output, you can create a query that leverages the event logs to find the group membership changes and then uses the authentication activity to identify when "Domain Users" actually logged in using Remote Desktop Services.

WITH GroupMembershipEvents AS (
SELECT
wl.hostname,
wl.time AS event_time,
wl.data AS event_data
FROM
win_event_log wl
WHERE
wl.data LIKE '%Remote Desktop Users%'
AND wl.data LIKE '%Domain Users%'
AND wl.eventid IN (4728, 4732, 4733, 4729)
),

AuthenticationEvents AS (
SELECT
aa.hostname,
aa.target_username,
aa.target_domain,
aa.logon_process,
aa.time AS auth_time
FROM
authentication_activity aa
WHERE
aa.logon_process = 'Remote Desktop Services'
AND aa.target_username = 'Domain Users'
AND aa.category = 'authentication_activity'
)

SELECT
gm.hostname,
gm.event_time AS group_event_time,
ae.auth_time AS login_time,
gm.event_data AS group_membership_event,
ae.target_username,
ae.target_domain,
ae.logon_process
FROM
GroupMembershipEvents gm
JOIN
AuthenticationEvents ae ON gm.hostname = ae.hostname
WHERE
ae.auth_time >= gm.event_time
ORDER BY
gm.event_time DESC, ae.auth_time DESC;

And again, you might need to refine this based on actual event data available in Central Data Lake. For instance, if you want all the domain users, consider using the wildcard character "%" in Sophos Central when creating this query.