Splunk Search

How to convert epoch time to a desired time zone?

sekhar463
Path Finder

Hi

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

Labels (1)
0 Karma

sekhar463
Path Finder

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.")")

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.")")

 

0 Karma

sekhar463
Path Finder

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 
 
sekhar463_0-1670590015776.png

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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>] )... ]
0 Karma

sekhar463
Path Finder

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"

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.)

0 Karma

sekhar463
Path Finder

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.")")

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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:

TIMEZONEoffset
ZULU0
EST5EDT-5
CENTRAL-6
PACIFIC-8

Screen Shot 2022-12-13 at 1.25.08 AM.png

(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_CONDITIONSDAYS_OF_WEEKEXCLUDE_CALENDAREXIT_CODEJOB_GROUPJOB_NAMEJOIDLAST_ENDLAST_STARTNEXT_STARTNTRYRUNTIMERUN_MACHINERUN_NUMSTATUSSTATUS_CODETIMEZONEoffsetENDTIMENEXTTIMESTARTTIME
1mo,tu,we,th,frEST-NYSE-HOLIDAYS-656PMTPNC-PMT-UI-DATA-CACHE-REFRESH-GMAG-FUNDS11982916705791640167058540001670579164 403234100ACTIVATED9EST5EDT-52022-12-09 01:45:59 (EST5EDT)2022-12-09 03:29:55 (EST5EDT)1969-12-31 15:59:55 (EST5EDT)

Screen Shot 2022-12-13 at 1.47.59 AM.png

(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.

0 Karma

sekhar463
Path Finder

hai Thanks 

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

could you give complete  search 

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.")")

 

 

0 Karma

sekhar463
Path Finder

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

sekhar463
Path Finder

hai i didnt understand this.

 

can you tell me clearly

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

TIMEZONEoffset
ZULU0
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.")") ]

  

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...