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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...