Referencing SPL variable in simple xml

[splunker011]

Hi I am currently trying to reference an SPL variable in simple xml for a table panel in a dashboard. I would like each field value for the "Free space (%)" field to change depending on what the "color" variable in the query evaluates to(green or red).

I found one method online which mentions creating a token in a set tag and then referencing in the colorpallete tag but I haven't been able to get it working:

<table>
  <title>TABLESPACE_FREESPACE</title>
  <search>
    <query>
      index="database" source="tables"
      | eval BYTES_FREE = replace(BYTES_FREE, ",", "")
      | eval BYTES_USED = replace(BYTES_USED, ",", "")
      | eval GB_USED = BYTES_USED / (1024 * 1024 * 1024)
      | eval GB_FREE = BYTES_FREE / (1024 * 1024 * 1024)
      | eval GB_USED = floor(GB_USED * 100) / 100
      | eval GB_FREE = floor(GB_FREE * 100) / 100
      | eval CALCULATED_PERCENT_FREE = (GB_FREE / (GB_USED + GB_FREE)) * 100
      | eval CALCULATED_PERCENT_FREE = floor(CALCULATED_PERCENT_FREE * 10) / 10
      | eval color = if(CALCULATED_PERCENT_FREE >= PERCENT_FREE, "#00FF00", "#FF0000")
      | rename TABLESPACE_NAME as "Tablespace", GB_USED as "Used Space (Gb)", GB_FREE as "Free Space (Gb)", PERCENT_FREE as "Free Space (%)"
      | table "Tablespace" "Used Space (Gb)" "Free Space (Gb)" "Free Space (%)"
    </query>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
    <done>
      <set token="color">$result.color$</set>
    </done>
  </search>
  <option name="count">21</option>
  <option name="drilldown">none</option>
  <option name="wrap">false</option>
  <format type="color" field="Free Space (%)">
    <colorPalette type="expression">$color$</colorPalette>
  </format>
</table>

Any help would be appreciated, thanks 🙂

[ITWhisperer]

color isn't listed in the final table command of your search so it doesn't appear in the final result set.

If you want a field that isn't displayed in your table, start the field name with and underscore e.g. _color and reference that in the done handler.

Try something like this

<table>
  <title>TABLESPACE_FREESPACE</title>
  <search>
    <query>
      index="database" source="tables"
      | eval BYTES_FREE = replace(BYTES_FREE, ",", "")
      | eval BYTES_USED = replace(BYTES_USED, ",", "")
      | eval GB_USED = BYTES_USED / (1024 * 1024 * 1024)
      | eval GB_FREE = BYTES_FREE / (1024 * 1024 * 1024)
      | eval GB_USED = floor(GB_USED * 100) / 100
      | eval GB_FREE = floor(GB_FREE * 100) / 100
      | eval CALCULATED_PERCENT_FREE = (GB_FREE / (GB_USED + GB_FREE)) * 100
      | eval CALCULATED_PERCENT_FREE = floor(CALCULATED_PERCENT_FREE * 10) / 10
      | eval _color = if(CALCULATED_PERCENT_FREE >= PERCENT_FREE, "#00FF00", "#FF0000")
      | rename TABLESPACE_NAME as "Tablespace", GB_USED as "Used Space (Gb)", GB_FREE as "Free Space (Gb)", PERCENT_FREE as "Free Space (%)"
      | table "Tablespace" "Used Space (Gb)" "Free Space (Gb)" "Free Space (%)" _color
    </query>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
    <done>
      <set token="color">$result._color$</set>
    </done>
  </search>
  <option name="count">21</option>
  <option name="drilldown">none</option>
  <option name="wrap">false</option>
  <format type="color" field="Free Space (%)">
    <colorPalette type="expression">$color|s$</colorPalette>
  </format>
</table>

[splunker011]

Hi ITWhisperer,

Its still not coloring the cells unfortunately. I've tried your suggestion and also modified it to try to get it to work. I noticed that you combined the last two fields to create a single column: 

| table ... "Free Space (%) _color"

Was that intentional? I tried making them separate fields in the table command but that didn't work either:

| table ... "Free Space (%)" "_color"

Also, is the method by which I'm selecting the "color/_color" variable in the done handler and then referencing it in the <colorpallete> tag correct? 

When I hardcode a value into the <colorpallete> tag it works fine but I need it to store the value of "color/_color"

[ITWhisperer]

You are right, it should have been

| table ... "Free Space (%)" "_color"

 You also need to make sure the token is quoted

<colorPalette type="expression">$color|s$</colorPalette>

 See updated solution

[splunker011]

Sweet, its storing the color in $color|s$ and displaying it in the "Free Space (%)" column cells now which is what I need.

But it only evaluates for the first color value in the column not for each individual field value. So the whole column is a single color.

Is there a way of making it evaluate for each field value?

[Cievo]

Hello @splunker011 

Try use this code:

<table>
<title>TABLESPACE_FREESPACE</title>
<search>
<query>
index="database" source="tables"
| eval BYTES_FREE = replace(BYTES_FREE, ",", "")
| eval BYTES_USED = replace(BYTES_USED, ",", "")
| eval GB_USED = BYTES_USED / (1024 * 1024 * 1024)
| eval GB_FREE = BYTES_FREE / (1024 * 1024 * 1024)
| eval GB_USED = floor(GB_USED * 100) / 100
| eval GB_FREE = floor(GB_FREE * 100) / 100
| eval CALCULATED_PERCENT_FREE = (GB_FREE / (GB_USED + GB_FREE)) * 100
| eval CALCULATED_PERCENT_FREE = floor(CALCULATED_PERCENT_FREE * 10) / 10
| rename TABLESPACE_NAME as "Tablespace", GB_USED as "Used Space (Gb)", GB_FREE as "Free Space (Gb)", PERCENT_FREE as "Free Space (%)"
| table "Tablespace" "Used Space (Gb)" "Free Space (Gb)" "Free Space (%)"
</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
</search>
<option name="count">21</option>
<option name="drilldown">none</option>
<option name="wrap">false</option>

<!-- Use rangeMap to define colors based on "Free Space (%)" -->
<format type="color" field="Free Space (%)">
<rangeMap>
<range min="0" max="20" color="#FF0000"/> <!-- Red for low free space -->
<range min="20" max="50" color="#FFA500"/> <!-- Orange for medium -->
<range min="50" max="100" color="#00FF00"/> <!-- Green for high free space -->
</rangeMap>
</format>
</table>

Have a nice day,

[splunker011]

Hi Cievo,

Is it possible to have a different threshold for each field value? "PERCENT_FREE" in the query is actually a static value/threshold which is not calculated from "GB_USED" and "GB_FREE". "CALCULATED_PERCENT_FREE" is the calculated value. 

So I would like each cell in the "Free Space (%)"  column to change color according to:

If  CALCULATED_PERCENT_FREE >=  PERCENT_FREE ->  Cell goes green

If CALCULATED_PERCENT_FREE =  PERCENT_FREE - 1  -> Cell goes amber
If CALCULATED_PERCENT_FREE < PERCENT_FREE - 1 -> Cell goes red

(The original query did not contain the amber clause but I have planned to put it in eventually when I get the functionality working)