Splunk Search
Highlighted

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

Path Finder

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=autoprodokta eventType="user.authentication.sso" "target{}.alternateId"=SmartCash earliest=-90d
| rename target{}.alternateId AS "id"
| eval timeday = strftime(time, "%D")
| stats count(id) as Success by timeday
| join
[ search index=auto
prodokta "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 LoginFailurePercent=round((Failure/TCL)*100,4)
| rename TCL AS Total
| table timeday Failure, Total, LoginFailurePercent
| sort -time
day

0 Karma
Highlighted

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

Path Finder

Hello @abhi04,

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

     1. | eval Login_Failure_Percent  =  0 

Can you try this.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

View solution in original post

Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Path Finder

Thanks @somesoni2 . Appreciated your help.

0 Karma