I'm trying to chart the exception rate of various apps that we run, and would ideally be generating a table that looks like this:
App ID | Exception | Number of ops with this exception | Total number of ops for this app
However, I haven't quite been able to get it working. The closest I have is this:
... | stats count as total_count, count(exception) as exception_count by app_id, exception
This isn't quite right since it only finds operations where an exception isn't null. Thus, for every row in the output table, totalcount = exceptioncount. It seems like I need to find all operations, then filters by appid, then finds the totalcount, then filter by exception, then find the exception_count. What's the best way to do this?
Can you try sum instead of count?
| stats count as totalcount, sum(exception) as exceptioncount by app_id
@watersd for the community to assist you better please add more context to your question. Kindly add some mock/anonymized sample data for us to understand what values you have for app_id, exception and also what an event look like which does not have exception.
Can you try the following search where if an event has exception field it will be counted as exception? I have removed
exception fields from split by field and moved it as aggregate function for counting when it is not null.
<yourCurrentSearch> | stats count as total_count, count(eval(isnotnull(exception)) as exception_count by app_id
Thanks. I updated the question with more info. It seems like I do need the exception field in the split by clause eventually.
... | eval exception=coalesce(exception, "WAS_NULL") | stats count BY app_id, exception | eventstats sum(count) AS total_count BY app_id | search NOT exception="WAS_NULL"
See this run-anywhere PoC:
index=_internal | rename component AS exception, sourcetype AS app_id | eval exception=coalesce(exception, "WAS_NULL") | stats count BY app_id, exception | eventstats sum(count) AS total_count BY app_id | search NOT exception="WAS_NULL"