Getting Data In

Splunk date time format

singhnitin
New Member

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

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

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")

View solution in original post

0 Karma

somesoni2
Revered Legend

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")
0 Karma

inventsekar
SplunkTrust
SplunkTrust
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.
thanks and best regards,
Sekar

PS - If this or any post helped you in any way, pls consider upvoting, thanks for reading !
0 Karma

sundareshr
Legend

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")
0 Karma

inventsekar
SplunkTrust
SplunkTrust

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

thanks and best regards,
Sekar

PS - If this or any post helped you in any way, pls consider upvoting, thanks for reading !
0 Karma

sundareshr
Legend

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")
0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

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

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...