topic Re: How to group events by time after using timechart span? in Splunk Search

How to group events by time after using timechart span?

russell120 — Tue, 31 Dec 2019 15:12:28 GMT

I'm using the following search with timechart span=1h to show how many events appear by the day and hour:

|inputlookup my_lookup.csv
|more lines of query
|timechart span=1h count
|rename count as "Number of Items"

This produces a result like this:

     _time         "Number of Items" 
2019-12-03 15:00            3
2019-12-04 17:00            2
2019-12-05 16:00            2
2019-12-09 17:00            2

What query do I need to show the number of events by just the hour after I've used timechart span=1hr? Below is what I'd like to see:

 hour      "New Count"
03:00PM         2
05:00PM         4
02:00PM         2

Re: How to group events by time after using timechart span?

richgalloway — Tue, 31 Dec 2019 15:42:50 GMT

To reformat the _time field you can use strftime.

...  | timechart span=1h count
| rename count as "Number of Items"
| eval hour=strftime(_time, "%I:%H%p")
| table hour "Number of Items"

Please explain how you arrived at the values for "New Count".

Re: How to group events by time after using timechart span?

jpolvino — Tue, 31 Dec 2019 15:42:59 GMT

This might help:

| makeresults 
| eval data="2019-12-03 15:00:00,3;
2019-12-04 17:00:00,2;
2019-12-05 16:00:00,2;
2019-12-09 17:00:00,2;
2019-12-09 08:00:00,2;
2019-12-08 11:00:00,3;
2019-12-05 11:00:00,1;
2019-12-08 12:00:00,3"
| makemv data delim=";" | mvexpand data | rex field=data "(\s|\n?)(?<data>.*)" | makemv data delim=","
| eval _time=strptime(mvindex(data,0),"%Y-%m-%d %H:%M:%S"),
     numItems=mvindex(data,1)
| fields _time numItems
| eval hourNum=strftime(_time,"%H")
| stats sum(numItems) AS CountByHour by hourNum
| eval hourNum=if(hourNum>12,"0".(hourNum-12).":00PM",hourNum.":00AM")

Just for readability, you should consider overriding your count with a name that isn't reserved, like Volume. Also avoid using spaces in field names, although you can do this at the very end for presentation using the rename command.

You'll want to add lines 14-16 to your search, minding the field name changes.

Re: How to group events by time after using timechart span?

jpolvino — Tue, 31 Dec 2019 15:44:08 GMT

Looks like "New Count" is the sum of "Number of Items" for that hour, spanning multiple days.

Re: How to group events by time after using timechart span?

mydog8it — Tue, 31 Dec 2019 15:45:04 GMT

Try adding this to the end of your search:

 | eval hour=strftime(_time,"%I:%M%p")
 | fields - _time | table hour *

Re: How to group events by time after using timechart span?

russell120 — Tue, 31 Dec 2019 16:50:30 GMT

@jpolvino You are exactly right. Please give me a moment while I read the solutions.

Re: How to group events by time after using timechart span?

russell120 — Tue, 31 Dec 2019 17:03:10 GMT

@richgalloway Unfortunately this produces a field with the exact time (hour & minute) when what I'm looking for is just the hour.

The resulting column should match the hour timestamp span=1hr outputs but in the "%I:%H%p" format.

Re: How to group events by time after using timechart span?

russell120 — Tue, 31 Dec 2019 17:09:23 GMT

Ah, this does exactly what I needed. I didn't even think to use |stats sum() by the hour. Much appreciated.