topic Re: YYYYMM timestamp - can Splunk extract time using strptime? in Getting Data In

YYYYMM timestamp - can Splunk extract time using strptime?

himynamesdave — Fri, 15 Jan 2016 21:07:02 GMT

My data format can be seen below (CSV). The date field ("PERIOD") is in %Y%m format.

...,PERIOD
...,201512

Although the following props.conf does not work:

[ csv ]
CHARSET=UTF-8
INDEXED_EXTRACTIONS=csv
KV_MODE=none
SHOULD_LINEMERGE=false
category=Structured
TIME_FORMAT=%Y%m
TIMESTAMP_FIELDS=PERIOD

Any ideas what I'm doing wrong guys?

Re: YYYYMM timestamp - can Splunk extract time using strptime?

somesoni2 — Fri, 15 Jan 2016 22:15:47 GMT

The minimum time format should include the day and month (year can be optional). If you've control over how you log your data, add the day part as well (you can default it to 01 for first day of month). Once you've that, above setting (with updated TIME_FORMAT) should work fine.

Re: YYYYMM timestamp - can Splunk extract time using strptime?

himynamesdave — Sat, 16 Jan 2016 10:39:31 GMT

Got it, thanks!

Re: YYYYMM timestamp - can Splunk extract time using strptime?

jkat54 — Sat, 16 Jan 2016 12:36:19 GMT

Try adding this sedcmd before the time_format:

sedcmd-AddDayToDate = "s/(20\d{4})/\101/g"

What this will do is match 20 followed by 4 digits (ex 201601, 209912, etc.), and when it finds this match it will replace it with whatever the original 6 digits were, plus it will add 01 for the first day of the month.

So in the end, the field should have a correct date value. You may wish to change it to \130 to always have it show on the 30th day instead, etc.

The uncertainty that comes for me is that I'm not sure if sedcmd will work prior to time_prefix or not. So please give this a try and let me know.

Re: YYYYMM timestamp - can Splunk extract time using strptime?

himynamesdave — Sun, 17 Jan 2016 08:59:12 GMT

Thanks! However, it would appear timestamp processing happens before any sedcmd's thus ruling this approach out.

Re: YYYYMM timestamp - can Splunk extract time using strptime?

jkat54 — Sun, 17 Jan 2016 12:47:42 GMT

Yeah sorry i was on my mobile so I didnt have a chance to look it up.

http://docs.splunk.com/Documentation/Splunk/6.2.0/Deploy/Datapipeline

So given that SEDCMD cant be done prior to TIME_FORMAT, you have a few options that I see.

Rewrite the code that generates the source as was suggested
Use a rex in your search ...| rex field=PERIOD "s/(20\d{4})/\101/g" | eval _time=strptime(PERIOD,"%Y%m%d") | ...
Use a rex in your search and take it to a summary index, then run searches on the summary index.
More options exist but these are some of the better ones I can think of.

Re: YYYYMM timestamp - can Splunk extract time using strptime?

jkat54 — Sun, 17 Jan 2016 12:51:15 GMT

You may have to urlencode that rex because the '{}' characters.

here it is encoded ... | rex field=PERIOD "s/(20\d%7B4%7D)/\101/g"

Re: YYYYMM timestamp - can Splunk extract time using strptime?

fdi01 — Thu, 21 Jan 2016 08:59:04 GMT

try with TIME_FORMAT = %Y-%m-%dT%H:%M:%S.%N instead TIME_FORMAT=%Y%m in your props.conf