How to group by field and calculate Error rate ?

[vamshiverma]

Hello,

I'm trying to determine the Error rate for individual servicename . I'm having trouble while performing group by followed by error_rate determining query.

This is how I calculate the Count for the individual servicenames.

index=myindex ServiceName="foo.bar.*" |stats count by ServiceName

ServiceName	Count
foo.bar.apple	10
foo.bar.banana	20

The following query determines the failure rate i.e status NOT OK , for the entire service , in my case  apple and banana services.

index=myindex ServiceName="foo.bar*"
 | eventstats count(HTTPStatus) as Total 
 | where HTTPStatus!=200 
 | stats count(HTTPStatus) as Error, values(Total) as Total
 | eval fail_rate = Error*100/Total 
 | fields fail_rate

fail_rate

0.0012

I want to have something like below, individual error rates for the services foo.bar.apple, foo.bar.banana.

ServiceName	Count	fail_rate
foo.bar.apple	10	0.0010
foo.bar.banana	20	0.0014

This is the query I'm trying to achieve the above table. I'm aware that, we need store count for each service name and again we need to run the query separately to determine the fail count, we cannot do this in parallel.

index=myindex ServiceName="foo.bar*"
 | eventstats count(HTTPStatus) as Total by ServiceName
 | where HTTPStatus!=200 
 | stats count(HTTPStatus) as Error, values(Total) as Total
 | eval fail_rate = Error*100/Total 
 | fields fail_rate

I appreciate your support and time!

Vamshi.

[493669 · Accepted solution]

Hi @vamshiverma 

Try below

index=myindex ServiceName="foo.bar*"
 | eventstats count(HTTPStatus) as Total by ServiceName
 | where HTTPStatus!=200 
 |stats count(eval(HTTPStatus!=200)) AS Error values(Total) as Total count as Count by ServiceName
 | eval fail_rate = Error*100/Total 
 | fields fail_rate ServiceName	Count

[vamshiverma]

@493669  Awesome! Worked like a charm. Thank you!

[vamshiverma]

@493669  I wonder is there any way to return foo.bar.* services even they have no results. For example, using this query if I have no error rate for foo.bar.banana it is not showing up. I want to display all the servicenames regardless if the error_rate is non-existent, I want it to be default set to zero.

-Thank you!