Splunk Search
Highlighted

How to limit streamstats sum to a range of values?

Explorer

We are running a CUSUM function where we do not want the value to run away either too high or too low (negative). Ideally we would like the bottom end of this one sided CUSUM to be 0 and the top side to be the value of the decision limit. I have tried using the technique where you accum a variable, but I can only get that to work on the low side.

Tags (3)
0 Karma
Highlighted

Re: How to limit streamstats sum to a range of values?

Splunk Employee
Splunk Employee

Maybe you could use eval to make a second field that is the filter of your first. I'm also not sure exactly what you mean by decision limit.

| eval my_new_field = if(field > 0 OR field < decision_limit, field, "")

So the if function returns an empty string if its not within the range we specify. Then you can just use streamstats on the new field.

0 Karma
Highlighted

Re: How to limit streamstats sum to a range of values?

Explorer

I tried that, but the variable used in the streamstats is not one you can alter.

0 Karma
Highlighted

Re: How to limit streamstats sum to a range of values?

Splunk Employee
Splunk Employee

Use comments (not answers) to reply.

What do you mean? The field that you're normally using with streamstats can definitely be altered

0 Karma
Highlighted

Re: How to limit streamstats sum to a range of values?

Explorer

Here is what I did:
| eval ZBAR = blah blah blah | streamstats sum(ZBAR) as CUSUM |
eval CUSUM = if(CUSUM < 0, 0, if(CUSUM > DECLIMIT, DECLIMIT, CUSUM))

CUSUM never got reset, in most cases it get getting more negative or positive.

0 Karma
Highlighted

Re: How to limit streamstats sum to a range of values?

Splunk Employee
Splunk Employee

Can you try putting the eval CUSUM BEFORE and then putting it in this order:

   | eval ZBAR = blah blah
   | eval CUSUM =  if(CUSUM < 0, 0, if(CUSUM > DECLIMIT, DECLIMIT, CUSUM))
   |  streamstats sum(CUSUM) as CUSUM

?

0 Karma
Highlighted

Re: How to limit streamstats sum to a range of values?

Explorer

Thanks. I tried:

search blah | eval CUSUM = max(0.000000, CUSUM)| eval CUSUM = min(H, CUSUM) | eval ZBAR = AVGFREQ - (CONTROLMEAN + K ) | streamstats sum(ZBAR) as CUSUM | table blah blah blah

I also tried:
search blah | eval CUSUM = if(CUSUM < 0, 0, if(CUSUM > H, H, CUSUM)) | eval ZBAR = AVGFREQ - (CONTROLMEAN + K ) | streamstats sum(ZBAR) as CUSUM | table blah

It appears as though there are 2 variables with the name CUSUM. One is scoped to the streamstats level and the other is scoped to the event level. Just my guess.

0 Karma
Highlighted

Re: How to limit streamstats sum to a range of values?

Splunk Employee
Splunk Employee

No its because you're overwriting them in that example. They don't have scope backwards - only forwards.

So if you do

search blah
| eval CUSUM
| eval ZBAR
| streamstats sum(ZBAR) as CUSUM_SUM

then you won't write over the earlier CUSUM. In the examples you gave, you first eval CUSUM but lose it completely when you make sum(ZBAR) as CUSUM. You can verify this yourself by doing table after each part of the command to see what is in the field (we don't call them variables) CUSUM

0 Karma
Highlighted

Re: How to limit streamstats sum to a range of values?

Explorer

This is what was suggested to me in the original answer. The problem is this way, CUSUM_SUM will go way outside the range of 0 to H. I'm starting to feel that there is no solution to this problem.

0 Karma
Highlighted

Re: How to limit streamstats sum to a range of values?

Splunk Employee
Splunk Employee

I'm not sure what you're trying to do:

For example,

search blah | eval CUSUM = if(CUSUM < 0, 0, if(CUSUM > H, H, CUSUM)) | eval ZBAR = AVGFREQ - (CONTROLMEAN + K ) | streamstats sum(ZBAR) as CUSUM | table blah

Is totally different from what you were doing earlier - like you don't even use the eval CUSUM.

Did you try something like this? It should be pretty simple...

base search
| eval DECLIMIT = 100
| eval ZBAR = AVGFREQ - (CONTROLMEAN + K ) 
| eval ZBAR_FILTER = case(ZBAR < 0, 0,  ZBAR > DECLIMIT, DECLIMIT, 1==1, ZBAR)
| streamstats sum(ZBAR_FILTER) as CUSUM

The point I was trying to make is that you need to add the filter log to the field that you are putting into the sum() function BEFORE you actually sum up the field values.

0 Karma