How to edit my search to calculate the error ratio by a certain field?

[Hung_Nguyen]

My website has multiple widgets owned by various team and hosted on various CDN. I want to see the error rate by widget. I came up with this Splunk search

 index=tto*  Type=ERROR Message="Widget load failed" | stats count(eval(Type="ERROR")) as errors by widgetId

The table shows up as

Widget ID | count
WidgetA   |  45
WidgetB   |  90

I would like to modify it so that I would get the error rate (errors/total users), so it would look like this

Widget ID | count
WidgetA   |  1%
WidgetB   |  2%

I modified the search to count the total number of user

index=tto*  (sourcetype=access method=GET uri_path="index.htm") OR (Type=ERROR Message="Widget load failed") | stats count(eval(uri_path="index.htm")) as UsersCount, count(eval(Type="ERROR")) as errors 

however, I don't know how I can calculate the error rate. would love some advice. thank you

[DalJeanis]

try -

index=tto* (sourcetype=access method=GET uri_path="index.htm") OR (Type=ERROR Message="Widget load failed") 
| stats count(eval(uri_path="index.htm")) as UsersCount, count(eval(Type="ERROR")) as errors by widgetId
| eval ratio = round((errors/UsersCount)*100, 2) 
| table widgetId, UsersCount, errors, ratio

[Hung_Nguyen]

Hi Dal, the UserCount is 0 when I put in the "by widgetId" string but it would compute correctly without it.

[nmohammed · Accepted solution]

hi Hung_Nguyen,

I think this should do it -

index=tto* (sourcetype=access method=GET uri_path="index.htm") OR (Type=ERROR Message="Widget load failed") | stats count(eval(uri_path="index.htm")) as UsersCount, count(eval(Type="ERROR")) as errors | eval ratio = round((errors/UsersCount)*100, 2) | table widgetId, ratio

[Hung_Nguyen]

Hi Mason,

I get a table like one below. where I only see 1 row and the widgetId column is empty, and I should see 3 rows. thanks

widgetId | ratio
| 0.13

[masonmorales]

That wasn't mine. 😉 Did you try the one I posted?

[Hung_Nguyen]

sorry Mason misread the name. I tried yours but I just get this table

Success | Failures | ratio
202019 | 267 | 0.13

I think I need to add the "By wigdetId" part

count(eval(Type="ERROR")) as errors by widgetId but if I add that it doesn't work as well

[nmohammed]

in which field are you capturing the widgetId or "Widget ID"?

you were able to produce results using the widgetId field using your first query right ?
index=tto* Type=ERROR Message="Widget load failed" | stats count(eval(Type="ERROR")) as errors by widgetId

The table shows up as
Widget ID | count
WidgetA | 45
WidgetB | 90

can you try this ?

index=tto* (sourcetype=access method=GET uri_path="index.htm") OR (Type=ERROR Message="Widget load failed") | stats count(eval(uri_path="index.htm")) as UsersCount, count(eval(Type="ERROR")) as errors | eval ratio = round((errors/UsersCount)*100, 2) | table "Widget ID", ratio

[Hung_Nguyen]

Hi mohammed, yes the first query produced the right table, your query produces a table like one below. where I only see 1 row and the widgetId column is empty, and I should see 3 rows.

Success | Failures | ratio
202019 | 267 | 0.13

"Widget ID" and widgetId are the same. sorry typo

[nmohammed]

hi Hung_Nguyen ,

in which field are you capturing the widgetId or "Widget ID"?

[Hung_Nguyen]

they are the same. I just rename widgetId to "Widget ID" for display purpose

[nmohammed]

can you paste sample data ?

[masonmorales]

You can use eval to calculate the percentage:

index=tto* (sourcetype=access method=GET uri_path="index.htm") OR (Type=ERROR Message="Widget load failed") | stats count(eval(uri_path="index.htm")) as UsersCount, count(eval(Type="ERROR")) as errors | eval error_rate_perc=round(errors/UsersCount*100)