Table with foreach search

[dkaharlytskyi]

Hi there, I'm new to Splunk and will be grateful for advice 

I have the following events:

{
    PROJECT_NAME = project1
    JOB_NAME = jobA
    JOB_RESULT = success
}

{
    PROJECT_NAME = project2
    JOB_NAME = job2
    JOB_RESULT = fail
}

I need to build the following table:

JOB_NAME	TOTAL_SUCCESS	TOTAL_FAILS
"for each JOB_NAME in PROJECT_NAME"	"sum of JOB_RESULT success for JOB_RESULT "	"sum of JOB_RESULT fail for JOB_RESULT "

Could you please help with queries for the table? 

Many thanks in advance!

[richgalloway]

Assuming the fields are already extracted, the stats command should do what you want.

| stats count(eval(JOB_RESULT="success")) as TOTAL_SUCCESS,
        count(eval(JOB_RESULT="fail")) as TOTAL_FAILS by PROJECT_NAME, JOB_NAME

[dtburrows3]

I'm not sure If I am completely understanding the ask here but will give it a shot.

So just going off your 2 sample events I think something like this would work. (There is an assumption that the fields "JOB_RESULT", "JOB_NAME", and "PROJECT_NAME" are already extracted and ready to use)

This search is tallying up the success and fails across all jobs grouped into the projects, so each project will have its own row in the final results.

<base_search>
    | stats
        count(eval('JOB_RESULT'=="success")) as TOTAL_SUCCESS,
        count(eval('JOB_RESULT'=="fail")) as TOTAL_FAILS
            by PROJECT_NAME

 Output would look something like this.

Or if you need it more granular to see the numbers at the Job level you can use this.

<base_search>
    | stats
        count(eval('JOB_RESULT'=="success")) as TOTAL_SUCCESS,
        count(eval('JOB_RESULT'=="fail")) as TOTAL_FAILS
            by PROJECT_NAME, JOB_NAME

 This will provide you output that each unique combo of PROJECT_NAME/JOB_NAME will have their own row and output would look like this.

For reference, here is the SPL used to simulate your problem on my local instance.

| makeresults 
    | eval
        _raw="{
    PROJECT_NAME = project1
    JOB_NAME = jobA
    JOB_RESULT = success
}"

    | append
        [
            | makeresults
                | eval
                    _raw="{
    PROJECT_NAME = project2
    JOB_NAME = job2
    JOB_RESULT = fail
}"
            ]  
    ```
    | extract pairdelim=" " kvdelim="="
    | stats
        count(eval('JOB_RESULT'=="success")) as TOTAL_SUCCESS,
        count(eval('JOB_RESULT'=="fail")) as TOTAL_FAILS
            by PROJECT_NAME
    ```
    | extract pairdelim=" " kvdelim="="
    | stats
        count(eval('JOB_RESULT'=="success")) as TOTAL_SUCCESS,
        count(eval('JOB_RESULT'=="fail")) as TOTAL_FAILS
            by PROJECT_NAME, JOB_NAME