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.")")
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 |
| 1 | mo,tu,we,th,fr | EST-NYSE-HOLIDAYS | -656 | PMT | PNC-PMT-UI-DATA-CACHE-REFRESH-GMAG-FUNDS | 119829 | 1670579164 | 0 | 1670585400 | 0 | 1670579164 | 403234100 | ACTIVATED | 9 | 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.
