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?
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
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
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.
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").....