Splunk Search

How to get the values for time_hour?

abhi04
Communicator

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

0 Karma

FrankVl
Ultra Champion

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
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...