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

[northcal_splunk]

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

[dwaddle · Accepted solution]

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]

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.