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
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Community Content Calendar, September edition

Welcome to another insightful post from our Community Content Calendar! We're thrilled to continue bringing ...

Splunkbase Unveils New App Listing Management Public Preview

Splunkbase Unveils New App Listing Management Public PreviewWe're thrilled to announce the public preview of ...

Leveraging Automated Threat Analysis Across the Splunk Ecosystem

Are you leveraging automation to its fullest potential in your threat detection strategy?Our upcoming Security ...