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
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.
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.
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.