YYYYMM timestamp - can Splunk extract time using strptime?

[himynamesdave]

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?

[fdi01]

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

[jkat54]

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.

[himynamesdave]

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

[jkat54]

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.

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

[jkat54]

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

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

[somesoni2]

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.

[himynamesdave]

Got it, thanks!