Splunk Search

How to group by field and calculate Error rate ?

vamshiverma
Explorer

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

 

ServiceNameCount
foo.bar.apple10
foo.bar.banana20

 

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.

ServiceNameCountfail_rate
foo.bar.apple100.0010
foo.bar.banana200.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.

 

Labels (5)
0 Karma
1 Solution

493669
Super Champion

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

View solution in original post

493669
Super Champion

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
Explorer

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

0 Karma

vamshiverma
Explorer

@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!

0 Karma
Get Updates on the Splunk Community!

Index This | What did the zero say to the eight?

June 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...

Splunk Observability Cloud's AI Assistant in Action Series: Onboarding New Hires & ...

This is the fifth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Now Playing: Splunk Education Summer Learning Premieres

It’s premiere season, and Splunk Education is rolling out new releases you won’t want to miss. Whether you’re ...