Thread Info
Options
Suggested
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Need help with a query for login users per machine

PK1

Hello Team,

I am looking for a data lake query that will help me with login history of users per machine.

Thanks,

PK1



This thread was automatically locked due to age.
  • 0

    I wonder if you can use "Successful logon (Data Lake)" as the starting point, e.g.

    SELECT meta_hostname AS ep_name, subject_username
    FROM xdr_data WHERE query_name = 'windows_event_successful_logon'
    GROUP by meta_hostname, subject_username

    You should be able to then export the results as a CSV, import that into Excel and create a pivot table where ep_name and subject_username are the rows, etc.. Does that work?

    • 0

      Hi  

      Thanks for reaching out to the Sophos Community Forum.

      You can run this custom query to get a successful login history from the device.

      Fullscreen
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      SELECT
      STRFTIME('%Y-%m-%dT%H:%M:%SZ', DATETIME(time, 'unixepoch')) AS date_time,
      eventid,
      'Successful Login' AS description,
      COALESCE(
      JSON_EXTRACT(data, '$.UserData.Param1'),
      JSON_EXTRACT(data, '$.UserData.User'),
      JSON_EXTRACT(data, '$.EventData.TargetUserName')
      ) AS username,
      COALESCE(
      JSON_EXTRACT(data, '$.UserData.Param3'),
      JSON_EXTRACT(data, '$.UserData.Address'),
      JSON_EXTRACT(data, '$.EventData.IpAddress')
      ) AS source_ip,
      COALESCE(
      JSON_EXTRACT(data, '$.EventData.TargetUserSid'),
      '-'
      ) AS user_sid
      FROM sophos_windows_events
      WHERE
      eventid IN (1149, 21, 4624)
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      By Default, this query pulls data for the last 7 days, 

      If a post solves your question, please use the " Verify Answer " button.    

      Regards,

      Altmash