Splunk Search

Count when sum reaches specific number then start over.

AlexMcDuffMille
Communicator

Hello,

I have data that shows the number of items I'm counting by item number. Is there a way to count when I have a certain amount of items, then reset the count back to zero? I could do something like | eval number=sum(field)/value, but then I wouldn't get the timestamp of when that count rolled over.

For example, I want to know when I have at least 5 items. So for this data I would want it to show a graph with values on day 2, day 3, and day 5, since that's when the count got high enough to be 5.

day1   2
day2   3
day3   6
day4   1
day5   5
1 Solution

aholzer
Motivator

Try the following:

<base_search> | stats count by day | accum count as accum_cnt | eval accum_cnt = if(accum_cnt>=5,0,accum_cnt)

This will aggregate the counts by the day. Then it will start running the cumulative for your count by day. When it reaches a value of 5 or greater it will change it to 0. Look for days with a value of 0.

If you want to save the value that it hit which equates to above 5 you can try something like this:

<base_search> | stats count by day | accum count as accum_cnt | eval accum_cnt_over = if(accum_cnt>=5,accum_cnt,0) | eval accum_cnt = if(accum_cnt>=5,0,accum_cnt) | where accum_cnt_over>0

Hope this helps

View solution in original post

mkinsley_splunk
Splunk Employee
Splunk Employee

What you want is the modulo operator. It is essentially the remainder after a division operation. As the dividend increases, the remainder increases, until a number is reached that divides perfectly and thus the remainder resets to zero over and over again. Exactly what you are looking for.

Here is a sample query:

index=_internal  | stats count as cat_val by date_hour | accum cat_val as subtotal  | eval i = subtotal % 6

Notice that i cycles between 0 and 5 and then continues to cycle as you want.

mkinsley_splunk
Splunk Employee
Splunk Employee

sure, can you give an example ?

0 Karma

AlexMcDuffMille
Communicator

I think this is what I am looking for. Is there a way to take this to the next level and keep a running total of events based on what the value is in another column?

0 Karma

aholzer
Motivator

Try the following:

<base_search> | stats count by day | accum count as accum_cnt | eval accum_cnt = if(accum_cnt>=5,0,accum_cnt)

This will aggregate the counts by the day. Then it will start running the cumulative for your count by day. When it reaches a value of 5 or greater it will change it to 0. Look for days with a value of 0.

If you want to save the value that it hit which equates to above 5 you can try something like this:

<base_search> | stats count by day | accum count as accum_cnt | eval accum_cnt_over = if(accum_cnt>=5,accum_cnt,0) | eval accum_cnt = if(accum_cnt>=5,0,accum_cnt) | where accum_cnt_over>0

Hope this helps

somesoni2
SplunkTrust
SplunkTrust

Can you provide sample data?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...