How to assign value for any field as "0" when no events are there for the field?

[abhi04]

I have a below query. But the below is not giving results after the July 11 date because there are no events for the Failure. Please help on the below as how we can get failure as "0" for the days where we dont have any failure events. Also the result of the below query have been attached.

index=auto_prod_okta eventType="user.authentication.sso" "target{}.alternateId"=SmartCash earliest=-90d
| rename target{}.alternateId AS "id"
| eval time_day = strftime(_time, "%D")
| stats count(id) as Success by time_day
| join
[ search index=auto_prod_okta "target{}.alternateId"=SmartCash outcome.result="FAILURE" earliest=-90d
| rename target{}.alternateId AS "id"
| eval time_day = strftime(_time, "%D")
| stats dc(id) as Failure by time_day]
| eval TCL=(Success+Failure)
| eval Login_Failure_Percent=round((Failure/TCL)*100,4)
| rename TCL AS Total
| table time_day Failure, Total, Login_Failure_Percent
| sort -time_day

[somesoni2 · Accepted solution]

Give this a try Updated

index=auto_prod_okta (eventType="user.authentication.sso" OR outcome.result="FAILURE") "target{}.alternateId"=SmartCash earliest=-90d 
| eval Success=if(eventType="user.authentication.sso",'target{}.alternateId', null())  
| eval Failure=if('outcome.result'="FAILURE",'target{}.alternateId', null()) 
| timechart span=1d count(Success) as Success dc(Failure) as Failure
| eval time_day = strftime(_time, "%D") 
| eval Total=(Success+Failure) 
| eval Login_Failure_Percent=round((Failure/Total)*100,4) 
| table time_day Failure, Total, Login_Failure_Percent 
| sort -time_day

[abhi04]

This gives me 0 failures for all the days even when the failures are not 0.

[somesoni2]

There was single quotes missing in the eval Failure line. Try now.

[abhi04]

Thanks @somesoni2 . Appreciated your help.

[Kawtar]

Hello @abhi04,

You should initiate the value with a default value.
Per example:

     1. | eval Login_Failure_Percent  =  0 

Can you try this.