Splunk Search

## Count when sum reaches specific number then start over.

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
``````
Tags (5)
1 Solution
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

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.

Splunk Employee

sure, can you give an example ?

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?

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

Revered Legend

Can you provide sample data?

Did you miss .conf21 Virtual?

### Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE! Catch Up Now >>

Get Updates on the Splunk Community!