topic Default value for `stats count` or `top` in Splunk Search

Default value for `stats count` or `top`

shulmaniel — Mon, 26 Aug 2019 18:05:31 GMT

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?

Re: Default value for `stats count` or `top`

solarboyz1 — Wed, 30 Sep 2020 01:55:49 GMT

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

Re: Default value for `stats count` or `top`

woodcock — Mon, 26 Aug 2019 18:26:59 GMT

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

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

Re: Default value for `stats count` or `top`

mayurr98 — Mon, 26 Aug 2019 18:30:30 GMT

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