How to group events and extract a field when grouped events contain a specific value?

[kevinkuszyk]

We have some overnight jobs that run and log out to Splunk. On top of this, we have a dashboard which groups by the job id and extracts information like start time, end time, duration etc.

The query looks a bit like this:

index=foo | stats range(_time) as duration by job-id | table job-id duration

We now want to add a status column to tell us if the job completed or had an error. If any of the events in a grouping have a log level of ERROR it should show Error, otherwise it should show Ok.
I've tried this snippet:

eval status=if(in(level, "ERROR"), "Error", "Ok")

Which is fine for evaluating on each event, but I want the grouping to show either Error or Ok depending on values in the the level field for each group.

Is this possible in Splunk, and how should I write the query?

[woodcock]

Maybe this:

index=foo
| stats range(_time) AS duration count(eval(level="ERROR")) AS Errors BY job-id

[woodcock]

Like this:

Your base search here
| stats count(eval(level == "ERROR")) AS errors count AS total BY group
| eval non_errors = total - errors

[renjith_nair · Accepted solution]

@kevinkuszyk ,

Try this ,

|stats min(eval(if(level=="ERROR","Error","Ok"))) as status by group

OR

|stats count(eval(if(level=="ERROR",1,null()))) as status by group|eval status=if(status>0,"Error","Ok")