Default value for `stats count` or `top`

[shulmaniel]

This should be a trivial thing, but I'm having a hard time figuring out how to do it in Splunk: how do I use a default value for a key?

Here's an example: suppose I'm interested in HTTP status codes, so I do something like

index=whatever "HTTP/1.1"
| top status_code

If there are no 5xx errors, I'd like this to return "0%"; otherwise, return the % of 5xx errors.

This should be trivial no?

In SQL, I guess the way I'd do this is to have some table on the left side of a join, containing every possible HTTP status code. Then LEFT OUTER JOIN to a table that generates actual percentages by error code, and use COALESCE() to return a zero if there's nothing on the right side corresponding to that status code. What's the Splunk equivalent?

[mayurr98]

This may help you to convert your sql query to spl.
https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/SQLtoSplunk

[woodcock]

This is the Sentinel Search problem discussed (with solution) here:

https://conf.splunk.com/session/2015/conf2015-LookupTalk.pdf

[solarboyz1]

You can do the same in Splunk by creating a lookup table that contains all the HTTP code you are interested in.

index=whatever "HTTP/1.1"
| stats count by status_code
| inputlookup YourLookupFile
| stats sum(count) as count by status_code
| fillnull value=0 count

The end results, will be a list of all status_codes with their counts, code with no count will show 0