Splunk Search

Default value for `stats count` or `top`

New Member

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?

Tags (3)
0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Esteemed Legend

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

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

0 Karma

Builder

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!