Splunk Search

Combine 2 searches to get Success and Failure Rate

shashank_24
Path Finder

Hi, I am working on a requirement where I have write an alert based on the failure rate percentage of a service. Let's say I have 10 web services and I want to trigger the alert based on the traffic, successful and failed requests. 

 

I have written a query but it doesn't seem to giving me the correct results -

 

index=myapp_prod sourcetype=myapp_service_log "System Exception" NOT "responseStatus=SUCCESS" NOT "ResponseStatusCode=404" NOT "Business Exception"  
| stats count as Failures by serviceName 
| appendcols
    [ search index=myapp_prod sourcetype=myapp_service_log "responseStatus=SUCCESS" 
    | stats count as Success by serviceName
    | fillnull]
| eval Total = Success + Failures

<!-- when failureRatePercentage > 10 -->

 

What i want is table like this. With my query, it's not coming right. I am getting multiple rows with same serviceName and empty column

serviceNameTotalRequestSuccessFailedFailureRatePercentage
service1100080020020
service22000150050025

 

Can anyone advice how can i achieve this? It's better to set the alert based on failure percentage rather than the absolute value

Labels (4)
0 Karma

manjunathmeti
Champion

hi @shashank_24 ,
Try this:

index=myapp_prod sourcetype=myapp_service_log "System Exception" NOT "responseStatus=SUCCESS" NOT "ResponseStatusCode=404" NOT "Business Exception"  
| stats count as Failures by serviceName 
| appendcols
    [ search index=myapp_prod sourcetype=myapp_service_log "responseStatus=SUCCESS" 
    | stats count as Success by serviceName
    | fillnull]
| stats max(*) as * by serviceName
| eval Total = Success + Failures, FailureRatePercentage=(Failures*100)/Total

 

If this reply helps you, a like would be appreciated.

0 Karma

shashank_24
Path Finder

Hi @manjunathmeti , Thanks for the response. I have already tried it actually and what i get is a weird table like this - rows up and down. Not sure why it is coming like this. Is it because I am using BY clause?

serviceNamefailedRatePercFailuresSuccessTotal
service10.7968127490039842249251
service20.97799511002444984405409
service399.95985547972701249012491
service4  3032 
service5  2222 

 

If you see above table the data is not aligned and some of the columns are empty. It looks like it's appending the rows. I need this to be aligned and should show the data for the correct service.

0 Karma

manjunathmeti
Champion

It means that you don't have any failures for the last 2 serviceName values. Use fillnull command after stats command to fill null values.

index=myapp_prod sourcetype=myapp_service_log "System Exception" NOT "responseStatus=SUCCESS" NOT "ResponseStatusCode=404" NOT "Business Exception"  
| stats count as Failures by serviceName 
| appendcols
    [ search index=myapp_prod sourcetype=myapp_service_log "responseStatus=SUCCESS" 
    | stats count as Success by serviceName]
| stats max(*) as * by serviceName
| fillnull
| eval Total = Success + Failures, FailureRatePercentage=(Failures*100)/Total

 

0 Karma

shashank_24
Path Finder

@manjunathmeti  No it's not giving the correct result. I think the appendcols is not working here. If you see the image i attached (Sorry I had to mask the service name due to security reasons), the 3rd service shows the failure rate as Zero where as that service has more than 50% failure rate in last 24 hours.

Also if i run the sub search which is counting the exceptions I get more than 2k result. So something is not right

shashank_24_0-1618948214522.png

 

0 Karma

manjunathmeti
Champion

I didn't see you are using appendcols. You need to use append.

index=myapp_prod sourcetype=myapp_service_log "System Exception" NOT "responseStatus=SUCCESS" NOT "ResponseStatusCode=404" NOT "Business Exception"  
| stats count as Failures by serviceName 
| append
    [ search index=myapp_prod sourcetype=myapp_service_log "responseStatus=SUCCESS" 
    | stats count as Success by serviceName]
| stats max(*) as * by serviceName
| fillnull
| eval Total = Success + Failures, FailureRatePercentage=(Failures*100)/Total

 

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!