Hi Team,
I am looking for a way in DQL to get the list of services with the request names and failure rate for that request based on management zone or/and tags. And, then compare it with previous week to get the list of requests for which failure rate has increased.
Sample:
// calculate HTTP 5xx response status code request rate. We are looking for each endpoints and interval of 1 day
timeseries {total = sum(dt.service.request.count),by: { endpoint.name}, interval:24h }, from:-7d, to:-1d
//filtering out the Non Key requests and including the requests that has "connected" in them
| filterOut contains(endpoint.name, "NON_KEY")
//Instead of end point name I would like to have a management zone filter below and would also like to include 4xx errors
| Filter contains(endpoint.name, "ABC") or contains(endpoint.name, "DEF")
//joining the metrics to calculate the error rate
| join [
timeseries {fiveXX = sum(dt.service.request.count, default: 0.0), by: { endpoint.name}, interval:24h}, from:-7d, to:-1d,
filter:http.response.status_code >= 500 //filtering for 5xx errors
and http.response.status_code <= 599
and contains(endpoint.name, "connectedhome") or contains(endpoint.name, "browsebuy")
//], kind:leftOuter, prefix: "httpState.", on:{ timeframe, endpoint.name }
], kind:inner, prefix: "httpState.", on:{ timeframe, endpoint.name }
| fieldsAdd {rate5xx = httpState.fiveXX[] / total[] * 100} //rate calculation
/*
| filter rate5xx[0] > 0.0
and rate5xx[1] > 0.00
and rate5xx[2] > 0.00
and rate5xx[3] > 0.00
and rate5xx[4] > 0.00
and rate5xx[5] > 0.00
and rate5xx[6] > 0.00//only rate > 0.0
*/
| fieldsAdd minusSixRate = rate5xx[0],
todayRate = rate5xx[6] //assigning rates for the days
| filter minusSixRate != 100.00 and todayRate != 100.00
| fields endpoint=endpoint.name, Last_Week_Rate=minusSixRate, Yesterday_Rate=todayRate, Total_Req_Yesterday=total[6], Total_Failed_Yesterday=httpState.fiveXX[6]
| sort Yesterday_Rate desc
Any help is appreciated. Thanks.
