In the following Splunk search, how can I only show those users that had a count > 1 in any given _time period?

[GadgetGeek]

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

[GadgetGeek]

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

[FrankVl]

Yes, then my answer should work for you (just add that bucket part): https://answers.splunk.com/answering/699948/view.html

[FrankVl · Accepted solution]

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

[GadgetGeek]

Updated the question to correct the sudo query.

[GadgetGeek]

I'd almost got it in one of my many attempts, but I missed the sum(count) at line 4!!

[FrankVl]

Yes, so I guessed that correctly, have your tried my suggestion by doing stats first, then filter and then chart?

[GadgetGeek]

It works... 😉

[inventsekar]

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

[FrankVl]

What's the point in using streamstats to calculate a delta? He just wants to filter his results for count>1.

[GadgetGeek]

What is MyField_for_BobSallyFred_time1 and MyField_for_BobSallyFred_time2?

Will this drop the column (MyField value) if the delta fails the condition?