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!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...