Given the following:
index=myindex source=mysource MYSEARCHTERM | stats count by _time MyField
Which gives the results:
_time Bob Sally Fred
2018-11-14 07:00:00.000 1 2 3
2018-11-14 08:00:00.000 0 1 2
How can I only show those users that had a count > 1 in any given _time period? So the result should be:
_time Sally Fred
2018-11-14 07:00:00.000 2 3
2018-11-14 08:00:00.000 1 2
i.e. Sally is still shown as she had a count > 1 in a previous time period, but Bob is no longer shown as none of his entries are > 1
Firstly index=myindex source=mysource MYSEARCHTERM | stats count by _time MyField
cannot result in the output you show. That search would result in 3 columns: _time, MyField and count. The output you are showing looks more like the output of | chart count over _time by MyField
One way to get the results you are after:
index=myindex source=mysource MYSEARCHTERM
| stats count by _time MyField
| where count>1
| chart sum(count) over _time by MyField
The Sudo query should actually be more like:
index=myindex source=mysource MYSEARCHTERM | bucket _time span=15m | chart count over _time by MyField limit=0
...but it was the dropping of columns answer I am after...
Yes, then my answer should work for you (just add that bucket part): https://answers.splunk.com/answering/699948/view.html
Firstly index=myindex source=mysource MYSEARCHTERM | stats count by _time MyField
cannot result in the output you show. That search would result in 3 columns: _time, MyField and count. The output you are showing looks more like the output of | chart count over _time by MyField
One way to get the results you are after:
index=myindex source=mysource MYSEARCHTERM
| stats count by _time MyField
| where count>1
| chart sum(count) over _time by MyField
Updated the question to correct the sudo query.
I'd almost got it in one of my many attempts, but I missed the sum(count) at line 4!!
Yes, so I guessed that correctly, have your tried my suggestion by doing stats first, then filter and then chart?
It works... 😉
I think you can use streamstats (delta) to solve this - (approx SPL)-
index=myindex source=mysource MYSEARCHTERM | streamstats current=f last(MyField_for_BobSallyFred_time1) as Count_old last(MyField_for_BobSallyFred_time2 as Count_new | eval delta=Count_old - Count_new | where delta > 1
from - https://answers.splunk.com/answers/296041/how-do-i-find-the-delta-with-the-previous-count-va.html
What's the point in using streamstats to calculate a delta? He just wants to filter his results for count>1.
What is MyField_for_BobSallyFred_time1 and MyField_for_BobSallyFred_time2?
Will this drop the column (MyField value) if the delta fails the condition?