Why time zone conversion does not always work (Zscaler ZPA)?

[MSY]

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?

[bowesmana · Accepted solution]

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.

[MSY]

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:

[bowesmana]

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

• %Y-%m-%d takes the date
• T matches T
• %H:%M:%S matches h/m/s of time
• . matches .
• %Q matches the 3 digit milliseconds
• Z matches Z
• %z matches the added 0000 which says that the timezone offset of that time string is 0000, i.e. it treats it as UTC.

[MSY]

Ah! Thank you again for taking the time to explain this. Much appreciated!