topic Re: Referencing SPL variable in simple xml in Dashboards & Visualizations

Referencing SPL variable in simple xml

splunker011 — Sun, 23 Feb 2025 19:12:06 GMT

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 🙂

Re: Referencing SPL variable in simple xml

Cievo — Sun, 23 Feb 2025 20:52:42 GMT

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>


<format type="color" field="Free Space (%)">
<rangeMap>
<range min="0" max="20" color="#FF0000"/> 
<range min="20" max="50" color="#FFA500"/> 
<range min="50" max="100" color="#00FF00"/> 
</rangeMap>
</format>
</table>

Have a nice day,

Re: Referencing SPL variable in simple xml

ITWhisperer — Mon, 24 Feb 2025 23:47:26 GMT

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>

Re: Referencing SPL variable in simple xml

splunker011 — Mon, 24 Feb 2025 19:51:28 GMT

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"

Re: Referencing SPL variable in simple xml

splunker011 — Mon, 24 Feb 2025 20:10:34 GMT

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)

Re: Referencing SPL variable in simple xml

ITWhisperer — Mon, 24 Feb 2025 23:48:30 GMT

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

Re: Referencing SPL variable in simple xml

splunker011 — Tue, 25 Feb 2025 12:39:41 GMT

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?