Splunk Search

lookup table definition for column in month-year format

New Member

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 buildingelecconsumption.csv | eval time=strptime(Month, "%m-%Y") | fields time

but that creates a blank column.

Tags (1)
0 Karma
1 Solution

Splunk Employee
Splunk Employee

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

View solution in original post

Splunk Employee
Splunk Employee

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

View solution in original post

New Member

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

0 Karma

New Member

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

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

Splunk Employee
Splunk Employee

What does your lookup table look like? Including headers.

0 Karma

New Member

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,Building1,Building2,Building_6, [...]
10,2009-10,1518,240,197,[...]
10,2009-11,1207,146,134,[...]
10,2009-12,1386,163,146,[...]

0 Karma

New Member

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

0 Karma