Live Discover Query - Authentication History for a User

 REVIEWED by Sophos 

This is a Windows-only query that looks at the system events logs to see logon history for users. This will show how a user is authenticated on Windows endpoints and servers. You can expect to see locally connected users (Interactive) for users directly on a machine, and RemoteInteractive for RDP users.

Descriptions for the logon types comes from the Microsoft KBA:
https://docs.microsoft.com/en-us/windows/win32/api/ntsecapi/ne-ntsecapi-security_logon_type

SELECT DISTINCT
   eventid AS Event_ID,
   time AS Epoch_Time,
   datetime(time,'unixepoch') Data_Time,
   COUNT(task_message) Logon_Count,
   CASE json_extract(swe.data,'$.EventData.LogonType')
      WHEN '2' THEN 'Interactive'
      WHEN '3' THEN 'Network'
      WHEN '4' THEN 'Batch'
      WHEN '5' THEN 'Service'
      WHEN '7' THEN 'Unlock'
      WHEN '8' THEN 'NetworkClearText'
      WHEN '9' THEN 'NewCredentials'
      WHEN '10' THEN 'RemoteInteractive'
      WHEN '11' THEN 'CacheInteractive'
   END 'Logon_Type_Human',
   json_extract(swe.data,'$.EventData.IpAddress') IP_Address,
   json_extract(swe.data,'$.EventData.TargetUserName') Target_User_Name
FROM sophos_windows_events swe
WHERE eventID
   IN (4672,4624,4625)
      AND time > strftime('%s','now','-90 days')
      AND Target_User_Name LIKE '%$$UserName$$%'
GROUP BY Logon_Type_Human;

You'll see the variable $$UserName$$ which supports wildcards. So searching for the user 'admin' will pull events for administrator, IT_admin, etc.

Happy searching,
AM