How to set missing data values to zero?

[xvxt006]

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

[xvxt006]

I did not know about this. This is useful

[somesoni2 · Accepted solution]

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

[xvxt006]

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

[xvxt006]

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?

[xvxt006]

Thanks. This is more efficient.

[ppablo]

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