How can I get access to previous calculate value?

[rparadinha]

I have logs from a SIP proxy server and I'm trying to calculate a threshold based on response status every minute.
I have the following query that returns a table:

    index=TEST sourcetype="TEST"  "=> M="
    | rex field=message "(?P<StatusCode>\d{3}) => M"

    | eval StatusError=if(match(StatusCode,"^(408)|(5\d{2})$"), 1, 0)
    | eval LocallyGenerated=if(searchmatch("(locally generated)"), 1, 0)

    | eval StatusLocallyError=if(LocallyGenerated == 1 AND StatusError == 1, 1, 0)
    | eval StatusCSError=if(LocallyGenerated == 0 AND StatusError == 1, 1, 0)
    | eval StatusLocallyNotError=if(LocallyGenerated == 1 AND StatusError == 0, 1, 0)
    | eval StatusGood=if(LocallyGenerated == 0 AND StatusError == 0, 1, 0)

    | reverse

   | timechart  sum(StatusGood) AS "Status Good",
             sum(eval(StatusLocallyError+StatusCSError)) AS "Status Error",
             sum(eval((StatusLocallyError+StatusCSError)-StatusGood)) AS "Diff",
                 span=1m

Table

Basically I want to calculate the Threshold column based on following formula:

if (isnull(previous_Threshold){
     previous_Threshold=0
}
if (DIFF > 0 ){
        Threshold = previous_Threshold + DIFF
} 
else {
        if ( previous_Threshold > 0 ) {
                Threshold = previous_Threshold + DIFF
                if ( Threshold < 0 )
                        Threshold = 0
        }
        else {
               Threshold = 0
        }
}

How can I get access to previous calculate value?

I tried to do this:

 | streamstats current=false window=1 values(Threshold) as previous_Threshold
| eval previous_Threshold=if(isnull(previous_Threshold),0,previous_Threshold)

| eval Threshold=if(Diff>=0,previous_Threshold+Diff,if(previous_Threshold>0,if(previous_Threshold+Diff>0,previous_Threshold+Diff,0),0))

| fields Good, Error, Diff, Threshold, previous_Threshold

But previous_Threshold is always empty. 😞

To facilitate the test, with the following query, you can simulate the original table.

| makeresults | eval mydata=mvappend("10;0","8;2","6;4","5;5","4;6","1;9","0;10","2;8","4;6","5;5","9;1","10;0","9;1","10;0","12;1","6;8","2;14","7;5","8;6","3;10","1;13","4;23","6;3","8;4","2;12","1;6","12;4","10;2","14;1","13;0","7;0","9;0", "12;0","11;1")
| mvexpand mydata 
| eval mydata=split(mydata,";") 
| eval Good=mvindex(mydata,0) 
| eval Error=mvindex(mydata,1)
| table Good, Error
| eval Diff=(Error-Good)

I also tried to calculate based on the old values of the diff column but so far I have not been able to find a formula that gives me what I want.
Example:

| makeresults | eval mydata=mvappend("10;0","8;2","6;4","5;5","4;6","1;9","0;10","2;8","4;6","5;5","9;1","10;0","9;1","10;0","12;1","6;8","2;14","7;5","8;6","3;10","1;13","4;23","6;3","8;4","2;12","1;6","12;4","10;2","14;1","13;0","7;0","9;0", "12;0","11;1")
| mvexpand mydata 
| eval mydata=split(mydata,";") 
| eval Good=mvindex(mydata,0) 
| eval Error=mvindex(mydata,1)
| table Good, Error
| eval Diff=(Error-Good)

| streamstats current=false window=1 values(Diff) as previous_diff
| eval previous_diff=if(isnull(previous_diff),0,previous_diff)

| eval previous_diff_positive=if(previous_diff<0,0,previous_diff)

| streamstats current=false window=1 values(previous_diff_positive) as previous_sum_diff_positive
| eval previous_sum_diff_positive=if(isnull(previous_sum_diff_positive),0,previous_sum_diff_positive)

| streamstats current=true global=true sum(previous_diff_positive) as previous_sum_diff_positive
| eval previous_sum_diff_positive=if(isnull(previous_sum_diff_positive),0,previous_sum_diff_positive)

| eval previous_diff_negative=if(previous_sum_diff_positive>0 AND previous_diff<0,previous_diff,0)
| streamstats current=true global=true sum(previous_diff_negative) as previous_sum_diff_negative

| eval threshold=if(Diff>=0,previous_sum_diff_positive+Diff,previous_sum_diff_positive+previous_sum_diff_negative+Diff)
| eval threshold=if(threshold<0,0,threshold)

| fields Good, Error, Diff, threshold, previous_diff, previous_diff_positive, previous_sum_diff_positive, previous_diff_negative, previous_sum_diff_negative

I'm running out of ideas.
Your help will be greatly appreciated.

[nrduren1115]

I would suggest using streamstats. This allows you to compute stats using previous events. For example you could use:

  | streamstats window=1 values(Status Good) as previous_status_good, values(Status Error) as previous_status_error

This would set up events that have both the current value and the value of the one previous. Calculation of threshold is straightforward from there.

http://docs.splunk.com/Documentation/Splunk/7.2.0/SearchReference/Streamstats

[rparadinha]

@nrduren1115 Thanks for the help.
It's not that simple. With previous_status_good and previous_status_error I can't calculate the previous Threshold. Previous Threshould value also depends on previous previous Threshould.
I'm trying this query but it isn't work.

     index=TEST sourcetype="TEST"  "=> M="
     | rex field=message "(?P<StatusCode>\d{3}) => M"

     | eval StatusError=if(match(StatusCode,"^(408)|(5\d{2})$"), 1, 0)
     | eval LocallyGenerated=if(searchmatch("(locally generated)"), 1, 0)

     | eval StatusLocallyError=if(LocallyGenerated == 1 AND StatusError == 1, 1, 0)
     | eval StatusCSError=if(LocallyGenerated == 0 AND StatusError == 1, 1, 0)
     | eval StatusLocallyNotError=if(LocallyGenerated == 1 AND StatusError == 0, 1, 0)
     | eval StatusGood=if(LocallyGenerated == 0 AND StatusError == 0, 1, 0)

     | reverse

    | timechart  sum(StatusGood) AS "Status Good",
              sum(eval(StatusLocallyError+StatusCSError)) AS "Status Error",
              sum(eval((StatusLocallyError+StatusCSError)-StatusGood)) AS "Diff",
                  span=1m

    | streamstats current=f window=1 last(Threshold) as prevthreshold 

    | eval prevthreshold=if(isnull(prevthreshold),0,prevthreshold)      
    | eval Threshold=if(Diff>0, prevthreshold+Diff ,if(prevthreshold>0, if((prevthreshold+Diff)<0,0,prevthreshold+Diff), 0))

The streamstats is not getting thew last Threshold value.

[rparadinha]

I update the description with more information.