How to stats by merging multiple events

[humantorch]

I have events in same index and source-type as follows:

9/12/19 11:28:46.398 AM [WARNING/ForkPoolWorker-13] project="xyz",begin="stage1",job_id=1
9/12/19 11:30:46.398 AM [WARNING/ForkPoolWorker-13] project="xyz",begin="stage1",job_id=2
9/12/19 11:38:46.398 AM[WARNING/ForkPoolWorker-13] project="xyz",end="stage1",job_id=1
9/12/19 11:35:46.398 AM[WARNING/ForkPoolWorker-13] project="xyz",end="stage1",job_id=2
9/12/19 11:35:48.398 AM[WARNING/ForkPoolWorker-13] project="xyz",begin="stage2",job_id=2
9/12/19 11:38:48.398 AM[WARNING/ForkPoolWorker-13] project="xyz",begin="stage2",job_id=1
9/12/19 11:45:48.398 AM[WARNING/ForkPoolWorker-13] project="xyz",end="stage2",job_id=2
9/12/19 11:45:50.398 AM[WARNING/ForkPoolWorker-13] project="xyz",result="SUCCESS",job_id=2
9/12/19 11:48:46.398 AM[WARNING/ForkPoolWorker-13] project="xyz",end="stage2",job_id=1
9/12/19 11:48:47.398 AM[WARNING/ForkPoolWorker-13] project="xyz",result="SUCCESS",job_id=1

I want to create a table for each project that lists all job_id and duration of each stages followed by whether that job_id was SUCCESS or FAIL

Like this:

Project XYZ Report:
job_id stage1(min) stage2(min) result

---

1 10 8 SUCCESS
2 5 10 SUCCESS

I tried join and append but they are really slow. What is the best way to achieve this?

[renjith_nair]

@humantorch ,

Try this and test against your data

"your base search" "other filters"
|eventstats values(result) as result by job_id,project
|eval stage=coalesce(begin,end)
|stats min(_time) as start,max(_time) as end,first(result) as result by job_id,stage,project
|eval diff=round((end-start)/60,2)
|eval {stage}=diff|fields - stage,start,end,diff
|stats values(*) as * by project,job_id