topic Re: Is it possible to create a timechart from a time field in a CSV or lookup? in Splunk Search

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

pkeller — Wed, 11 Nov 2015 22:23:38 GMT

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 😉

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

aljohnson_splun — Wed, 11 Nov 2015 22:40:31 GMT

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

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

stephanefotso — Wed, 11 Nov 2015 22:43:08 GMT

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

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

MuS — Wed, 11 Nov 2015 22:45:52 GMT

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

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

MuS — Wed, 11 Nov 2015 22:46:31 GMT

do slow today 🙂

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

pkeller — Wed, 11 Nov 2015 22:56:20 GMT

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

Thank you

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

sssignals — Sat, 09 Sep 2017 16:12:20 GMT

I had a similar problem.