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

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...