Splunk Search

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

demkic
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

sundareshr
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

sundareshr
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 ......
0 Karma

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

0 Karma

sundareshr
Legend

Try it now. I fixed the typo.

0 Karma

demkic
Explorer

awesome, thank you!

0 Karma

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

0 Karma

puneethgowda
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

sundareshr
Legend

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

0 Karma

rajgowd1
Communicator

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

0 Karma

sundareshr
Legend

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

0 Karma

rajgowd1
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

aaraneta_splunk
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

rajgowd1
Communicator

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
0 Karma

sundareshr
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

rajgowd1
Communicator

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

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>