How to find daily maximum and minimum failure rates over the last 7 days?

[demkic]

Hi all, the following search I have is calculating the failure rate per day over the last 7 days (set by the time picker). I would also like to see what the minimum and maximum failure rates have been at some point during those days as well. I am assuming for this to work, it would need to calculate for each day the hourly failure rates and then display the minimum and maximum for each day in two separate columns.

basesearch...|
        | timechart span=1d count(eval(success="false")) as declined, count as total
        | eval percent_declined=round(declined / total * 100, 1) | table _time total declined percent_declined | eval _time=strftime(_time,"%A, %B %e, %Y %I:%M %p") | rename total as "Total Transactions (volume)", declined as "Total Failed Transactions (volume)", percent_declined as "Failure Rate"

thank you!

[sundareshr · Accepted solution]

Try this

basesearch...|
| timechart span=1h count(eval(success="false")) as declined, count as total
| eval percent_declined=round(declined / total * 100, 1) 
| timechart span=1d sum(total) as total sum(declined) as declined min(percent_declined) as percent_declined_min max(percent_declined) as percent_declined_max avg(percent_declined) as percent_declined_avg
| table _time total declined percent_declined*
| eval _time=strftime(_time,"%c") 
| rename ......

[demkic]

Hi Sundareshr,

I am receiving the following error when running the above query: Error in 'timechart' command: The number of wildcards between field specifier 'percent*' and rename specifier 'percent_declined_max' do not match. Note: empty field specifiers implies all fields, e.g. sum() == sum(*) What does this mean?

[sundareshr]

Try it now. I fixed the typo.

[demkic]

awesome, thank you!

[rajgowd1]

Hi Sundaresh,
i am also trying to display min,max and avg values of failures and success.here is the search
when i try below search,i am getting some values but not sure whether those are correct or not.

index=myindex "|METRICS|" |extract kvdelim=":" pairdelim="," ResponseCode!=200 |search co_name="" co_env="" co_org="" host="" |eval StatusCode=if(ResponseCode>200,"Success","Decline")|timechart span=1h count(eval(StatusCode="Decline")) as Declined, count as Total
| eval Percent_Declined=round(Declined / Total * 100, 1)| timechart span=1d sum(Total) as Total sum(Declined) as Declined min(Percent_Declined) as Percent_Declined_Min
max(Percent_Declined) as Percent_Declined_Max avg(Percent_Declined) as Percent_Declined_Avg|eval Percent_Declined_Avg=round(Percent_Declined_Avg,2)| eval Percent_Declined_Min=round(Percent_Declined_Min,2)|eval Percent_Declined_Max=round(Percent_Declined_Max,2)| table _time Total Declined DeclinePercentage Percent_Declined*
|eval DeclinePercentage=round(Declined / Total * 100, 1)|eval DeclinePercentage=DeclinePercentage+"%"
|fieldformat Total=tostring(Total,"commas")| fieldformat Declined=tostring(Declined, "Commas")
| eval _time=strftime(_time,"%c")

[puneethgowda]

you can exclude below query and use UI option for Commas as it is available in 6.5.1 isn't it!

| fieldformat Declined=tostring(Declined, "Commas")

[sundareshr]

The query looks good. I think the results should be right as well. Do you see different?

[rajgowd1]

Hi Sundaresh,
i just changed time window to 2 hours.i think values are displaying properly.Thank you

[sundareshr]

@rajgowd1 Excellent. Please accept the answer to close it out.

[rajgowd1]

Hi Sundaresh,
some how i am not able to see Accept button in current page.

is it because,this question was asked by someone else?

[aaraneta_splunk]

Hi @rajgowd1 - Yes, it's because this question was asked another user.

@demkic - Was sundareshr's solution helpful? If yes, please click "Accept" below the answer and up-vote any comments that were helpful. If no, please leave more feedback. Thanks!

[rajgowd1]

Yes,i feel different.
when i try this part got wrong results

index=myindex "|METRICS|" |extract kvdelim=":" pairdelim="," |search co_name="" co_env="" co_org="" host="*"|eval StatusCode=if(ResponseCode>200,"Success","Decline")|timechart span=1h count(eval(StatusCode="Decline")) as Declined, count as Total

i don't think there will be 184 requests other than status 200

1. _time Declined Total 2016-12-01 18:00 184 211 2016-12-01 19:00 1080 1241 2016-12-01 20:00 2549 2810 2016-12-01 21:00 4088 4318 2016-12-01 22:00 3061 3234

[sundareshr]

Replace your timechart with this (verify field names in rest of your query)

... | timechart span=1h count by StatusCode | addtotals | rest of your query

[rajgowd1]

i just tried updated query.it is working and now i am able to compare success and failures.Thank you