Splunk Search

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

MSY
Explorer

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:

Zscaler.png

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?

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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
Explorer

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:

Splunk Timezone Conversion.png

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...