How to get the values for time_hour?

[abhi04]

I have a below query, which displays the Success, Failure, Total and Failure_Percent by time_hour. It only displays the value for the hour for which we have failure. I want t to display Failure for the time_hour for which it is zero also. Please help me on the same. Below is the query:

index=auto_prod_okta eventType="user.session.start" outcome.result="SUCCESS" earliest=@d | eval time_day = strftime(_time, "%D") | eval time_hour = strftime(_time, "%H")
| stats count(actor.alternateId) as Success by time_hour| join [search index=auto_prod_okta eventType="user.session.start" outcome.result="FAILURE" NOT "outcome.reason"=INVALID_CREDENTIALS NOT "outcome.reason"=LOCKED_OUT NOT "outcome.reason"=PASSWORD_BASED_LOGIN_DISALLOWED NOT "outcome.reason"="GENERAL_NONSUCCESS" NOT "outcome.reason"="null" earliest=@d| eval time_hour = strftime(_time, "%H") | stats count(actor.alternateId) as Failure by time_hour|fillnull value=0 ] |eval Total=(Success+Failure) | eval Failure_Percent=round((Failure/Total)*100,4) | table time_hour, Success, Failure, Total, Failure_Percent

[FrankVl]

You can achieve that by timecharting the data, rather than doing a stats. Also: I think the overall search can be restructured a lot.

Try this:

index=auto_prod_okta eventType="user.session.start" ( outcome.result="SUCCESS" OR (outcome.result="FAILURE" NOT "outcome.reason"=INVALID_CREDENTIALS NOT "outcome.reason"=LOCKED_OUT NOT "outcome.reason"=PASSWORD_BASED_LOGIN_DISALLOWED NOT "outcome.reason"="GENERAL_NONSUCCESS" NOT "outcome.reason"="null") ) earliest=@d 
| timechart count(actor.alternateId) by outcome.result span=1h
| eval time_hour = strftime(_time, "%H")
| eval Total=(SUCCESS+FAILURE)
| eval Failure_Percent=round((FAILURE/Total)*100,4)
| table time_hour, SUCCESS, FAILURE, Total, Failure_Percent