Splunk Search

Use of fillnull displays wrong color in 'single value'

Mike6960
Path Finder

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?

0 Karma
1 Solution

DavidHourani
Super Champion

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

View solution in original post

DavidHourani
Super Champion

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
Path Finder

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

0 Karma

DavidHourani
Super Champion

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

0 Karma

Mike6960
Path Finder

@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

0 Karma

DavidHourani
Super Champion

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 🙂

0 Karma

Mike6960
Path Finder

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

0 Karma

DavidHourani
Super Champion

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>
0 Karma

DavidHourani
Super Champion

fixed xml formatting you can try the above now.

0 Karma

Mike6960
Path Finder

@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>
0 Karma

DavidHourani
Super Champion

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.

0 Karma

Mike6960
Path Finder

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

0 Karma

DavidHourani
Super Champion

try the xml I sent you above, should be green

0 Karma

Mike6960
Path Finder

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>
0 Karma

DavidHourani
Super Champion

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.

0 Karma

Mike6960
Path Finder

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

0 Karma

DavidHourani
Super Champion

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
0 Karma

Mike6960
Path Finder

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

0 Karma

DavidHourani
Super Champion

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.

0 Karma

DavidHourani
Super Champion

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

0 Karma

Mike6960
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...