lookup table definition for column in month-year format

[pjaguilarjr]

I've uploaded a few .csv files as lookup tables that have a month-date timestamp column, but I'm not able to get splunk to read that column as a date. I created a lookup definition specifying the time column and input the "%m-%Y" format (ex: 10-2013), but no dice. I had the data in %b-%Y format (ex: Oct-2013) originally, and I've also tried late binding using

| inputlookup building_elec_consumption.csv | eval time=strptime(Month, "%m-%Y") | fields time

but that creates a blank column.

[_d_ · Accepted solution]

IIRC strptime() has a day granularity, in that it needs at least the day to work properly. One workaround is to append the first day of the month at runtime and key "time" off of that:

| inputlookup month.csv | eval modMonth=Month."-1" | eval time=strptime(modMonth, "%Y-%m-%d")

[pjaguilarjr]

I did have to add the day in. We ended up making it work by tricking splunk like so:

|eval _time=strptime(Month,"%Y-%m-%d")

and from there:

| eval Month=strftime(_time,"%m")

However it's probably best left in epoch, since without an index, splunk can't use the default time functions(time range picker, earliest=, etc.)

[pjaguilarjr]

Does it require time as well or am I doing something wrong?

|inputlookup data.csv |eval time=strptime(Month, "%Y-%m-%d")| table time, Month

yields

time                    Month

1 1254369600.000000 2009-10-01
2 1257048000.000000 2009-11-01
3 1259643600.000000 2009-12-01
4 1262322000.000000 2010-01-01
5 1265000400.000000 2010-02-01

[_d_]

You may also want to chase it with "| fields - modMonth"

[_d_]

What does your lookup table look like? Including headers.

[pjaguilarjr]

Below are the first few columns. I switched the Month column to %Y-%m so it would sort properly, but I'm still not able to extract anything.

FY,Month,Building_1,Building_2,Building_6, [...]
10,2009-10,1518,240,197,[...]
10,2009-11,1207,146,134,[...]
10,2009-12,1386,163,146,[...]

[pjaguilarjr]

It's a comma delimited .csv made in microsoft excel. The headers are just the first row, capitalized, with underscores instead of spaces.