topic Re: How to convert epoch time to a desired time zone? in Splunk Search

How to convert epoch time to a desired time zone?

sekhar463 — Thu, 19 Oct 2023 12:59:55 GMT

from below events how to convert epoch time to a desired time zone

want to convert LAST_START="1670326641", LAST_END="1670326670", NEXT_START="1670412600", into desired time zone based on TIMEZONE field

Re: how to convert epoch time to a desired time zone

yuanliu — Wed, 07 Dec 2022 18:00:43 GMT

You will need a lookup table to interpret TIMEZONE as SPL does not provide such meta data., e.g.,

TIMEZONE	offset
ZULU	0
CENTRAL	-6
PACIFIC	-8

(I know the wording is extremely US-centric - but that's how your data look like.) Let's call this table timezone. Then, you just perform a lookup and calculate.

| lookup timezone TIMEZONE output offset | foreach LAST_* NEXT_* [ fieldformat <<FIELD>> = strftime(<<FIELD>> + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")") ]

Re: How to convert epoch time to a desired time zone?

sekhar463 — Thu, 08 Dec 2022 07:59:06 GMT

hai i didnt understand this.

can you tell me clearly

Re: How to convert epoch time to a desired time zone?

sekhar463 — Thu, 08 Dec 2022 08:09:24 GMT

hi can you give me complete query as an example after lookup file creation

Re: How to convert epoch time to a desired time zone?

yuanliu — Fri, 09 Dec 2022 01:26:57 GMT

You can follow this document to set up a lookup: Use lookups in Splunk Web; per Define a CSV lookup in Splunk Web:

CSV lookups are best for small sets of data. The general workflow for creating a CSV lookup in Splunk Web is to upload a file, share the lookup table file, and then create the lookup definition from the lookup table file.

Hope this helps.

Re: How to convert epoch time to a desired time zone?

sekhar463 — Fri, 09 Dec 2022 09:38:53 GMT

hi small query

i have created lookup file and lookup definition

while creating automatic lookup what should i keep for Lookup input fields and Lookup output field

below is the query i was using and getting error

sourcetype=autosys_POC
| lookup timezone TIMEZONE output offset
| foreach LAST_* NEXT_*
| fieldformat STARTTIME = strftime(LAST_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")

Re: How to convert epoch time to a desired time zone?

yuanliu — Fri, 09 Dec 2022 10:28:51 GMT

foreach opens a subsearch; you need square brackets around it.

| lookup timezone TIMEZONE output offset | foreach LAST_* NEXT_* [ fieldformat <<FIELD>> = strftime(<<FIELD>> + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")") ]

You can then rename those fields as you like. If you don't want to use foreach loop, you can spell them out individually, i.e.,

| lookup timezone TIMEZONE output offset | fieldformat STARTTIME = strftime(LAST_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")") | fieldformat ENDTIME = strftime(LAST_END + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")") | fieldformat NEXTTIME = strftime(NEXT_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")

Re: How to convert epoch time to a desired time zone?

sekhar463 — Fri, 09 Dec 2022 12:47:10 GMT

Hai tried but getting error while using the search below.

i guess automatic lookup is incorrect, can you tell me the input and output fields for automatic lookup

sourcetype=autosys_POC
| lookup Timezone TIMEZONE output offset
| fieldformat STARTTIME = strftime(LAST_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")
| fieldformat ENDTIME = strftime(LAST_END + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")
| fieldformat NEXTTIME = strftime(NEXT_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")

Error in 'lookup' command: Could not construct lookup 'Timezone, TIMEZONE, output, offset'. See search.log

The lookup table 'Timezone' does not exist or is not available.

The search job has failed due to an error. You may be able view the job in the

Re: How to convert epoch time to a desired time zone?

yuanliu — Fri, 09 Dec 2022 20:03:37 GMT

The sceenshot suggests that you named the lookup "timezonelookupdefine", not "Timezone". If so,

sourcetype=autosys_POC | lookup timezonelookupdefine TIMEZONE output offset | fieldformat STARTTIME = strftime(LAST_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")") | fieldformat ENDTIME = strftime(LAST_END + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")") | fieldformat NEXTTIME = strftime(NEXT_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")

From lookup#Syntax

Syntax
The required syntax is in bold.
lookup [local=<bool>] [update=<bool>] <lookup-table-name> ( <lookup-field> [AS <event-field>] )... [ OUTPUT | OUTPUTNEW (<lookup-destfield> [AS <event-destfield>] )... ]

Re: How to convert epoch time to a desired time zone?

yuanliu — Sat, 10 Dec 2022 05:16:43 GMT

Side note: You do not need to set up automatic lookup for this task unless there is a genuine need to.

Re: How to convert epoch time to a desired time zone?

sekhar463 — Mon, 12 Dec 2022 09:32:54 GMT

Hai, i have updated the search but not getting new filelds created.

index=ivz_onboarding_css_autosys source=Autosyscss1
| lookup timezonelookupdefine TIMEZONE output offset
| fieldformat STARTTIME = strftime(LAST_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")
| fieldformat ENDTIME = strftime(LAST_END + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")
| fieldformat NEXTTIME = strftime(NEXT_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")

resulted event :

2022-12-12 03:26:06.881, JOID="119829", JOB_NAME="PNC-PMT-UI-DATA-CACHE-REFRESH-GMAG-FUNDS", JOB_GROUP="PMT", TIMEZONE="EST5EDT", RUN_NUM="403234100", NTRY="0", RUN_MACHINE=" ", STATUS_CODE="9", STATUS="ACTIVATED", LAST_START="0", LAST_END="1670579164", NEXT_START="1670585400", DATE_CONDITIONS="1", DAYS_OF_WEEK="mo,tu,we,th,fr", EXCLUDE_CALENDAR="EST-NYSE-HOLIDAYS", RUNTIME="1670579164", EXIT_CODE="-656"

Re: How to convert epoch time to a desired time zone?

yuanliu — Tue, 13 Dec 2022 04:46:05 GMT

2022-12-12 03:26:06.881, JOID="119829", JOB_NAME="PNC-PMT-UI-DATA-CACHE-REFRESH-GMAG-FUNDS", JOB_GROUP="PMT", TIMEZONE="EST5EDT", RUN_NUM="403234100", NTRY="0", RUN_MACHINE=" ", STATUS_CODE="9", STATUS="ACTIVATED", LAST_START="0", LAST_END="1670579164", NEXT_START="1670585400", DATE_CONDITIONS="1", DAYS_OF_WEEK="mo,tu,we,th,fr", EXCLUDE_CALENDAR="EST-NYSE-HOLIDAYS", RUNTIME="1670579164", EXIT_CODE="-656"

Here, the event's TIMEZONE is EST5EDT. Does your timezonelookupdefine contain this timezone? A simple way to test is

| makeresults | eval TIMEZONE = "EST5EDT" | lookup timezonelookupdefine TIMEZONE output offset

A side note about EST5EDT: You realize that this poses an additional challenge in that offset is 5 in the winter and 4 in the summer, right? (At least before the new law takes effect next year.)

Re: How to convert epoch time to a desired time zone?

sekhar463 — Tue, 13 Dec 2022 08:48:06 GMT

hi i have added timezone for EST in lookup and doing search but its not creating new fields

STARTTIME,ENDTIME ,NEXTTIME

index=ivz_onboarding_css_autosys source=Autosyscss1
| eval NEW_START_TIME=strftime(LAST_START,"%Y/%m/%d %H:%M:%S")
| eval NEW_END_TIME=strftime(LAST_END,"%Y/%m/%d %H:%M:%S")
| eval NEW_NEXT_TIME=strftime(NEXT_START,"%Y/%m/%d %H:%M:%S")
| lookup timezonelookupdefine TIMEZONE output offset
| fieldformat STARTTIME = strftime(NEW_START_TIME + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")
| fieldformat ENDTIME = strftime(NEW_END_TIME + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")
| fieldformat NEXTTIME = strftime(NEW_NEXT_TIME + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")

Re: How to convert epoch time to a desired time zone?

yuanliu — Tue, 13 Dec 2022 10:01:06 GMT

index=ivz_onboarding_css_autosys source=Autosyscss1
| eval NEW_START_TIME=strftime(LAST_START,"%Y/%m/%d %H:%M:%S")
| eval NEW_END_TIME=strftime(LAST_END,"%Y/%m/%d %H:%M:%S")
| eval NEW_NEXT_TIME=strftime(NEXT_START,"%Y/%m/%d %H:%M:%S")
| lookup timezonelookupdefine TIMEZONE output offset
| fieldformat STARTTIME = strftime(NEW_START_TIME + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")
| fieldformat ENDTIME = strftime(NEW_END_TIME + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")
| fieldformat NEXTTIME = strftime(NEW_NEXT_TIME + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")")

Of course you wouldn't have new fields. You are trying to apply strftime on string fields NEW_START_TIME, etc. If you have copied my sample, it would have given you results.

To illustrate, I have made a sample lookup "timezonelookupdefine" with these four:

TIMEZONE	offset
ZULU	0
EST5EDT	-5
CENTRAL	-6
PACIFIC	-8

(I'm using a simple offset for EST5EDT here. Handling daylight savings time will drain your brain, as it has drained many a developers'.) Then, I run the following against your sample data:

| lookup timezonelookupdefine TIMEZONE output offset | fieldformat STARTTIME = strftime(LAST_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")") | fieldformat ENDTIME = strftime(LAST_END + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")") | fieldformat NEXTTIME = strftime(NEXT_START + tonumber(offset), "%F %H:%M:%S (".TIMEZONE.")") ``` replace fieldformmat with eval if you want to use these as fields ```

Note: No strftime before lookup! This is the output

DATE_CONDITIONS

DAYS_OF_WEEK

EXCLUDE_CALENDAR

EXIT_CODE

JOB_GROUP

JOB_NAME

JOID

LAST_END

LAST_START

NEXT_START

NTRY

RUNTIME

RUN_MACHINE

RUN_NUM

STATUS

STATUS_CODE

TIMEZONE

offset

ENDTIME

NEXTTIME

STARTTIME

mo,tu,we,th,fr

EST-NYSE-HOLIDAYS

-656

PMT

PNC-PMT-UI-DATA-CACHE-REFRESH-GMAG-FUNDS

119829

1670579164

1670585400

1670579164

403234100

ACTIVATED

EST5EDT

-5

2022-12-09 01:45:59 (EST5EDT)

2022-12-09 03:29:55 (EST5EDT)

1969-12-31 15:59:55 (EST5EDT)

(If you want to use the display values as field for future calculation, use eval instead of fieldformat.)

As shown in the above screenshot, I emulated your last illustrated input using

| makeresults | fields - _time | eval _raw = "2022-12-12 03:26:06.881, JOID=\"119829\", JOB_NAME=\"PNC-PMT-UI-DATA-CACHE-REFRESH-GMAG-FUNDS\", JOB_GROUP=\"PMT\", TIMEZONE=\"EST5EDT\", RUN_NUM=\"403234100\", NTRY=\"0\", RUN_MACHINE=\" \", STATUS_CODE=\"9\", STATUS=\"ACTIVATED\", LAST_START=\"0\", LAST_END=\"1670579164\", NEXT_START=\"1670585400\", DATE_CONDITIONS=\"1\", DAYS_OF_WEEK=\"mo,tu,we,th,fr\", EXCLUDE_CALENDAR=\"EST-NYSE-HOLIDAYS\", RUNTIME=\"1670579164\", EXIT_CODE=\"-656\"" | extract | fields - _raw ``` data emulation above ```

All of these you can test for yourself. Except you still need to make sure that the lookup table contains the correct timezone strings and offset values, and the lookup is spelled correctly.

Re: How to convert epoch time to a desired time zone?

sekhar463 — Tue, 13 Dec 2022 11:39:29 GMT

hai Thanks

i have confused, i am using the query but not getting fields added.

could you give complete search