topic Re: How to use XQL parse_timestamp in Cortex XDR Discussions

How to use XQL parse_timestamp

unlucky — Thu, 17 Aug 2023 15:38:38 GMT

I am trying to convert a string to a timestamp object and cannot understand how the parse_timestamp function works.

My string is as follow :

"2023-08-17T17:40:38.000246+0300"

My XQL query is as follow :

alter

timestamp = parse_timestamp("%Y-%m-%dT%H:%M:%S", format_string("%s", <field containing the timestamp string>))

This is clearly not the right way to use this function, but I cannot find any clear example showing how the function works...
Does anybody have a working example and can show their input and output ?

The documentation is not helpful

Re: How to use XQL parse_timestamp

nsinghvirk — Fri, 18 Aug 2023 16:22:32 GMT

Hello @unlucky ,

Thanks for reaching out on LiveCommunity!

I think the function you are looking for is format_timestamp in place of format_string because your data is already in timestamp format.

Lets understand the definition of these 3 functions.

parse_timestamp() -> The parse_timestamp() function returns a TIMESTAMP object after converting a string representation of a timestamp.

format_timestamp() -> The format_timestamp() function returns a string after formatting a timestamp according to a specified string format.

format_string() -> The format_string() function returns a string from a format string that contains zero or more format specifiers, along with a variable length list of additional arguments that matches the format specifiers.

Since the string you are trying to convert is already a timestamp hence please use format_timestamp function which will take a timestamp and return a string to parse_timestamp function to convert it to a timestamp of your choice. I have tried below line and its working for me.

alter time_test = parse_timestamp("%Y/%m/%d %H:%M:%S", format_timestamp("%Y/%m/%d %H:%M:%S", _time))

Below are the reference link for above functions.

https://docs-cortex.paloaltonetworks.com/r/Cortex-XDR/Cortex-XDR-XQL-Language-Reference/parse_timestamp

https://docs-cortex.paloaltonetworks.com/r/Cortex-XDR/Cortex-XDR-XQL-Language-Reference/format_timestamp

https://docs-cortex.paloaltonetworks.com/r/Cortex-XDR/Cortex-XDR-XQL-Language-Reference/format_string

Re: How to use XQL parse_timestamp

unlucky — Fri, 18 Aug 2023 17:15:39 GMT

ErrorField timestamp for function format_timestamp is invalid. Expected date but received string.

As I was saying, even though my string contains a timestamp, it is still a string!

Maybe there is something I am not understanding here, but what I am trying to achieve is to convert this string to a date type.