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.
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
@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.
I update the description with more information.