How to compare success/failure by status

[mwolfe]

I've got data so:

"[clientip]  [host] - [time] [method] [uri_path] [status] [useragent]" ..  
and do the following search:

index=web  uri_path="/somepath" status="200" OR status="400"
| rex field=useragent "^(?<app_name>[^/]+)/(?<app_version>[^;]+)?\((?<app_platform>[^;]+); *"
| eval app=app_platform+" "+app_name+" "+app_version

I've split up the useragent just fine and verified the output. I want to now compare status  by "app".
So I've added the following:

| stats count by app, status

Which gives me:

app	status	count
android app 1.0	200	5000
ios app 2.0	400	3
android app 1.1	200	500
android app 1.0	400	12
ios app 2.0	200	3000

How can I compare, for a given "app" (combo of platform, name, version) the rate of success where success is when the response = 200 and failure if 400. I understand that I need to take success and divide by success + failure count.. But how do I combine this data? 
Also note that I need to consider that some apps may not have any 400 errors. 

[mwolfe]

I think I got it 

| eval success=if(status=200,1,0)
| eval failure=if(status=400,1,0)
| stats sum(failure) as fail_sum, sum(success) as success_sum by app
| eval success_rate=round((success_sum / (success_sum + fail_sum))*100,1)
| table app, success_rate

[PickleRick]

You can also check out two nice commands - xyseries and untable which can be used to (de)tabularize such data series.

[gcusello · Accepted solution]

Hi @mwolfe ,

don't use sum but count:

index=web  uri_path="/somepath" status="200" OR status="400"
| rex field=useragent "^(?<app_name>[^/]+)/(?<app_version>[^;]+)?\((?<app_platform>[^;]+); *"
| eval app=app_platform+" "+app_name+" "+app_version
| eval success=if(status=200,1,0)
| eval failure=if(status=400,1,0)
| stats 
     count(failure) AS fail_count
     count(success) AS success_count
     BY app
| eval success_rate=round((success_count / (success_count + fail_count))*100,1)
| table app success_rate

otherwise, you could insert the eval in the stats:

index=web  uri_path="/somepath" status="200" OR status="400"
| rex field=useragent "^(?<app_name>[^/]+)/(?<app_version>[^;]+)?\((?<app_platform>[^;]+); *"
| eval app=app_platform+" "+app_name+" "+app_version
| stats 
     count(eval(status=400)) AS fail_count
     count(eval(status=200)) AS success_count
     BY app
| eval success_rate=round((success_count / (success_count + fail_count))*100,1)
| table app success_rate

Ciao.

Giuseppe

[gcusello]

Hi @mwolfe ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉