How to populate statistics listing all the names where both status=FAIL and status=Success exist

[limalbert]

A sample set of logs with fieldnames (time, name, and status) from one index=test

1. name=X1 status=FAIL time=7am
1. name=X1 status=FAIL time=7:01am
2. name=X1 status=SUCCESS time=7:02am
3. name=X2 status=SUCCESS time=8am
4. name=X3 status=FAIL time=9am
5. name=X3 status=FAIL time=9:01am
6. List item

Current search:

index=test | stats count as tryCount by name, status
| chart count(status) by tryCount, status

Question 1: Count name of tries by status (success, fail, fail+success). Query above isn't exactly accurate because it's ignoring the category that has both fail+success.
Expected result:
name success fail fail+success
X1 1
X2 1
X3 1

Question 2: How do I populate statistics listing all the name where both status=FAIL and status=Success exist?
Expected result:

name   statusList  
X1     FAIL - 7am, FAIL - 7:01am,  SUCCESS - 7:02am

Question 3, how do I show list of status for this scenario with row showing name and column showing by _time?
Expected result:

**name   failList      successList 
X1     7am, 7:01am    7:02am
X2                    8am**

[Sukisen1981]

code for ques 3

|sort status,- time|eval fl=case(status="FAIL",time),sc=case(status="SUCCESS",time)|stats values(fl) as fl ,values(sc) as sc by name|makemv delim="*" fl|makemv delim="*" sc|nomv fl|nomv sc|rename fl as failist,sc as successlist

[Sukisen1981]

here is the code for ques 2

|sort status,time|eval status_time= status.",".time|stats values(status_time) as newfld,values(status) as status by name|mvexpand newfld|where mvcount(status)>1|fields name,newfld| mvcombine delim=" " newfld|nomv newfld|rename newfld as statuslist

[limalbert]

@Sukisen1981 is it possible to add a requirement where it'll show the list with timestamp gap different of 15 minutes?

[Sukisen1981]

hi @limalbert
It is possible but you need to filter (make a bin of time 15 mins) before this code

[Sukisen1981 · Accepted solution]

Hi @limalbert
here is the code for question1

|stats values(status) as status by name|eval newstatus=if(mvcount(status)>1,"Fail+Success",status)|eval {newstatus}=name|fields - status, - newstatus|fillnull value=0 FAIL,Fail+Success,SUCCESS|eval FAIL=case(FAIL=0,"",FAIL !="0",1)|eval SUCCESS=case(SUCCESS=0,"",SUCCESS !="0",1)|rename "Fail+Success" as fascs|eval fascs=case(fascs=0,"",fascs !="0",1)|rename fascs  as "Fail+Success"|fields name,FAIL,SUCCESS,"Fail+Success"

Assumption - all 6 lines in your sample are different events
Get back to you on ques. 2& 3

[limalbert]

This is totally correct!! Thank you!