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
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.
Try adding this to the end of your search:
| eval hour=strftime(_time,"%I:%M%p")
| fields - _time | table hour *
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.
Ah, this does exactly what I needed. I didn't even think to use |stats sum()
by the hour. Much appreciated.
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".
@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.
Looks like "New Count" is the sum of "Number of Items" for that hour, spanning multiple days.
@jpolvino You are exactly right. Please give me a moment while I read the solutions.