specifying "timezone" in VQL function "timestamp"

[Herbert-Karl]

While playing around with the VQL function timestamp, I noticed that I was unable to affect the output using the parameter "timezone".

compare the results from

• SELECT timestamp(string="Thu Aug 29 2024 21:03") FROM scope()
• SELECT timestamp(string="Thu Aug 29 2024 21:03",timezone="CEST") FROM scope()
• SELECT timestamp(string="Thu Aug 29 2024 21:03",timezone="+0200") FROM scope()
• each executed interactivey through the Velo server feature to run VQL queries directly on the client
• each run returns the same value: 2024-08-29T21:03:00Z

technical details: using Velociraptor agent version 0.7.1, running on freebsd14.1-release-p3, amd64

Im unsure if Im using the parameter wrongly and would like some feedback on this.

My specific usecase is that Im parsing some other data where I get a string representing a timestamp in localtime (e. g. "Thu Aug 29 2024 21:03"). I want to parse that string into a timestamp while at the same time using the right timezone (here currently "CEST" for me), so that the timestamp is not shifted.

[scudette]

Thanks for filing this issue - indeed the Timezone parameter is deprecated and doesn't do anything.

In Velociraptor all times are stored in a Golang time.Time object - which stores both the time and the relevant timezone. Therefore internally times are never ambiguous and specifying a timezone is meaningless and does nothing.

The only time a timezone hint is needed is when we parse timestamps from a different timezone and it is ambiguous. You can provide this hint using the vql PARSE_TZ variable which is a hint for all timestamp parsing operations.

For example consider the following example

LET PARSE_TZ <= "local"

SELECT timestamp(string="Thu Aug 29 2024 21:03") ,
       timestamp(string="Thu Aug 29 2024 21:03 CEST") FROM scope()

The time Thu Aug 29 2024 21:03 is ambiguous since it has no timezone, So in this case the PARSE_TZ variable says - if the timezone looks ambiguous then assume it is in local time.

The second timestamp Thu Aug 29 2024 21:03 CEST is not ambiguous and will always be parsed correctly so PARSE_TZ has no effect.

You can also specify any specific timezone for PARSE_TZ - for example "Europe/Berlin", although most of the time it will be "local" which uses the local machine timezone (unless you are parsing say an external log file with ambiguous timestamps and you know the timezone).

Of course timestamps can be presented in the GUI in any timezone you want by changing the timezone in the user preferences screen.

When data is serialized to JSON times are always encoded in UTC so if you look at the JSON files on disk all times will be in UTC.

[predictiple]

Is there an easy way to check what timezone the client thinks it's in? It doesn't appear to be collected by the info plugin.

[scudette]

It normally should not matter what is the local timezone right?

If you really had to you can do this:

[predictiple]

Normally yes. If the endpoint is has a misconfigured timezone and it wrote the timestamps that we later need to parse using the "local" hint, then the misconfiguration should be a self-correcting. It will be a problem is someone fixes the endpoint's timezone after the data is written.

Should be a very rare situation though since modern OSes tend to set their TZ via geolocation.

[Herbert-Karl]

Thanks for filing this issue - indeed the Timezone parameter is deprecated and doesn't do anything.

In Velociraptor all times are stored in a Golang time.Time object - which stores both the time and the relevant timezone. Therefore internally times are never ambiguous and specifying a timezone is meaningless and does nothing.

The only time a timezone hint is needed is when we parse timestamps from a different timezone and it is ambiguous. You can provide this hint using the vql PARSE_TZ variable which is a hint for all timestamp parsing operations.

For example consider the following example

LET PARSE_TZ <= "local"

SELECT timestamp(string="Thu Aug 29 2024 21:03") ,
       timestamp(string="Thu Aug 29 2024 21:03 CEST") FROM scope()

The time Thu Aug 29 2024 21:03 is ambiguous since it has no timezone, So in this case the PARSE_TZ variable says - if the timezone looks ambiguous then assume it is in local time.

The second timestamp Thu Aug 29 2024 21:03 CEST is not ambiguous and will always be parsed correctly so PARSE_TZ has no effect.

You can also specify any specific timezone for PARSE_TZ - for example "Europe/Berlin", although most of the time it will be "local" which uses the local machine timezone (unless you are parsing say an external log file with ambiguous timestamps and you know the timezone).

Of course timestamps can be presented in the GUI in any timezone you want by changing the timezone in the user preferences screen.

When data is serialized to JSON times are always encoded in UTC so if you look at the JSON files on disk all times will be in UTC.

Thanks for the explanation. I tried with PARSE_TZ and it works for my use case. :) Maybe the documentation of the function could mention that local time timestamps can be converted properly by setting the variable PARSE_TZ