topic Re: How to group data by time from csv? in Splunk Search

How to group data by time from csv?

harshal_chakran — Fri, 25 Jul 2014 05:55:09 GMT

Hi,

I have a csv with two columns, where 1st column is of datetime format :
"%d-%b-%Y %H:%M:%S" i.e. 01-Jan-2014 00:15:00 & second one showing a RESULT (numerical value)

Is it possible to group my RESULT column with respect to one hour and 6 hours and displaying it in a table?
So that in first case I can show only one hour of data and in second case only 6 hours of data.

Kindly Help...!!!

Re: How to group data by time from csv?

strive — Fri, 25 Jul 2014 06:54:06 GMT

Assuming your CSV column names as Time and Result

Try this

|inputlookup test.csv | eval _time=strptime(Time,"%d-%b-%Y %H:%M:%S") | bucket _time span=6h | stats sum(Result) as Total by _time

The above example is for 6 hour buckets. You can change span for 1 hour buckets.

Re: How to group data by time from csv?

harshal_chakran — Fri, 25 Jul 2014 09:23:17 GMT

thanks for the answer strive.

Is it possible to show it as :-

timestamp RESULT
01-Jan-2014 00:15:00 1234
01-Jan-2014 00:15:00 3456
01-Jan-2014 00:15:00 6578

what I am getting now is:

timestamp RESULT
01-Jan-2014 00:15:00 1234
3456
6578

Re: How to group data by time from csv?

tom_frotscher — Fri, 25 Jul 2014 12:28:23 GMT

You can use the mvexpand command in this case.
yoursearch | mvexpand RESULTS

But if you used the search mentiond by @strive, you should already have you desired result. Maybe you can post the exact search you used again.

Re: How to group data by time from csv?

strive — Fri, 25 Jul 2014 14:26:14 GMT

With the search that i have given you wont get multiple values for a single timestamp. Give sample input and also expected output. We can modify search as per your requirements

Re: How to group data by time from csv?

harshal_chakran — Mon, 04 Aug 2014 11:07:21 GMT

actualy I dont want the sum, only the table with all the values relative to that timestamp

Re: How to group data by time from csv?

somesoni2 — Mon, 04 Aug 2014 12:39:41 GMT

Just replace "|stats sum(Result) as Total by _time" with " | table _time Result".