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

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

GadgetGeek — Wed, 14 Nov 2018 10:37:33 GMT

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

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

inventsekar — Wed, 14 Nov 2018 12:12:16 GMT

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

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

GadgetGeek — Tue, 29 Sep 2020 22:03:27 GMT

What is MyField_for_BobSallyFred_time1 and MyField_for_BobSallyFred_time2?

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

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

FrankVl — Wed, 14 Nov 2018 13:13:39 GMT

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

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

FrankVl — Wed, 14 Nov 2018 13:15:28 GMT

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

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

GadgetGeek — Wed, 14 Nov 2018 13:15:55 GMT

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

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

FrankVl — Wed, 14 Nov 2018 13:20:34 GMT

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

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

GadgetGeek — Wed, 14 Nov 2018 13:23:45 GMT

Updated the question to correct the sudo query.

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

FrankVl — Wed, 14 Nov 2018 13:26:12 GMT

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

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

GadgetGeek — Wed, 14 Nov 2018 13:26:34 GMT

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

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

GadgetGeek — Wed, 14 Nov 2018 13:47:21 GMT

It works... 😉