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
Path Finder

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

Tags (1)
0 Karma
1 Solution

FrankVl
Ultra Champion

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

View solution in original post

0 Karma

GadgetGeek
Path Finder

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

0 Karma

FrankVl
Ultra Champion

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

0 Karma

FrankVl
Ultra Champion

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
0 Karma

GadgetGeek
Path Finder

Updated the question to correct the sudo query.

0 Karma

GadgetGeek
Path Finder

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

0 Karma

FrankVl
Ultra Champion

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

0 Karma

GadgetGeek
Path Finder

It works... :winking_face:

inventsekar
SplunkTrust
SplunkTrust

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

thanks and best regards,
Sekar

PS - If this or any post helped you in any way, pls consider upvoting, thanks for reading !
0 Karma

FrankVl
Ultra Champion

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

0 Karma

GadgetGeek
Path Finder

What is MyField_for_BobSallyFred_time1 and MyField_for_BobSallyFred_time2?

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...