Splunk Search

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

Explorer

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!

0 Karma
1 Solution

Legend

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

View solution in original post

0 Karma

Legend

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

View solution in original post

0 Karma

Explorer

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 'percentdeclinedmax' do not match. Note: empty field specifiers implies all fields, e.g. sum() == sum() What does this mean?

0 Karma

Legend

Try it now. I fixed the typo.

0 Karma

Explorer

awesome, thank you!

0 Karma

Communicator

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 coname="*" coenv="" co_org="" host="" |eval StatusCode=if(ResponseCode>200,"Success","Decline")|timechart span=1h count(eval(StatusCode="Decline")) as Declined, count as Total
| eval PercentDeclined=round(Declined / Total * 100, 1)| timechart span=1d sum(Total) as Total sum(Declined) as Declined min(PercentDeclined) as PercentDeclinedMin
max(PercentDeclined) as PercentDeclinedMax avg(PercentDeclined) as PercentDeclinedAvg|eval PercentDeclinedAvg=round(PercentDeclinedAvg,2)| eval PercentDeclinedMin=round(PercentDeclinedMin,2)|eval PercentDeclinedMax=round(PercentDeclinedMax,2)| table time Total Declined DeclinePercentage PercentDeclined

|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")

0 Karma

Communicator

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")

0 Karma

Legend

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

0 Karma

Communicator

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

0 Karma

Legend

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

0 Karma

Communicator

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?

0 Karma

Splunk Employee
Splunk Employee

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!

0 Karma

Communicator

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

index=myindex "|METRICS|" |extract kvdelim=":" pairdelim="," |search coname="" coenv="" 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
0 Karma

Legend

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

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

Communicator

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

0 Karma