List of RDP Sessions in last N Days

  REVIEWED by Sophos 

This query takes a variable called 'Days to look back from now'  and searches the windows event logs for evenit ID 1149 then uses JSON extract to get the username and remote IP address info for the remote terminal sessions.

   datetime(time,'unixepoch') 'Date-Time',
   (strftime('%s','now')-time)/(3600*24) 'Days ago' ,eventid, 'TS Remote' AS Source,
   JSON_EXTRACT(data, '$.UserData.Param1') AS Name,
   JSON_EXTRACT(data, '$.UserData.Param2') AS Source_Machine_Network,
   JSON_EXTRACT(data, '$.UserData.Param3') AS Source_IP
FROM sophos_windows_events
WHERE source = 'Microsoft-Windows-TerminalServices-RemoteConnectionManager/Operational' AND
   eventid = 1149 AND
   time > strftime('%s', 'now', '-$$Days to look back from now$$ days');