I have this kind of data,
Event ID | Event Step | Status | |
100 | 1 | SUCCESS | |
100 | 2 | SUCCESS | |
100 | 3 | FAILURE | |
100 | 4 | FAILURE | |
100 | 5 | SUCCESS | |
100 | 6 | FAILURE |
Success Condition : When Event Steps 1,2,4 and 5 ALL are SUCCESS , then SUCESS, Else it is in PROGRESS.
Can you help me with the query to find out the same.
I have tried AND Condition, but it is not giving me correct result, because it searches for one condition only.
I have tried OR as well, but that gives me wrong count.
You need to evaluate the status of each step and recombine them into a single row per event (stats), then evaluate your success condition
...
| eval status1=if(step=1,status,null)
| eval status2=if(step=2,status,null)
| eval status3=if(step=3,status,null)
| eval status4=if(step=4,status,null)
| eval status5=if(step=5,status,null)
| eval status6=if(step=6,status,null)
| stats values(status1) as status1, values(status2) as status2, values(status3) as status3, values(status4) as status4, values(status5) as status5, values(status6) as status6 by event
| eval overall=if(status1="SUCCESS" AND status2="SUCCESS" AND status4="SUCCESS" AND status5="SUCCESS", "SUCCESS", "IN PROGRESS")
| table event, overall