Splunk Search

How to group events by time after using timechart span?

Communicator

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
0 Karma
1 Solution

Builder

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.

View solution in original post

0 Karma

Builder

Try adding this to the end of your search:

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

Builder

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.

View solution in original post

0 Karma

Communicator

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

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Communicator

@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.

0 Karma

Builder

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

0 Karma

Communicator

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

0 Karma