Splunk Search

How to convert the time in raw data to different timezone?

Veeru
Path Finder

I have the raw data where i need to convert the time in raw data to particular time zone
example:if the time contains emea in it i need to convert to CST time.

his is the 3 conditions of time zone:
when emea => CEST/CST time
when apac => HKT time
when us=>EDT

6/10/22
9:39:00.000 AM
 
2022-06-10 15:39:00 emea
6/10/22
9:41:56.000 AM
 
2022-06-10 15:41:56 apac
6/10/22
9:41:56.000 AM
 
2022-06-10 15:41:56 us

 

Please help me on the query
Thank you in advance

Labels (4)
Tags (1)
0 Karma

JacekF
Path Finder

I'm not sure what do you mean by us, apac or emea time zones. But if you know what is the time difference between us and EDT, apac and HKT, emea and CST time zones, you can use relative_time function to make the time conversions:

| makeresults
| eval timestamp="2022-06-10 15:39:00 emea,2022-06-10 15:41:56 apac,2022-06-10 15:41:56 us"
| makemv timestamp delim=","
| mvexpand timestamp
| rex field=timestamp "\s(?<tz>[\w]+)$"
| eval time = strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| eval time = case(
    tz=="us", strftime(relative_time(time, "+2h"), "%m/%e/%Y %I:%M:%S %p")+" EDT",
    tz=="apac", strftime(relative_time(time, "-2h"), "%m/%e/%Y %I:%M:%S %p")+" HKT",
    tz=="emea", strftime(relative_time(time, "-6h"), "%m/%e/%Y %I:%M:%S %p")+" CST"
)

The values I've provided to relative_time for the time specifier argument, probably do not have much sense. You will have to adjust them to properly convert time zones.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

That's what I meant by nasty tricks. You're not rendering a given timestamp but you're adjusting the _time to a deliberately wrong value to render it to a wrong value _in your local timezone_ and then you add a predefined string.

 

0 Karma

JacekF
Path Finder

I'm not adjusting _time, just creating a new "time" field with converted time zone. I'm not sure if the question was about changing the value of the _time field.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

OK, you may not be modifying the _time field value itself. That's not the point. The point is that the value you're calculating the string representation is not equal to the _time value. It's skewed with a predefined offset.

0 Karma

JacekF
Path Finder

We don't know how those events look like. Maybe they include multiple time fields and the conversion needs to be done with some filed which was not used for _time calculation. In general I agree that such modifications to the _time field should be avoided.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

It's not about the events themselves 🙂 _time is _time.

As I understand the "problem" - there is a timestamp - possibly parsed out from the event - which as we know is internally stored as number of seconds since epoch and is completely "timezoneless". And OP wants to render it in different timezone than user's configured timezone. Splunk as such doesn't allow it. The strftime converts given timestamp to a string but the timezone is not-configurable. It's always the one configured for the user "globally". And there is no way around it.

What I meant and what you showed is rendering a completely different value of timestamp to make the string representation appear "correct". It "works" meaning that it should produce expected string values but is prone to cause much confusion and errors later when everyone forgets how it's done, when the daylight saving kicks and so on.

Unfortunately, time calculations can be annoying and timezone differences can be confusing.

I suppose I'd advise @Veeru to keep the time rendered in user's timezone but maybe give for reference some predefined timestamps in other timezones rendered in user's timezone.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Short of doing very nasty tricks and pretending that the time is in fact a completely different timestamp than it really is you can't display a timestamp in a different timezone than the one your user is configured with.

As simple as that.

I know it's sometimes annoying if you want to be able to - for example - switch from one timezone to another to check what the situation looks like for - for example - another team in your multinational corpo. But time manipulation is sufficiently complicated as it is and _not_ being able to render timestamp in arbitrary timezones (possibly omitting the timezone info from resulting string) saves everyone a lot of headaches.

Veeru
Path Finder

Hello All,

I want to convert the UTC time to 3 different time zones as i mentioned in previous message
can  anyone please help me out on this.
 Basically when time zone consists of emea in it should be convert to CST time
2022-06-10 15:39:00 emea ->  2022-06-10  10:39:00 CST and viceversa

Thank you in advance
veeru

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You should be able to use the rex command to convert the regions into time zones then use strptime to parse the timestamps.  Once parsed, they'll be in UTC.  Splunk will convert them to the user's selected time zone.

| rex mode=sed field=timestamp "s/emea/CEST/"
| rex mode=sed field=timestamp "s/apac/HKT/"
| rex mode=sed field=timestamp "s/us/EDT/"
| eval ts= strptime(timestamp, "%Y-%m-%d %H:%M:%S %Z")
---
If this reply helps you, Karma would be appreciated.

Veeru
Path Finder

@richgalloway  This not giving us the timestamp field even

My issue is when emea comes it should show us the cst time and as well when apac comes it should show us the hongkong time.

 

Thank you for replying
veera

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

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