How to sum the numbers based on a condition and store it in new field?

[amaurya1]

Requirement -
account_no can have many session_no and session_no can have many sub_session_no. For each session, I want to find the total number of fails with no success at all and finally I want to sum all these fails.

index=abc sourcetype=xyz 
| eval is_passed=if(label=="PASS", 1, 0)
| eval is_failed=if(label=="FAIL", 1, 0)
| stats values(sub_session_no),  sum(is_passed) as pass, sum(is_failed) as fail by account_no session_no
| eval fails_within_session = if(fail > 0 and success = 0, sum(fail), 0)
| stats sum(fails_within_session) as Total_failed

Problem - I get an error in the second last line of the code saying "sum" can't be used with eval. Can someone please help me with this?

[rbechtold · Accepted solution]

Hey Amaurya,

Since the "fail" field is already a sum of the "is_failed" field by account_no and session_no, you should be able to use it directly in place of the "sum(fail)" portion of your eval function to find the failures per session.

 index=abc sourcetype=xyz 
| eval is_passed=if(label=="PASS", 1, 0) 
| eval is_failed=if(label=="FAIL", 1, 0) 
| stats values(sub_session_no), sum(is_passed) as pass, sum(is_failed) as fail by account_no session_no 
| eval fails_within_session = if(fail > 0 AND pass = 0, fail, 0)
| stats sum(fails_within_session) as Total_failed

If I'm misunderstanding the question, please let me know and I will re-work the answer!

[amaurya1]

@rbechtold - Thanks a lot.. I think you are right, it's giving me correct answer.