All Apps and Add-ons

How to convert UTC to CST in SPL?

Hutch
Path Finder

Hey Splunkers,

 

I am working on a search but I have encountered a road block in my search. I am attempting to change a UTC time zone to CST within the search. I was able to change the EPOCH times to CST but I am struggling to locate any documentation on how I can convert the UTC time to match the same as my CST results. I need to change my time to match the other time zones.

 

 

 

 

2022-08-31T21:04:52Z

 

 

 

needs to be converted to the same format as

 

 

 

08/31/2022 16:21:16

 

 

 

 

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

To convert x_device.last_seen , try this:

| rename x_device.last_seen as last_seen
| eval last_seen = strftime(strptime(last_seen, "%Y-%m-%dT%H:%M:%S%Z"), "%m/%d/%Y %H:%M:%S")

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

matt8679
Path Finder

If you want to change the time in search you can try the following:

Add this below main search

|eval time_format=strftime(_time, "%Y-%m-%d %H:%M:%S")

|eval time_epoch=strptime(time_format, "%Y-%m-%d %H:%M:%S")

|eval time_cst=time_epoch-21600

|eval _time=strftime(time_cst, "%Y-%m-%d %H:%M:%S")

0 Karma

Hutch
Path Finder

@matt8679 

 

This a snippet from my current search

index="x_devices" AND falcon_device.hostname=myhost
| spath
| stats count, min(_time) as firstTime, max(_time) as lastTime, max(_indextime) as recentTime, BY x_device.hostname, x_device.last_seen
| fieldformat firstTime=strftime(firstTime,"%m/%d/%Y %H:%M:%S") 
| fieldformat lastTime=strftime(lastTime,"%m/%d/%Y %H:%M:%S") 
| fieldformat recentTime=strftime(recentTime,"%m/%d/%Y %H:%M:%S") 
| fieldformat lastUpdated=strftime(lastUpdated,"%m/%d/%Y %H:%M:%S")
| fields + x_device.hostname, x_device.last_seen, firstTime, lastTime, recentTime, lastUpdated, timeElapsed, hash, timeElapsed

 

The x_device.last_seen field in the following format

2022-08-24T22:06:01Z

 I can seem to get x_devices.last_seen in an epoch format.

 

I am currently only trying to change that field since all of my other times are already in the correct format. It appears that I may need to make modifications to transforms.conf

0 Karma

richgalloway
SplunkTrust
SplunkTrust

To convert x_device.last_seen , try this:

| rename x_device.last_seen as last_seen
| eval last_seen = strftime(strptime(last_seen, "%Y-%m-%dT%H:%M:%S%Z"), "%m/%d/%Y %H:%M:%S")

 

---
If this reply helps you, Karma would be appreciated.

Hutch
Path Finder

@richgalloway that worked! 

0 Karma

matt8679
Path Finder

| stats count, min(_time) as firstTime, max(_time) as lastTime, max(_indextime) as recentTime, BY x_device.hostname, x_device.last_seen

|eval x_devices.last_seen=tostring(x_devices.last_seen)

|eval time_format=strftime( x_devices.last_seen, "%Y-%m-%d %H:%M:%S")

|eval time_epoch=strptime(time_format, "%Y-%m-%d %H:%M:%S")

|eval time_cst=time_epoch-21600

|eval x_devices.last_seen=strftime(time_cst, "%Y-%m-%d %H:%M:%S")

 

Hutch
Path Finder

@matt8679 

|eval x_devices.last_seen=tostring(x_devices.last_seen)

This is changing the value for "x_devices.last_seen" to NULL in the table. Is it possible that it is unable to convert it to a string? 

0 Karma

matt8679
Path Finder

Try taking that part out and running everything below the eval to convert to string

richgalloway
SplunkTrust
SplunkTrust

The short answer is: you can't.

Splunk will parse a timestamp from any time zone into UTC for internal storage.  When that timestamp is displayed, however, it will always be in the user's chosen time zone.

---
If this reply helps you, Karma would be appreciated.
0 Karma

Hutch
Path Finder

I am only wanting to change the time within this search and not on my indexers. If I understand your response correctly. There is zero way of converting the UTC time listed above into CST within the search. I do want to not I am not trying to change it across my indexers. Only within the search and dashboards.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Notice my answer did not mention indexers.  Splunk does not provide a means for converting time zones in searches and dashboard, because it does so automatically to the user's selected time zone.

---
If this reply helps you, Karma would be appreciated.
0 Karma

Hutch
Path Finder

@richgalloway 

You are telling me that there is no way to convert 

2022-08-24T22:06:01Z

to an epoch format?

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...