Splunk Search

How can I get access to previous calculate value?

rparadinha
Explorer

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

alt text

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. :disappointed_face:

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.

0 Karma

nrduren1115
Explorer

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

0 Karma

rparadinha
Explorer

@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.

0 Karma

rparadinha
Explorer

I update the description with more information.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...