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

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...