How to use each value in a column to determine count of "success", "failure", or "error", then calculate the success percentage?

[jshellman]

I have a search, something like this:

search stuff
    | rex "extract cat"
    | rex "extract field2"
    | rex "extract field3"
    | eval theValue=coalesce(field2, field3)
    | stats count by cat, theValue
    | table count, cat, theValue

So, the output is something like this:

count  | cat   | theValue
55     | BER   | A
 2     | BER   | 302
 1     | BER   | 201
14     | CCG   | 502
 3     | CCG   | null
88     | CCG   | 100

Now, if theValue is A or 100 (or one of a small constant set of values), then it represents a "success" case, if theValue is null it's an error, and any other theValue is an expected "failure" case. So, for each cat value, I want to calculate a percentage of success/failure and show the error count if it exists separately.

So, I want to change the above to: (expressions in {} show the calculation and I want to show the result of that)

cat | success       | errors
BER | {55/(55+2+1)} | 
CCG | {88/(88+14)}  | 3

How might I accomplish this?

[somesoni2 · Accepted solution]

Try like this

Updated null value handling

search stuff
| rex "extract cat"
| rex "extract field2"
| rex "extract field3"
| eval theValue=coalesce(field2, field3)
| stats count by cat, theValue
| eval status=case(theValue="A" OR theValue="100" OR thevalue="AnyOtherCondition","success", theValue="null","errors",1=1,"failure")
| chart sum(count) over cat by status
| fillnull value=0 success failure errors
| eval success=round(success/(success+failure+errors),2)
| table cat success error

[jshellman]

Thanks! That almost did it. Because failures and errors don't always occur, I needed to add 3 lines like

| eval errors=if(isnotnull(errors), errors, 0)

or else the success wouldn't show up. Maybe there's an easier way to do that, but it worked.

[somesoni2]

This would work just fine. For reference, these are ways you can handle assigning default value, in the order of my personal preference.

...| fillnull value="DefaultValue" fieldname1 fieldname2...

...| eval fieldname1=coalesce(fieldname1,"DefaultValue") ...

...| eval fieldname1=if(isnotnull(fieldname1),fieldname1,"DefaultValue").....