topic Re: How to set missing data values to zero? in Splunk Search

How to set missing data values to zero?

xvxt006 — Mon, 28 Sep 2020 17:16:19 GMT

Hi,

I want to track good requests (http=200) vs bad requests (http>399)and i have used the below query. But sometimes requests don't have bad requests then the column value is empty. So my formula is not working as it cannot copy empty value. So I am not getting an percentages. is this the right way to do this? i want track good and bad requests by uri as shown below.

status=200 | rex field=uri_path "/(?(?:[^/]))" | stats count as GoodRequests, dc(sid) as GoodSessions by uri_path | join type=outer uri_path [search status>399 | rex field=uri_path "/(?(?:[^/]))" | stats count as Failures, dc(sid) as FailedSessions by uri_path]| eval TotalRequests= (GoodRequests+Failures)| eval TotalSessions=(GoodSessions+FailedSessions) | eval GoodRequestsPerc = round((GoodRequests/TotalRequests)*100,2) | eval GoodSessionsPerc = round((GoodSessions /TotalSessions)*100,2) | eval FailuresPerc = round((Failures/TotalRequests)*100,2) | eval FailureSessionsPerc = round((FailedSessions/TotalSessions)*100,2) | sort - Failures

In this below example you can see only failures has data.

uri_path GoodRequests GoodSessions FailedSessions FailureSessionsPerc Failures FailuresPerc GoodRequestsPerc GoodSessionsPerc TotalRequests TotalSessions
rest 3 8

Re: How to set missing data values to zero?

ppablo — Tue, 05 Aug 2014 17:07:04 GMT

Hi @xvxt006

I'm very much a search command apprentice in training so not sure if this will be super helpful, but have you tried the fillnull command? http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Fillnull

Re: How to set missing data values to zero?

somesoni2 — Tue, 05 Aug 2014 17:10:26 GMT

You can use fillnull command before "| eval TotalRequests=" (after join) as @Patrick suggested.

You can also try this alternative approach (no joins, should perform better as well).

status=200 OR status>399  | rex field=uri_path "/(?<uri_path>(?:[^/]*))" | eval requestType=if(status=200,"Good","Bad") 
| chart count as requests dc(side) as sessions over uri_path by requestType 
| rename "requests: Good" as GoodRequests ,"requests: Bad" as Failures , "sessions: Good" as GoodSessions , "sessions: Bad" as FailedSessions  
| eval TotalRequests= (GoodRequests+Failures)| eval TotalSessions=(GoodSessions+FailedSessions) 
| eval GoodRequestsPerc = round((GoodRequests/TotalRequests)*100,2) | eval GoodSessionsPerc = round((GoodSessions /TotalSessions)*100,2) 
| eval FailuresPerc = round((Failures/TotalRequests)*100,2) | eval FailureSessionsPerc = round((FailedSessions/TotalSessions)*100,2) | sort - Failures

Re: How to set missing data values to zero?

xvxt006 — Tue, 05 Aug 2014 19:16:50 GMT

Thanks. This is more efficient.

Re: How to set missing data values to zero?

xvxt006 — Tue, 05 Aug 2014 19:17:16 GMT

I did not know about this. This is useful

Re: How to set missing data values to zero?

xvxt006 — Wed, 13 Aug 2014 02:54:37 GMT

Hi, once i get the stats table, if i drill down, i am not getting intended results. i have tried drill down on row and cell. i guess this is the limitation of how it adds the row at the end of the query and when i drill down 2nd time it loses the context. any suggestions?

Re: How to set missing data values to zero?

xvxt006 — Wed, 13 Aug 2014 03:19:19 GMT

Actually i am able to see events in verbose mode. Let me know if there is any other way to view events.