Solved: Re: Change color table on treshold field

anissabnk · ‎03-28-2025

Hello, I'm having a problem with the colouring of a column in my table.

I need to colour the AverageExecutionTime column according to the value of Treshold.

If AverageExecutionTime > Treshold then the AverageExecutionTime column is coloured red.

If AverageExecutionTime < Treshold then the AverageExecutionTime column is coloured green.

I've tried lots of things but it doesn't work, the conidition isn't respected, and AverageExutionTime is always coloured green.
The first line should be in red

<row>
<panel>
<title>XRT Execution Dashboard</title>
<table>
<search>
<query>index="aws_app_corp-it_xrt" sourcetype="xrt_log" "OK/INFO - 1012550 - Total Calc Elapsed Time"
| rex field=source "(?<Datetime>\d{8}_\d{6})_usr@(?<Username>[\w\.]+)_ses@\d+_\d+_MAXL#(?<TemplateName>\d+)_apd@(?<ScriptName>[\w]+)_obj#(?<ObjectID>[^.]+)\.msh\.log"
| rex "Total Calc Elapsed Time\s*:\s*\[(?<calc_time>\d+\.\d+)\]\s*seconds"
| stats avg(calc_time) as AverageExecutionTime max(calc_time) as MaxExecutionTime by ScriptName, ObjectID, TemplateName
| eval AverageExecutionTime = round(AverageExecutionTime, 3)
|lookup script_tresholds ObjectID ScriptName MaxLTemplate as "TemplateName" OUTPUT Threshold AS "Treshold"
| table ScriptName, AverageExecutionTime, MaxExecutionTime, Treshold, ObjectID, TemplateName
|search $ScriptName$ $ObjectID$ $TemplateName$
|sort - AverageExecutionTime</query>
<earliest>$earliest$</earliest>
<latest>$latest$</latest>
</search>


<option name="count">100</option>
<option name="drilldown">cell</option>
<option name="refresh.display">progressbar</option>
<format type="color" field="Color">
<colorPalette type="map">{"High":"#D94E17", "Low":"#55C169"}</colorPalette>
</format>
<drilldown>
<condition field="ScriptName">
<link target="_blank">/app/search/dev_vwt_dashboards_uc31_details?ScriptName=$row.ScriptName$&Script_Execution_Details=true&earliest=$earliest$&latest=$latest$</link>
</condition>
</drilldown>
</table>
</panel>
</row>
<row>
<panel>
<title>TEST XRT Execution Dashboard</title>
<table>
<search>
<query>index="aws_app_corp-it_xrt" sourcetype="xrt_log" "OK/INFO - 1012550 - Total Calc Elapsed Time"
| rex field=source "(?<Datetime>\d{8}_\d{6})_usr@(?<Username>[\w\.]+)_ses@\d+_\d+_MAXL#(?<TemplateName>\d+)_apd@(?<ScriptName>[\w]+)_obj#(?<ObjectID>[^.]+)\.msh\.log"
| rex "Total Calc Elapsed Time\s*:\s*\[(?<calc_time>\d+\.\d+)\]\s*seconds"
| stats avg(calc_time) as AverageExecutionTime max(calc_time) as MaxExecutionTime by ScriptName, ObjectID, TemplateName
| eval AverageExecutionTime = round(AverageExecutionTime, 0)
| lookup script_tresholds ObjectID ScriptName MaxLTemplate as "TemplateName" OUTPUT Threshold AS "Treshold"
| eval colorCode = if(AverageExecutionTime > Treshold, "#D94E17", "#55C169")
| table ScriptName, AverageExecutionTime, MaxExecutionTime, Treshold, ObjectID, TemplateName, colorCode
| search $ScriptName$ $ObjectID$
| sort - AverageExecutionTime</query>
<earliest>$earliest$</earliest>
<latest>$latest$</latest>
</search>
<option name="refresh.display">progressbar</option>
<format type="color" field="AverageExecutionTime">
<colorPalette type="expression">if(AverageExecutionTime > Treshold,"#D94E17", "#55C169")</colorPalette>
</format>

</table>
</panel>
</row>

marnall · ‎03-28-2025

One way you could do this is by appending the color code to the values of AverageExecutionTime (making it a multi-value field), then reference that color code value in the colorPalette expression, then throw in some CSS to hide the color code in the multi-value field:

  <row>
    <panel>
      <html depends="$hidecsspanel$">
        <style>
        #ColoredTable table tbody td div.multivalue-subcell[data-mv-index="1"]{
        display: none;
        }
        </style>
      </html>
      <title>TEST XRT Execution Dashboard</title>
      <table id="ColoredTable">
        <search>
          <query>index="aws_app_corp-it_xrt" sourcetype="xrt_log" "OK/INFO - 1012550 - Total Calc Elapsed Time"
| rex field=source "(?&lt;Datetime&gt;\d{8}_\d{6})_usr@(?&lt;Username&gt;[\w\.]+)_ses@\d+_\d+_MAXL#(?&lt;TemplateName&gt;\d+)_apd@(?&lt;ScriptName&gt;[\w]+)_obj#(?&lt;ObjectID&gt;[^.]+)\.msh\.log"
| rex "Total Calc Elapsed Time\s*:\s*\[(?&lt;calc_time&gt;\d+\.\d+)\]\s*seconds"
| stats avg(calc_time) as AverageExecutionTime max(calc_time) as MaxExecutionTime by ScriptName, ObjectID, TemplateName
| eval AverageExecutionTime = round(AverageExecutionTime, 0)
| lookup script_tresholds ObjectID ScriptName MaxLTemplate as "TemplateName" OUTPUT Threshold AS "Treshold"
| eval colorCode = if(AverageExecutionTime > Treshold, "#D94E17", "#55C169")
| table ScriptName, AverageExecutionTime, MaxExecutionTime, Treshold, ObjectID, TemplateName, colorCode
| search $ScriptName$ $ObjectID$
| sort - AverageExecutionTime
| eval AverageExecutionTime = mvappend(AverageExecutionTime,colorCode)
| fields - colorCode</query>
          <earliest>$earliest$</earliest>
          <latest>$latest$</latest>
        </search>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="AverageExecutionTime">
        <colorPalette type="expression">mvindex(value,1)</colorPalette>
        </format>
      </table>
    </panel>
  </row>

View solution in original post

marnall · ‎03-28-2025

One way you could do this is by appending the color code to the values of AverageExecutionTime (making it a multi-value field), then reference that color code value in the colorPalette expression, then throw in some CSS to hide the color code in the multi-value field:

  <row>
    <panel>
      <html depends="$hidecsspanel$">
        <style>
        #ColoredTable table tbody td div.multivalue-subcell[data-mv-index="1"]{
        display: none;
        }
        </style>
      </html>
      <title>TEST XRT Execution Dashboard</title>
      <table id="ColoredTable">
        <search>
          <query>index="aws_app_corp-it_xrt" sourcetype="xrt_log" "OK/INFO - 1012550 - Total Calc Elapsed Time"
| rex field=source "(?&lt;Datetime&gt;\d{8}_\d{6})_usr@(?&lt;Username&gt;[\w\.]+)_ses@\d+_\d+_MAXL#(?&lt;TemplateName&gt;\d+)_apd@(?&lt;ScriptName&gt;[\w]+)_obj#(?&lt;ObjectID&gt;[^.]+)\.msh\.log"
| rex "Total Calc Elapsed Time\s*:\s*\[(?&lt;calc_time&gt;\d+\.\d+)\]\s*seconds"
| stats avg(calc_time) as AverageExecutionTime max(calc_time) as MaxExecutionTime by ScriptName, ObjectID, TemplateName
| eval AverageExecutionTime = round(AverageExecutionTime, 0)
| lookup script_tresholds ObjectID ScriptName MaxLTemplate as "TemplateName" OUTPUT Threshold AS "Treshold"
| eval colorCode = if(AverageExecutionTime > Treshold, "#D94E17", "#55C169")
| table ScriptName, AverageExecutionTime, MaxExecutionTime, Treshold, ObjectID, TemplateName, colorCode
| search $ScriptName$ $ObjectID$
| sort - AverageExecutionTime
| eval AverageExecutionTime = mvappend(AverageExecutionTime,colorCode)
| fields - colorCode</query>
          <earliest>$earliest$</earliest>
          <latest>$latest$</latest>
        </search>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="AverageExecutionTime">
        <colorPalette type="expression">mvindex(value,1)</colorPalette>
        </format>
      </table>
    </panel>
  </row>

anissabnk · ‎04-01-2025

@marnall

I would to had to do the th same for "MaxEcutionTime" field, can you help me please

marnall · ‎04-03-2025

You could do the same thing to the MaxExecutionTime by adding 2 more rows in the search:

...
| eval colorCode2 = if(MaxExecutionTime > Treshold, "#D94E17", "#55C169")
...
| eval MaxExecutionTime = mvappend(MaxExecutionTime,colorCode)
...

And another format section:

<format type="color" field="MaxExecutionTime">
<colorPalette type="expression">mvindex(value,1)</colorPalette>
</format>

So now it reads:

  <row>
    <panel>
      <html depends="$hidecsspanel$">
        <style>
        #ColoredTable table tbody td div.multivalue-subcell[data-mv-index="1"]{
        display: none;
        }
        </style>
      </html>
      <title>TEST XRT Execution Dashboard</title>
      <table id="ColoredTable">
        <search>
          <query>index="aws_app_corp-it_xrt" sourcetype="xrt_log" "OK/INFO - 1012550 - Total Calc Elapsed Time"
| rex field=source "(?&lt;Datetime&gt;\d{8}_\d{6})_usr@(?&lt;Username&gt;[\w\.]+)_ses@\d+_\d+_MAXL#(?&lt;TemplateName&gt;\d+)_apd@(?&lt;ScriptName&gt;[\w]+)_obj#(?&lt;ObjectID&gt;[^.]+)\.msh\.log"
| rex "Total Calc Elapsed Time\s*:\s*\[(?&lt;calc_time&gt;\d+\.\d+)\]\s*seconds"
| stats avg(calc_time) as AverageExecutionTime max(calc_time) as MaxExecutionTime by ScriptName, ObjectID, TemplateName
| eval AverageExecutionTime = round(AverageExecutionTime, 0)
| lookup script_tresholds ObjectID ScriptName MaxLTemplate as "TemplateName" OUTPUT Threshold AS "Treshold"
| eval colorCode = if(AverageExecutionTime > Treshold, "#D94E17", "#55C169")
| eval colorCode2 = if(MaxExecutionTime > Treshold, "#D94E17", "#55C169")
| table ScriptName, AverageExecutionTime, MaxExecutionTime, Treshold, ObjectID, TemplateName, colorCode
| search $ScriptName$ $ObjectID$
| sort - AverageExecutionTime
| eval AverageExecutionTime = mvappend(AverageExecutionTime,colorCode)
| eval MaxExecutionTime = mvappend(MaxExecutionTime,colorCode)
| fields - colorCode</query>
          <earliest>$earliest$</earliest>
          <latest>$latest$</latest>
        </search>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="AverageExecutionTime">
        <colorPalette type="expression">mvindex(value,1)</colorPalette>
        </format>
        <format type="color" field="MaxExecutionTime">
        <colorPalette type="expression">mvindex(value,1)</colorPalette>
        </format>
      </table>
    </panel>
  </row>

anissabnk · ‎04-01-2025

I would to had to do the th same for "MaxEcutionTime" field, can you help me please

anissabnk · ‎04-02-2025

@marnall, please

anissabnk · ‎04-01-2025

thank you so much, it works

Change color table on treshold field

administration

configuration

notable event

troubleshooting

.conf25 Registration is OPEN!

Detecting Cross-Channel Fraud with Splunk

Splunk at Cisco Live 2025: Learning, Innovation, and a Little Bit of Mr. Brightside

Are you a member of the Splunk Community?