Splunk Enterprise Security
cancel
Turn on suggestions
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

Change color table on treshold field

anissabnk
Path Finder
2 weeks ago

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
Reply
1 Solution
Solved! Jump to solution
Solution

marnall
Motivator
2 weeks ago

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

Reply
Solved! Jump to solution
Solution

marnall
Motivator
2 weeks ago

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

 

Reply
Solved! Jump to solution

anissabnk
Path Finder
a week ago

@marnall 

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

0 Karma
Reply
Solved! Jump to solution

marnall
Motivator
Thursday

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
Reply
Solved! Jump to solution

anissabnk
Path Finder
a week ago

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

0 Karma
Reply
Solved! Jump to solution

anissabnk
Path Finder
Wednesday

@marnall, please

0 Karma
Reply
Solved! Jump to solution

anissabnk
Path Finder
a week ago

thank you so much, it works

0 Karma
Reply
Get Updates on the Splunk Community!

Learn Splunk Insider Insights, Do More With Gen AI, & Find 20+ New Use Cases You Can ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Buttercup Games: Further Dashboarding Techniques (Part 7)

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

What are Community Office Hours? Community Office Hours is an interactive 60-minute Zoom series where ...
Top