Splunk Search
Highlighted

How to transform a string (i.e. 11-MAY-2017) to a date field?

Explorer

I have two fields START and END that are tagged as strings. The two fields always carry a value in the format dd-[3-letter MONTH-yyyy. As an example:

START=07-SEP-2017
END=11-NOV-2045

I have gone through and applied solutions provided in previous posts to no avail. I have tried using regex or eval and strptime commands unsuccessfully while attempting to convert the date format 14-JUN-2017 to a date field. my most recent update was to use Index=* sourcetype=* | eval -START=strptime(START, %d-%b-%Y). Has anyone come across this 'specific' format and issue before?

0 Karma
Highlighted

Re: How to transform a string (i.e. 11-MAY-2017) to a date field?

Explorer

Just to add to this....I want the value to be numeric so I can sort it.

0 Karma
Highlighted

Re: How to transform a string (i.e. 11-MAY-2017) to a date field?

SplunkTrust
SplunkTrust

There may be a syntax issue with the way you used strptime, but can't say for sure as you didn't format the query portion using code formatter (101010 button on top of the editor OR Ctrl+K after selecting text). Try like this

index=yourIndex sourcetype=yourSourcetype | eval START=strptime(START,"%d-%b-%Y")  | eval END=strptime(END,"%d-%b-%Y") 
0 Karma
Highlighted

Re: How to transform a string (i.e. 11-MAY-2017) to a date field?

Explorer

I think I figured it out. This is the syntax I used:

index=NAME sourcetype=NAME | eval STARTTIME=strftime(strptime(START, "%d-%b-%Y"), "%m/%d/%y") | | eval ENDTIME=strftime(strptime(END, "%d-%b-%Y"), "%m/%d/%y")

View solution in original post

0 Karma
Highlighted

Re: How to transform a string (i.e. 11-MAY-2017) to a date field?

Legend

Hi AJNZAZ,
could you detail your problem?
I checked strptime with your format and it runs, you can use these fields in epochtime for calculations (e.g. difference):

index=_internal 
| head 1 
| eval START="07-SEP-2017", END="11-NOV-2045", START=strptime(START,"%d-%b-%Y"), END=strptime(END,"%d-%b-%Y"), END=strptime(END,"%d-%b-%Y"), DIFF=tostring(END-START,"duration") 
| table START END DIFF

Result is

START   END DIFF
1504735200.000000   2393967600.000000   10292+01:00:00.000000

Bye.
Giuseppe

0 Karma