Count when sum reaches specific number then start over.

[AlexMcDuffMille]

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

[mkinsley_splunk]

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]

sure, can you give an example ?

[AlexMcDuffMille]

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?

[aholzer · Accepted solution]

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]

Can you provide sample data?