Use of fillnull displays wrong color in 'single value'

[Mike6960]

I am using | fillnull totalCount in my search so I get an 0 when there is no result.
The color range I use is from min to 0 is green, from 0 to max is red.
Somehow the '0' is still showing red. Is there any way to change this?

[DavidHourani · Accepted solution]

Hi @Mike6960,

Make sure you have something like this for your colors in xml :

<option name="rangeColors">["0x65a637","0xd93f3c"]</option>
<option name="rangeValues">[0.99]</option>

Also since this makes 0-0.99 green you can use 0.1 instead for fillnull if 0 is still not working : ...|fillnull value=0.1 totalCount or force to zero just in case : ...|fillnull value=0 totalCount

Cheers,
David

[Mike6960]

I have the same in my xml en tried your suggestions but it does not work

[DavidHourani]

could you please post the xml for your single value view ?

[Mike6960]

@DavidHourani , i got it working. I had the fillnull not at the end. But I have another search where te fillnull does not work at all. Maybe you see why?

index=captiva
|chart count by message.messageid
| search count < 2
|stats sum(count)
| fillnull count value=0

[DavidHourani]

hahah, well replace that one then with this :

index=captiva 
|chart count by message.messageid 
| search count < 2 
|stats sum(count)
| append [ | makeresults | eval count=0 | table count ] 
| head 1

if there are no values, sum will not give a null field, it will actually give nothing at all, so you need to create an extra fake field that will only show in case there are no results 🙂

[Mike6960]

@DavidHourani Thanks, I get a 0 value now. I edited the xml like you suggested in your first answer and also this works great!. Thank You very much. I dont think i will ever accomplish using splunk without asking for help ....

[DavidHourani]

This should do the trick :

  <single>
        <search>
          <query>index=captiva 
| chart count by message.messageid 
| where count < 2 
| stats sum(count) 
| append 
    [| makeresults 
    | eval count=0 
    | table count ] 
| head 1
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0</option>
       <option name="rangeColors">["0x65a637","0xd93f3c"]</option>
        <option name="rangeValues">[0]</option>
        <option name="refresh.display">progressbar</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">1</option>
      </single>

[DavidHourani]

fixed xml formatting you can try the above now.

[Mike6960]

@DavidHourani , it worked already with your first suggestion. I only thought I needed to use fillenull but i understand that the append does the same trick?

My xml is now

<single>
        <title></title>
        <search>
          <query>index=captiva 
 |chart count by message.messageid 
 | search count &lt; 2 
 |stats sum(count)
 | append [ | makeresults | eval count=0 | table count ] 
 | head 1</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">block</option>
        <option name="drilldown">all</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x65a637","0x65a637","0xd93f3c"]</option>
        <option name="rangeValues">[0.99]</option>
        <option name="refresh.display">progressbar</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="underLabel">niet aangekomen in ESB</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">1</option>

[DavidHourani]

yes, it does, fillnull will work when the column is already there and you want to fill null values whereas append will work when there are no columns and no results typically after you run a stats command such as sum or count and there is nothing to sum/count.

[Mike6960]

@DavidHourani . I was to soon with my cheering...It still displays a red color when the value is 0.

[DavidHourani]

try the xml I sent you above, should be green

[Mike6960]

I am going mad, its not working. This is the xml I have now:

<single>
                 <search>
           <query>index=captiva 
 | chart count by message.messageid 
 | where count &lt; 2
 | stats sum(count) 
 | append 
     [| makeresults 
     | eval count=0 
     | table count ] 
 | head 1
</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="colorBy">value</option>
         <option name="colorMode">block</option>
         <option name="drilldown">none</option>
         <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x65a637","0xd93f3c"]</option>
         <option name="rangeValues">[0]</option>
         <option name="refresh.display">progressbar</option>
         <option name="showSparkline">1</option>
         <option name="showTrendIndicator">1</option>
         <option name="trellis.enabled">0</option>
         <option name="trellis.scales.shared">1</option>
         <option name="trellis.size">medium</option>
         <option name="trendColorInterpretation">standard</option>
         <option name="trendDisplayMode">absolute</option>
         <option name="unitPosition">after</option>
         <option name="useColors">1</option>
         <option name="useThousandSeparators">1</option>

      </single>

[DavidHourani]

I just tried it with just this :

| makeresults | eval count=0 | table count

And it's actually green for the 0 and red when it's a one.

[Mike6960]

When I try only the makeresults then it does work, strange...

[DavidHourani]

this is really weird, I tried it as you said and I was getting red as well. This fixed it for me:

   index=captiva 
   | chart count by message.messageid 
   | where count < 2
   | stats sum(count) as result
   | append
      [| makeresults 
      | eval result="0" 
      | table result ] 
 | head 1
 |fields result

[Mike6960]

absolutely no idea why this works but it does 🙂
The append command only kicks in when there are no results I guess?

[DavidHourani]

yeah, append only kicks in when results are empty, and what you said about tablesort of fixing it, it's the same for the fields I added here, apparently the sum(count) was breaking the results, when I removed it, everything was working and when I added it the 0 became red.

[DavidHourani]

Try this one, it works for me and I had the same thing you were describing.

[Mike6960]

@DavidHourani , strange thing is also when I do ....eval count= 1 , the value keeps being 0