Advanced Threat Hunting with Cortex XQL

Taking XQL Further

In our previous guide, we covered the basics of Cortex XQL. Now, it's time to dive into the advanced features that turn a simple search into a powerful threat-hunting tool. We'll focus on data transformation, advanced filtering, and the elusive join operation.

1. Complex Data Transformation with alter

Sometimes the data in your logs isn't in the format you need. The alter stage allows you to create new fields on the fly.

Extracting Substrings

If you have a file path and you only want the file name:

dataset = xdr_data
| filter action_file_path != null
| alter file_name = arraylast(split(action_file_path, "\"))
| fields file_name, action_file_path

2. Advanced Aggregations with comp

Aggregations are vital for identifying anomalies (e.g., a single user logging into 50 different machines).

Detecting Horizontal Movement

dataset = xdr_data
| filter event_type = "LOGIN"
| comp count(endpoint_name) as distinct_login_count by actor_effective_username
| filter distinct_login_count > 10
| sort desc distinct_login_count

3. Mastering the join Operation

Joins allow you to correlate data across different datasets. This is where XQL truly shines.

Correlating Process Activity with Network Traffic

Suppose you find a suspicious process and want to see if it communicated externally:

dataset = xdr_data
| filter actor_process_image_name == "powershell.exe"
| join (dataset = pan_traffic_raw) as traffic on traffic.source_ip = xdr_data.endpoint_ip
| filter traffic.destination_port == 443
| fields xdr_data.endpoint_name, xdr_data.actor_process_command_line, traffic.destination_ip

4. Advanced Window Functions

Window functions allow you to perform calculations across a set of rows related to the current row.

Calculating Time Deltas between Events

dataset = xdr_data
| filter actor_effective_username = "admin"
| sort asc _time
| alter time_diff = _time - prev(_time)
| filter time_diff < 5000 // events occurring within 5 seconds of each other

5. Using bin for Time-Series Analysis

If you want to visualize data over time, use bin to group timestamps.

dataset = xdr_data
| filter event_type = "FILE"
| alter time_bucket = bin(_time, 1h)
| comp count(event_id) as file_ops_per_hour by time_bucket

Best Practices for Performance

1. Filter Early: Use filter as close to the dataset line as possible to reduce the amount of data processed.
2. Be Specific: Avoid dataset = * if you know exactly which log source you need.
3. Limit Fields: Only use the fields you actually need to see in the final output.

Conclusion

Advanced XQL is about more than just finding data—it's about manipulating and correlating it to tell a complete story of what happened in your environment. These advanced techniques are what separate a standard security analyst from an elite threat hunter.

Happy Hunting!