Sorry if this was a question asked before but i couldn't seem to find it. I am trying to do a group by on _time so that i can aggregate rows with the same time together. However I was wondering if its possible to do a group by on a date range like.. between 1 second or something.
My current search is | stats count(_time) by _time
eg.)
what i have
_time | count(_time) | col
2017-02-28T05:42:45.000-05:00 | 1 | A
2017-02-28T05:42:44.000-05:00 | 1 | A
2017-02-28T08:11:07.000-05:00 | 2 | B
what I want
_time | count(_time) | col
2017-02-28T05:42:45.000-05:00 (grouped up in someway) | 2 | A
2017-02-28T08:11:07.000-05:00 | 2 | B
since 2017-02-28T05:42:45.000-05:00 and 2017-02-28T05:42:44.000-05:00 are one second apart, i was hoping if its possible to
group them together.
Thanks in advance.
If you've events like below, the 2nd event can be merged with both first and second, so where should it go Or you want all three events to be merged together?
_time | count(_time) | col
2017-02-28T05:42:45.000-05:00 | 1 | A
2017-02-28T05:42:44.000-05:00 | 1 | A
2017-02-28T05:42:43.000-05:00 | 1 | A
Is col your extracted field? Are you looking for group by time every second and not looking for group by col?
<Your Base Search>| stats count as Count by col
Based on what you have asked you can use bin with stats or timechart, either one with span=1s (provided you have multiple rows per second and data flowing in every second. If data flow is not every second you should have a longer span.
| bin _time span=1s
| stats count by _time
Or
| timechart span=1s count by _time
Pretty close. Since dcheng123 wants the col value, we need to include it in the stats.
| bin _time span=1s
| stats count by _time col
Also, timechart chokes on "by _time", since it already assumes it is going to present the data by _time.
| bin _time span=1s
| timechart span=1s count by col
@DalJeanis yes... I was not sure of col field being available since it was not present in the query in question and also question did not mention about stats by col? Lets hope that @dcheng123 issue is resolved 🙂