Splunk Search

Timezone offset causing blanks during conversion

rhilderbrand1
Observer

I have a date column that I'm trying to convert to %m/%d/%Y. The date stamp is a little complex but I got it to work until daylight savings took affect. Now anything with a timezone offset that has a non-zero number in the third digit, -0480 for example, returns blank. Below is my query...

| inputlookup DateStampConvert.csv
| rename "System Name" as systemName
| rename "Date Stamp" as DateStampDate
| eval dateStamp=strftime(strptime(DateStampDate, "%b %d %Y %H:%M:%S %z"), "%m/%d/%Y")
| table systemName dateStamp
| outputlookup dateStamp.csv

Is there something I'm missing?

Labels (1)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

-0480? What kind of a timezone spec is that?

0 Karma

rhilderbrand1
Observer

Not sure. Everything was fine during daylight savings time but on November 8, half of the devices started reporting their UTC time offsets in what I assume are minutes rather than hours and minutes. The messed up time offsets are all multiples of 60, for example -0480 or -0360. I suspect the use of %z is strictly with hour and minute formats so anything greater than 59 in the last two digits may throw it off. I guess I'll have to read up on rex or regex to pull the needed info out instead. 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

LOL. I just had a very similar case myself. See this thread https://community.splunk.com/t5/Getting-Data-In/Index-time-extraction-and-non-indexed-field/m-p/5777...

The difference is that you want to do the parsing/correction in search-time whereas I did it in index-time (since I had to correct _time).

But the general idea is the same - extract the main part of the event as UTC, extract the "timezone" part, cast it to number, multiply by 60 and add the resulting number to the main timestamp.

johnhuang
Motivator

Share some examples.

0 Karma
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...