Dashboards & Visualizations

How to show count as 0 for rangemap if a range does not exist

northcal_splunk
Engager

Hi,
I are trying to generate a search report using the follow query

rangemap field="value"
    "<$1"=0-1
    "$1-$2"=1-2
    "$2-$3"=2-3
    "$3-$5"=3-5
    |top limit=0 range 

If there is no value falls in range 2-3, the report shows as follow

range    count         percent
<$1        10           50
$1-$2      5            25
$3-$5      5            25

How could I make the report shows as

range    count         percent
<$1        10           50
$1-$2      5            25
$2-$3      0            0``
$3-$5      5            25

Thanks

Tags (1)
1 Solution

dwaddle
SplunkTrust
SplunkTrust

One way I solve this is with a lookup providing default (or sentinel) values. If you had a lookup file named ranges.csv that had in it:

range,count
<$1,0
$1-$2,0
$2-$3,0
$3,$5,0

Then you could change your search to something like:

|rangemap field="value"
   "<$1"=0-1
   "$1-$2"=1-2
   "$2-$3"=2-3
   "$3-$5"=3-5
|top limit=0 range
|inputlookup append=true ranges.csv
|stats max(count) by range

The inputlookup appends the zero-rows for your known ranges, and the stats command removes the zero-rows where they are not needed.

View solution in original post

dwaddle
SplunkTrust
SplunkTrust

One way I solve this is with a lookup providing default (or sentinel) values. If you had a lookup file named ranges.csv that had in it:

range,count
<$1,0
$1-$2,0
$2-$3,0
$3,$5,0

Then you could change your search to something like:

|rangemap field="value"
   "<$1"=0-1
   "$1-$2"=1-2
   "$2-$3"=2-3
   "$3-$5"=3-5
|top limit=0 range
|inputlookup append=true ranges.csv
|stats max(count) by range

The inputlookup appends the zero-rows for your known ranges, and the stats command removes the zero-rows where they are not needed.

martin_mueller
SplunkTrust
SplunkTrust

Additionally, a note about rangemap - in the search from your question, a $2 item will appear both in the 1-2 and the 2-3 category because the ranges are both inclusive. If that's undesired behaviour you can replace the rangemap with a case expression like so:

... | eval range = case(value < 1, "<$1", value < 2, "$1-$2", ...)

That way a $2 item will only be listed in $2-$3, the upper bounds of each range are treated as non-inclusive by the less-than rather than a less-than-or-equal used by rangemap.

The lookup suggested by @dwaddle is still required when using case instead of rangemap.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...