Splunk Search

How to do the time conversion for 2017-04-14T13:52:21.000Z to a readable format?

dchalasani
Path Finder

How to do the time conversion for 2017-04-14T13:52:21.000Z to an understandable format? Any one please tell me the Query. Thanks!

Tags (3)
0 Karma
1 Solution

kmorris_splunk
Splunk Employee
Splunk Employee

Try something like this:

[YOUR BASE SEARCH]
| eval newformat=strftime(strptime(yourtimefield,"Current format of date/time field"),"Format you want the date/time in") 
| table yourtimefield newformat

Example with current format and new format:

[YOUR BASE SEARCH]
| eval newformat=strftime(strptime(yourtimefield,"%H:%M:%S.%3q %Z %b %d %Y"),"%m/%d/%Y %p") 
| table yourtimefield newformat

To help determine your time format, see Date and Time Format Variables documentation: http://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/Commontimeformatvariables

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

... | foreach start_date end_table [ eval <<FIELD>> = strptime(<<FIELD>>, "%Y/%m/%dT%H:%M:%S.%3q %Z") | fieldformat <<FIELD>> = strftime(<<FIELD>>, "%m/%d/%Y %H:%M:%S") ] | table start_date end_table
0 Karma

dchalasani
Path Finder

Not working..This are my field names alert_started_at alert_ended_at.

And I am trying to show the alert_started_at alert_ended_at readable format in the table.

Can you please help me with this one?

Thanks

0 Karma

woodcock
Esteemed Legend

You need to be sure that you are specifying your details correctly; did you try changing the field names in the foreach and table commands to match what they really are? Also, if you have a solution, make sure to click Accept on an answer to close your question.

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

Try this:

[YOUR BASE SEARCH]
| eval alert_started_at=strftime(strptime(alert_started_at,"%Y-%m-%dT%H:%M:%S.%3q%Z"),"%m/%d/%Y %p") 
| eval alert_ended_at=strftime(strptime(alert_ended_at,"%Y-%m-%dT%H:%M:%S.%3q%Z"),"%m/%d/%Y %p") 
| table alert_started_at alert_ended_at

This will overwrite the original value of alert_started_at and alert_ended_at, so if you want to maintain that original values, you should change the fieldname before the equals to your new field name. Also, the second date format is just an example, use the documentation link from my earlier answer to format the date the way you want it.

The first format should work for your posted date format in your question.

0 Karma

dchalasani
Path Finder

Old format : 2017-05-12T13:34:31.000Z

New format(after applying) : 04/12/2017 PM

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

What format would you like it to be in?

0 Karma

dchalasani
Path Finder

Like 04/12/2017 T %H:%M:%S PM %Z

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

Change the second format in each eval statement to:

%m/%d/%Y T %H:%M:%S %Z

0 Karma

dchalasani
Path Finder

Worked Thanks!

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

Great! Glad I could help.

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

Try something like this:

[YOUR BASE SEARCH]
| eval newformat=strftime(strptime(yourtimefield,"Current format of date/time field"),"Format you want the date/time in") 
| table yourtimefield newformat

Example with current format and new format:

[YOUR BASE SEARCH]
| eval newformat=strftime(strptime(yourtimefield,"%H:%M:%S.%3q %Z %b %d %Y"),"%m/%d/%Y %p") 
| table yourtimefield newformat

To help determine your time format, see Date and Time Format Variables documentation: http://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/Commontimeformatvariables

0 Karma

dchalasani
Path Finder

What I am trying to do is put the start_date and end_table(Readable format) in the table chat.

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

So, to make sure I understand, you have 2 date/time fields: start_date and end_date, and you want to format them and put them in a table?

0 Karma

dchalasani
Path Finder

Yes Exactly

0 Karma

dchalasani
Path Finder

I am trying to show the Alert start and end date and time.

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

In that case you would use the same method:

[YOUR BASE SEARCH]
| eval start_time=strftime(strptime(startfield,"%H:%M:%S.%3q %Z %b %d %Y"),"%m/%d/%Y %p")
| eval end_time=strftime(strptime(endfield,,"%H:%M:%S.%3q %Z %b %d %Y"),"%m/%d/%Y %p")
| table start_time end_time

Again, replace the formats I am using with your current and desired format. Does this help?

0 Karma

dchalasani
Path Finder

It is not working..Showing empty table with the field name(Start_time)

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

Are you replacing startfield and endfield with your fields, and have you changed the format in the strptime to the current format?

0 Karma

dchalasani
Path Finder

Hi I need some help regarding Sparkline, Trend Indicator

Can you please correct below query..change to Sparkline, Trend Indicator

index=main sourcetype=description | stats count as alert_risk_level |eval alert_risk_level=($alert_risk_level$/1000)|eval alert_risk_level=round(alert_risk_level,2)

Right now I am trying to do this TRENDING OVER TIME for risk identifier means high medium low risk.

Thanks in Advance!

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

Is there a particular format you wanted it in?

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...