Splunk Search

How to group events by time after using timechart span?

russell120
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

jpolvino
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

mydog8it
Builder

Try adding this to the end of your search:

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

jpolvino
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

russell120
Communicator

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

richgalloway
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

russell120
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

jpolvino
Builder

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

0 Karma

russell120
Communicator

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

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.