How to get Success, In Progress and Failure Count from this data

[agar1122]

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.

[ITWhisperer]

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