Splunk Search

lookup table definition for column in month-year format

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

but that creates a blank column.

Tags (1)
0 Karma
1 Solution

_d_
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

_d_
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

pjaguilarjr
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

pjaguilarjr
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

_d_
Splunk Employee
Splunk Employee

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

0 Karma

_d_
Splunk Employee
Splunk Employee

What does your lookup table look like? Including headers.

0 Karma

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

0 Karma

pjaguilarjr
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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!