Splunk Search

Convert the timezone from a time field

lain179
Communicator

I have a field extracted from log entries, containing time values in GMT. Can I convert the field to PST time? If so, how can I accomplish that?

stats values(SearchUser) as User values(SearchStartDate) as "Start" values(SearchEndDate) as "End" by Project, SearchID



SearchStartDate and SearchEndDate need to be converted to PST.

Tags (1)
1 Solution

kristian_kolb
Ultra Champion

One way would be to make use of the strptime()/strftime() functions of eval, which will let you convert time from strings, e.g. 2013-05-03 12:23:34 to epoch (which is the time expressed as the number of seconds since midnight Jan 1, 1970). While that might seem odd, it makes addition/subtraction very easy.

So. Let's assume that your timestamps look like above, but for the wrong timezone, you can do like this;

your base search 
| eval start_e = strptime(SearchStartDate, "%Y-%m-%d %H:%M:%S") +36000 
| eval SearchStartDate = strftime(start_e, "%Y-%m-%d %H:%M:%S") 
| eval end_e = strptime(SearchEndDate, "%Y-%m-%d %H:%M:%S") +36000 
| eval SearchEndDate = strftime(end_e, "%Y-%m-%d %H:%M:%S") 
| the rest of your search as shown in your question

The 36000 is just the number of seconds (10 hours) which is the difference between the two timezones. NB. This is just my guessing, I don't remember if it's eight or nine hours diff.

strptime() makes the string into an integer, according to the specification
strftime() turns the number back into a string, according to the specification

Also, note that this will NOT change any data in the event, but just modify how it's presented.

Please see the following for more info;

http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/Commontimeformatvariables
http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/CommonEvalFunctions
http://www.strftime.net

Hope this helps,

Kristian

View solution in original post

kristian_kolb
Ultra Champion

One way would be to make use of the strptime()/strftime() functions of eval, which will let you convert time from strings, e.g. 2013-05-03 12:23:34 to epoch (which is the time expressed as the number of seconds since midnight Jan 1, 1970). While that might seem odd, it makes addition/subtraction very easy.

So. Let's assume that your timestamps look like above, but for the wrong timezone, you can do like this;

your base search 
| eval start_e = strptime(SearchStartDate, "%Y-%m-%d %H:%M:%S") +36000 
| eval SearchStartDate = strftime(start_e, "%Y-%m-%d %H:%M:%S") 
| eval end_e = strptime(SearchEndDate, "%Y-%m-%d %H:%M:%S") +36000 
| eval SearchEndDate = strftime(end_e, "%Y-%m-%d %H:%M:%S") 
| the rest of your search as shown in your question

The 36000 is just the number of seconds (10 hours) which is the difference between the two timezones. NB. This is just my guessing, I don't remember if it's eight or nine hours diff.

strptime() makes the string into an integer, according to the specification
strftime() turns the number back into a string, according to the specification

Also, note that this will NOT change any data in the event, but just modify how it's presented.

Please see the following for more info;

http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/Commontimeformatvariables
http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/CommonEvalFunctions
http://www.strftime.net

Hope this helps,

Kristian

kristian_kolb
Ultra Champion

Well, the TZ setting will AFAIK only work on the timestamp used for indexing purposes, so-to-speak, i.e. not for any other time data inside the events.

lain179
Communicator

Ok. Thanks.

0 Karma

bmacias84
Champion

I recommend setting your TZ (timezone) settings for each one of your sources or sourcetypes. Splunk will do the math for you. However this will not work on previously indexed data.

lain179
Communicator

I guess there is no function to convert the time directly instead of doing the Math?

Actually the time values in logs are in UTC. Because of the daylight saving, the difference is sometimes 7 hours and sometimes is 8 hours. So adding a constant number won't work too well.

Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...