Splunk Search
Highlighted

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

Engager

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?

0 Karma
Highlighted

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

Builder

Can you try sum instead of count?

| stats count as totalcount, sum(exception) as exceptioncount by app_id

0 Karma
Highlighted

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

Legend

@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



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

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

Engager

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

0 Karma
Highlighted

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

Esteemed Legend

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"

View solution in original post

Highlighted

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

Engager

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

0 Karma