Is it possible to create a timechart from a time field in a CSV or lookup?

[pkeller]

host,value,timestamp
a1,30,24-Oct-15 00:00
a1,10,24-Oct-15 01:00
a1,5,24-Oct-15 02:00
a2,3,24-Oct-15 00:00
a2,5,24-Oct-15 01:00

I'm wondering if it's possible using either inputcsv or inputlookup (if the csv is a lookup table) to do something like:

|inputcsv mycsv | search host=a1 | timechart span=1h avg(value) by host

(obviously, that doesn't work ... and I'm thinking that the only way to do this is to index the CSV with the TIME_FORMAT defined based on the 'timestamp' field.)

I probably shouldn't even be asking this to begin with 😉

[sssignals]

I had a similar problem.

[MuS · Accepted solution]

Hi pkeller,

yes, you can use eval strptime() on the timestamp field to parse the value as timestamp, see docs http://docs.splunk.com/Documentation/Splunk/6.3.0/SearchReference/Timechart for more details. So try this:

 |inputcsv mycsv | search host=a1 | eval _time=strptime(timestamp, "%d-%b-%y %H:%M")  | timechart span=1h avg(value) by host

Hope this helps ...

cheers, MuS

[stephanefotso]

True. Since the timechart command uses the _time field in your event data, that search query will not work, unless you have an _time field in your csv file.

Thanks

[aljohnson_splun]

oh yeah. definitely.

You'll use inputlookup to get csv files loaded. Here is a doc on doing that. However, you'll need to turn the timestamp into a timestamp with eval:

| eval _time = strptime(timestamp, "%d-%b-%y")

Then, you can go ahead and just do:

| timechart span=1h max(value) as max_value by host

So the total search:

| inputlookup data.csv
| eval _time = strptime(timestamp, "%d-%b-%y")
| timechart span=1h max(value) as max_value by host

[MuS]

do slow today 🙂

[pkeller]

You all are awesome.
The creation of _time via strptime worked perfectly

Thank you