Splunk Search

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

Explorer

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

0 Karma
1 Solution

Contributor

hi Hung_Nguyen,

I think this should do it -

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

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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
0 Karma

Explorer

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

0 Karma

Contributor

hi Hung_Nguyen,

I think this should do it -

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

View solution in original post

0 Karma

Explorer

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

0 Karma

Influencer

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

0 Karma

Explorer

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

0 Karma

Contributor

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 uripath="index.htm") OR (Type=ERROR Message="Widget load failed") | stats count(eval(uripath="index.htm")) as UsersCount, count(eval(Type="ERROR")) as errors | eval ratio = round((errors/UsersCount)*100, 2) | table "Widget ID", ratio

0 Karma

Explorer

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

0 Karma

Contributor

hi Hung_Nguyen ,

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

0 Karma

Explorer

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

0 Karma

Contributor

can you paste sample data ?

0 Karma

Influencer

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)
0 Karma