topic Re: XQL 2 Datasets in Cortex XDR Discussions

XQL 2 Datasets

J.Gammara — Mon, 20 Jan 2025 16:46:11 GMT

Hello community,

I am reaching out to you after many hours of trying to get this XQL query but something is not working.

I need to join the IP address from endpoints to my query

dataset = management_auditing
| filter description contains "SOX" and (description contains "assign tags" or description contains "remove tags")
| alter HOSTNAME = if(description contains "assign tags", ltrim(description, "Assign tags (SOX) for"), ltrim(description, "Remove tags (SOX) for"))
| alter ACTION_TYPE = if(description contains "assign tags", "Assign", "Remove")
| join ( dataset = endpoints | fields ip_address as Ipaddress) as endpoints endpoints.Ipaddress = source_ip
| fields _time, ACTION_TYPE as Tipo_Accion, HOSTNAME as Hostname, description as Actividad_Realizada, source_ip as IP, user_name, management_auditing_result as Estado_Accion

however it is not working as it is saying that the Join statement needs to be fixed, I would appreciate your assistance with checking what could be wrong with this Query

Re: XQL 2 Datasets

jmazzeo — Mon, 20 Jan 2025 20:10:10 GMT

Hi @J.Gammara, thanks for reaching us using the Live Community.

The "ip_address" field in the "endpoints" dataset is an array, you need to get all the values from the array first to compare the values.

You can check the dataset schemas by running a query to the dataset without any other stage, and going to the "Schema" tab:

If this post answers your question, please mark it as the solution.

Re: XQL 2 Datasets

J.Gammara — Mon, 20 Jan 2025 21:22:23 GMT

Thank you so much for your reply,

what happens if the field is an array? what function should I use in that situation or where can I read more about it ?