Splunk Enterprise Security

Change color table on treshold field

anissabnk
Path Finder

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

anissabnk_0-1743177694551.png

 

<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 "(?&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, 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>
<!--format type="color" field="AverageExecutionTime">
<colorPalette type="expression">
<mapping field="AverageExecutionTime"> if(AverageExecutionTime > Treshold, "#D94E17", "#55C169")
</mapping>
</colorPalette>
</format-->
<!-- Mise en couleur conditionnelle -->
<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$&amp;Script_Execution_Details=true&amp;earliest=$earliest$&amp;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 "(?&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</query>
<earliest>$earliest$</earliest>
<latest>$latest$</latest>
</search>
<option name="refresh.display">progressbar</option>
<format type="color" field="AverageExecutionTime">
<colorPalette type="expression">if(AverageExecutionTime &gt; Treshold,"#D94E17", "#55C169")</colorPalette>
</format>

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

0 Karma
1 Solution

marnall
Motivator

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>

redandgreen.png

View solution in original post

marnall
Motivator

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>

redandgreen.png

anissabnk
Path Finder

@marnall 

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

0 Karma

marnall
Motivator

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

anissabnk
Path Finder

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

0 Karma

anissabnk
Path Finder

@marnall, please

0 Karma

anissabnk
Path Finder

thank you so much, it works

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

What Is Splunk? Here’s What You Can Do with Splunk

Hey Splunk Community, we know you know Splunk. You likely leverage its unparalleled ability to ingest, index, ...

Level Up Your .conf25: Splunk Arcade Comes to Boston

With .conf25 right around the corner in Boston, there’s a lot to look forward to — inspiring keynotes, ...

Manual Instrumentation with Splunk Observability Cloud: How to Instrument Frontend ...

Although it might seem daunting, as we’ve seen in this series, manual instrumentation can be straightforward ...