Splunk Search

Default value for `stats count` or `top`

shulmaniel
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

mayurr98
Super Champion

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

0 Karma

woodcock
Esteemed Legend

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

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

0 Karma

solarboyz1
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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...