Splunk date time format

[singhnitin]

In splunk, I have a file which has date in the format June 16th,2014 and I am trying to extract out the month_year variable in the format 2014-06.

Any help will be appreciated.

TIA

[somesoni2 · Accepted solution]

Try like this (run anywhere sample)

| gentimes start=-1 | eval date="June 16th,2014" | table date| eval date_month=strftime(strptime(replace(date,"(\w+)([^,]+),(\d+)","1 \1 \3"),"%d %B %Y"),"%Y-%m")

[inventsekar]

rex field=_raw "(?<month>\w+)\s\d+\w\w,(?<year>\d\d\d\d)" | eval MON=case(month == "Nov", "11", month == "July", "7", month == "June", "6", month == "Aug", "8") |  eval date=year."-".MON | table date MON, year _raw

it gives this output -

date    MON year    _raw
2014-11 11  2014    the format Nov 10th,2014 and extract out the month_year  in the format 2014-06.
2014-8  8   2014    the format Aug 6th,2014 and extract out the month_year  in the format 2014-06.
2014-7  7   2014    the format July 1st,2014 and extract out the month_year  in the format 2014-06.
2014-6  6   2014    the format June 16th,2014 and extract out the month_year  in the format 2014-06.

[sundareshr]

Try this

UPDATED TO SHOW MONTH*

| rex field=x mode=sed "s/(?<dt>\w{3,4}\s\d\d?)([snrt][hd]),\s?(?<yr>\d{4})/\1, \3/g" | eval y=strptime(x,"%B %-d, %Y") | eval date=strftime(y, "%Y-%m")

[inventsekar]

Sir, i am trying this one.. but its not working. also may i know, what this one does - ([snrt][hd]) please

sourcetype=monthyear | rex field=_raw mode=sed "s/(?

\w{3,4}\s\d\d?)([snrt][hd]),\s?(?\d{4})/\1, \3/g" | table dt yr _raw

[sundareshr]

Since you date can have st, nd, rd, th after the date, ([snrt][hd]) in the rex command is to remove those chars to it can be formatted into a epoch time.

The rex command assume you have the date extracted into a field called x. if you don't have the date extracted, remove the field=x and try it. Like this

| rex mode=sed "s/(?<dt>\w{3,4}\s\d\d?)([snrt][hd]),\s?(?<yr>\d{4})/\1, \3/g" | eval y=strptime(x,"%B %-d, %Y") | eval date=strftime(y, "%Y-%m")