topic Re: How to get total and sub search total per unique field value? in Splunk Search

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

watersd — Wed, 30 Sep 2020 01:19:29 GMT

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?

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

nareshinsvu — Wed, 30 Sep 2020 01:19:39 GMT

Can you try sum instead of count?

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

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

niketn — Wed, 17 Jul 2019 02:45:17 GMT

@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

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

watersd — Thu, 18 Jul 2019 17:42:41 GMT

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

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

woodcock — Sat, 27 Jul 2019 19:59:25 GMT

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"

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

watersd — Mon, 29 Jul 2019 17:07:46 GMT

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