I've been working on a Dashboard/Query that takes two date/time values (UTC) from Zscaler ZPA logs and converts to local timezone (PST). Some entries have a blank Time_Disconnected value and I do not know why.
Original (Zscaler):
TimestampAuthentication=2023-01-31T16:51:09.000Z
TimestampUnAuthentication=2023-01-31T17:19:05.169Z
Query:
| rename TimestampAuthentication AS Time_Auth, TimestampUnAuthentication AS Time_Disconn
| eval Time_Authenticated=strftime(strptime(Time_Auth, "%Y-%m-%dT%H:%M:%S.%z"), "%Y-%m-%d %H:%M:%S")
| eval Time_Disconnected=strftime(strptime(Time_Disconn, "%Y-%m-%dT%H:%M:%S.%z"), "%Y-%m-%d %H:%M:%S")
| sort -_time
| table _time, Time_Auth, Time_Authenticated, Time_Disconn, Time_Disconnected
(Time_Auth and Time_Disconn are the raw values)
Result:
Why is it that the last entry does not have the Time_Disconnected field populated? I have seen a few of those conversions not working. Is my query incorrectly formatted in some way?
Interesting...
Although your use of %z is wrong in that it is being used to interpret time offset and it is looking at the milliseconds to determine offset, I did think that Z was used to infer a UTC timestamp.
However, to fix your issue, use
| eval Time_Authenticated_D_Solution=strftime(strptime(Time_Auth."0000", "%FT%T.%QZ%z"), "%Y-%m-%d %H:%M:%S")
| eval Time_Disconnected_D_Solution=strftime(strptime(Time_Disconn."0000", "%FT%T.%QZ%z"), "%Y-%m-%d %H:%M:%S")
The %F is shorthand for %Y-%m-%d and %T is shorthand for %H:%M:%S
Also, %Q is the notation for 3 digit milliseconds.
Adding the "0000" to the base string and using %QZ%z to match the ms + Z + the new 0000 as TZ offset will then force the time offset to be UTC.
Interesting...
Although your use of %z is wrong in that it is being used to interpret time offset and it is looking at the milliseconds to determine offset, I did think that Z was used to infer a UTC timestamp.
However, to fix your issue, use
| eval Time_Authenticated_D_Solution=strftime(strptime(Time_Auth."0000", "%FT%T.%QZ%z"), "%Y-%m-%d %H:%M:%S")
| eval Time_Disconnected_D_Solution=strftime(strptime(Time_Disconn."0000", "%FT%T.%QZ%z"), "%Y-%m-%d %H:%M:%S")
The %F is shorthand for %Y-%m-%d and %T is shorthand for %H:%M:%S
Also, %Q is the notation for 3 digit milliseconds.
Adding the "0000" to the base string and using %QZ%z to match the ms + Z + the new 0000 as TZ offset will then force the time offset to be UTC.
Thanks so much! That did it. Good to know about the %Q corresponding to the 3-digit milliseconds and the %F and %T.
To map it out without the shorthand notation:
| eval Time_Authenticated=strftime(strptime(Time_Auth, "%Y-%m-%dT%H:%M:%S.%z"), "%Y-%m-%d %H:%M:%S")
| eval Time_Authenticated_D=strftime(strptime(Time_Auth."0000", "%Y-%m-%dT%H:%M:%S.%QZ%z"), "%Y-%m-%d %H:%M:%S")
| eval Time_Disconnected=strftime(strptime(Time_Disconn, "%Y-%m-%dT%H:%M:%S.%z"), "%Y-%m-%d %H:%M:%S")
| eval Time_Disconnected_D=strftime(strptime(Time_Disconn."0000", "%Y-%m-%dT%H:%M:%S.%QZ%z"), "%Y-%m-%d %H:%M:%S")
I'm confused about the "0000" explanation though.
Result:
The purpose of adding the 0000 to the existing time string is to convert this string
2023-01-25T02:23:05.369Z
to this string
2023-01-25T02:23:05.369Z0000
so when you use the time format string of
"%Y-%m-%dT%H:%M:%S.%QZ%z"
it maps like this
Ah! Thank you again for taking the time to explain this. Much appreciated!