Adding two stat results using one common field

[husainpatanwala]

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 !

[ITWhisperer]

Try this

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

[husainpatanwala]

thanks mate,this one seems to be working.

[tscroggins]

@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?