Refine your search:

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.

asked 26 Apr '13, 13:02

pjaguilarjr's gravatar image

pjaguilarjr
403
accept rate: 0%


2 Answers:

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

link

answered 29 Apr '13, 12:55

_d_'s gravatar image

_d_
3.7k37
accept rate: 36%

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

(29 Apr '13, 12:58) _d_

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

(29 Apr '13, 21:07) 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.)

(18 Jun '13, 06:13) pjaguilarjr

What does your lookup table look like? Including headers.

link

answered 28 Apr '13, 18:08

_d_'s gravatar image

_d_
3.7k37
accept rate: 36%

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

(28 Apr '13, 19:39) 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,[...]

(29 Apr '13, 12:14) pjaguilarjr
Post your answer
toggle preview

Follow this question

Log In to enable email subscriptions

RSS:

Answers

Answers + Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×430

Asked: 26 Apr '13, 13:02

Seen: 545 times

Last updated: 18 Jun '13, 06:13

Copyright © 2005-2014 Splunk Inc. All rights reserved.