How to get total and sub search total per unique field value?

[watersd]

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, total_count = exception_count. It seems like I need to find all operations, then filters by app_id, then finds the total_count, then filter by exception, then find the exception_count. What's the best way to do this?

[woodcock · Accepted solution]

Like this:

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

[watersd]

Amazing! That's exactly what I was looking for. Thanks!

[niketn]

@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

[watersd]

Thanks. I updated the question with more info. It seems like I do need the exception field in the split by clause eventually.

[nareshinsvu]

Can you try sum instead of count?

| stats count as total_count, sum(exception) as exception_count by app_id