XQL to get details of endpoint connection time

[a.martinez578319]

Hello Team,

I need to create a report in Cortex XDR to identify endpoint connection activity within a specific time window.

The requirement is not to know when the endpoint was first registered in Cortex, so `first_seen` does not apply here.

What I need is to identify:

1. Endpoints that changed from DISCONNECTED to CONNECTED during the selected time window.
2. The timestamp when that DISCONNECTED-to-CONNECTED transition happened.
3. Endpoints that were already CONNECTED before the selected time window and are still CONNECTED at the time the query is executed.
4. For endpoints that were already CONNECTED before the selected time window, I also need to know the original time when they became CONNECTED.

The challenge I am facing is that `last_seen` appears to be continuously updated while the endpoint remains connected. Because of that, if an endpoint connected at 7:00 AM and is still connected at 7:55 AM, `last_seen` may show 7:55 AM instead of the original time when the endpoint became CONNECTED.

So I am trying to determine whether Cortex XDR stores the actual status transition event, specifically when an endpoint changed from DISCONNECTED to CONNECTED, and whether this can be queried using XQL, Agent Audit Logs, or another built-in dataset.

In short, I am looking for a way to report both:

- Endpoints that became CONNECTED during the selected time window, including the time when the transition happened.

- Endpoints that were already CONNECTED before the selected time window and are still CONNECTED when the query runs, including the original time when they became CONNECTED.

Regards.

[susekar]

Hello @a.martinez578319 ,

Greetings for the day.

Cortex XDR stores agent connection state changes in the Agent Audit Logs, which can be queried via XQL using the agent_audit_logs dataset. This allows you to identify specific transitions from DISCONNECTED to CONNECTED and see the exact timestamp of that event.

Connection Status Logic in Cortex XDR:

• Transition Event: When an agent reconnects, it generates an audit log entry with a specific description (e.g., "Agent connected").
• last_seen vs. Connection Time: You are correct that last_seen is a heartbeat field that updates continuously while the agent is online. To find the start of the connection, you must look for the Transition Event in the audit logs.
• Disconnected Thresholds: By default, an agent is marked "Disconnected" after 10 minutes of silence and "Connection Lost" after 30 days.
  

  
  (XQL Query for Connection Reporting)
  

To satisfy your specific requirements, you can use the following XQL query logic. It focuses on the agent_audit_logs to capture transitions and the endpoints dataset for current status.

1. Endpoints that transitioned during the time window

This query identifies the exact moment an agent changed from a disconnected state back to connected.

config timeframe = $SELECTED_TIME_WINDOW
| dataset = agent_audit_logs
| filter (description contains "Agent connected" or description contains "successfully connected")
| fields _time as connection_event_time, endpoint_name, endpoint_id, description
| sort asc connection_event_time

2. Endpoints already CONNECTED (and their original connection time)

Since endpoints only shows the current state, finding the "original" time for endpoints that were already connected requires looking further back in the audit logs. You can join the current inventory with the historical connection events.

// Identify currently connected endpoints and find their EARLIEST 'connected' event
dataset = endpoints
| filter endpoint_status = "CONNECTED"
| fields endpoint_name, endpoint_id, last_seen
| join type = inner (
    dataset = agent_audit_logs
    | filter description contains "connected"
    | comp min(_time) as original_connection_time by endpoint_id
) as history history.endpoint_id = endpoint_id
| fields endpoint_name, history.original_connection_time, last_seen

Note on Reporting: For a comprehensive report, I recommend running the historical audit log query over a wider timeframe (e.g., 30 days) to ensure you capture the "original" connection event for agents that have been online for a long time.

If you feel this has answered your query, please let us know by clicking like and on "mark this as a Solution".

Thanks & Regards,
S. Subashkar Sekar