topic Adding two stat results using one common field in Splunk Search

Adding two stat results using one common field

husainpatanwala — Wed, 05 May 2021 22:59:27 GMT

Hi guys I have two stats

index |Exception| count

index |Error |count

I want is something like this :

index |Exception|Error |count .

count should be shown for exceptions where it is present for an index and same for Error.

something like this

index Exception Error count

index_1 ArithmeticException 50

index_2 loginerror 2

index_1 inputerror 5

the count shouldnt mess up due to same index.Please help !

Re: Adding two stat results using one common field

tscroggins — Wed, 05 May 2021 22:58:22 GMT

@husainpatanwala

stats omits output with null values in the by clause. The simplest way to achieve your desired output where count is greater than zero is to assign empty string values to Exception and Error when they're null:

| eval Exception=coalesce(Exception, ""), Error=coalesce(Error, "")
| stats count by index Exception Error

The coalesce() eval function is similar to the SQL COALESCE() function and returns the first non-null value in the argument list.

To include Exception and Error values where count is zero, you'll need to define a list of known Exception and Error values, append the list to your output, sort the list by descending count values, and deduplicate the Exception and Error output. Is this your intent?

Re: Adding two stat results using one common field

ITWhisperer — Wed, 05 May 2021 22:58:53 GMT

Try this

search | stats count by index exception | append [ search | stats count by index error ]

Re: Adding two stat results using one common field

husainpatanwala — Wed, 05 May 2021 23:07:27 GMT

thanks mate,this one seems to be working.