Splunk Search

How to color the columns based on previous column value

Vignesh-107
Path Finder

 

MessagesNov 20Dec 20Jan 20Feb 20
Messge 00100
Messge 11311
Messge 211000
Messge 31000
Messge 49500
Messge 51100
Messge 61100
Messge 70100

 

Here i want to color the columns background based on previous column value

Nov 20 Dec 20

01(green bg)
110(red bg)
10(red bg)
95(red bg)
11(yellow bg)
11(yellow bg)
01(green bg)

 

Comparing condtions
if the current values is more than previous column value then it should have a green-background
if the current values is less than previous column value then it should have a red-background
if the current values is equal to previous column value then it should have a yellow-background 

Labels (1)
0 Karma

Vignesh-107
Path Finder

 This the query i am trying to use


| index=sample check=ERROR
| stats first(_time) as _time by SampleCode, Message
| rex mode=sed field=Message
"s/failed: //g
s/Order.ScenarioId/Order-ScenarioId/
s/([0-9])[\.|\_]([0-9])/\1\2/
s/[0-9]+//
s/(PREOK)?(ID)?([A-Z]+_[A-Z]+_?[A-Z]+)?(\b([A-Z]{1,}\b))?/\1\2\3/g
s/ / /"
| rex field=Message "^(?<ResponseMsg>[ a-zA-Z \-,]+)"
| timechart span=mon limit=0 count by ResponseMsg
| fields - _span, _spandays
| eval _time = strftime(_time,"%b %y")
| transpose 0 header_field=_time, column_name=ResponseMsg

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

A lot easier working from uncharted data

| index=sample check=ERROR
| stats first(_time) as _time by SampleCode, Message
| rex mode=sed field=Message
"s/failed: //g
s/Order.ScenarioId/Order-ScenarioId/
s/([0-9])[\.|\_]([0-9])/\1\2/
s/[0-9]+//
s/(PREOK)?(ID)?([A-Z]+_[A-Z]+_?[A-Z]+)?(\b([A-Z]{1,}\b))?/\1\2\3/g
s/ / /"
| rex field=Message "^(?<ResponseMsg>[ a-zA-Z \-,]+)"
| bin span=mon _time
| stats count by _time ResponseMsg
| sort ResponseMsg _time
| autoregress count as previousCount p=1
| autoregress ResponseMsg as previousMsg p=1
| eval sign=if(ResponseMsg=previousMsg,count-previousCount,null)
| eval sign = sign/abs(sign)
| fillnull value=0 sign
| eval sign=if(sign &lt; 0, "RED", if(sign &gt; 0, "GREEN", "YELLOW"))
| eval count=mvappend(count,sign)
| fields - sign previousCount previousMsg
| eval _time = strftime(_time,"%b %y")
| xyseries ResponseMsg _time count
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

 

  <row>
    <panel depends="$stayhidden$">
      <title>Colour based on previous</title>
      <html>
        <style>
          #tableCellColourWithoutJS table tbody td div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
    </panel>
    <panel>
      <table id="tableCellColourWithoutJS">
        <title>Colour Cell by Previous</title>
        <search>
          <query>| makeresults | eval _raw="Messages	Nov_20	Dec_20	Jan_21	Feb_21
Messge 0	0       1       0     0
Messge 1	1       3       1     1
Messge 2	11      0       0     0
Messge 3	1       0       0     0
Messge 4	9       5       0     0
Messge 5	1       1       0     0
Messge 6	1       1       0     0
Messge 7	0       1       0     0"
| multikv forceheader=1
| fields - _raw _time linecount
| fields - _mkv*
| transpose 0 header_field=Messages column_name=Month
| eval Month=strptime(Month . "_01", "%b_%y_%d")
| sort - Month
| autoregress Month as next p=1
| eval months=mvappend(Month, next)
| fields - next
| mvexpand months
| stats list(*) as * by months
| where months != "months"
| foreach *
  [ eval sign=(tonumber(mvindex('&lt;&lt;FIELD&gt;&gt;',0)) - tonumber(mvindex('&lt;&lt;FIELD&gt;&gt;', 1)))
  | eval sign = sign / abs(sign)
  | fillnull value=0 sign
  | eval sign=if(sign &lt; 0, "RED", if(sign &gt; 0, "GREEN", "YELLOW"))
  | eval &lt;&lt;FIELD&gt;&gt;=mvappend(mvindex('&lt;&lt;FIELD&gt;&gt;', 0),sign)
  | fields - sign ]
| fields - months
| eval Month=mvindex(Month,0)
| sort Month
| eval Month=strftime(Month, "%b %y")
| transpose 0 header_field=Month column_name=Messages</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color">
          <colorPalette type="expression">case (match(value,"RED"), "#ff0000",match(value,"YELLOW"), "#ffff00",match(value,"GREEN"),"#00ff00",true(),"#ffffff")</colorPalette>
        </format>
      </table>
    </panel>
  </row>

 

The hidden panel sets up the style to hide the second multi-value on each row (which contains the required colour).

The first part of the query sets up your example data - I assumed you meant Jan and Feb 21 not 20

By parsing the month to epoch time format it can then be used to sort

Getting the next month allows it to be "joined" with the previous month with the stats command so each month has the previous months value for each message

For each field, evaluate if the value for the message is greater or less or equal to previous month's value and set colour accordingly

Sort by month again, then format to shown date format

Finally, convert back to desired table layout

Set colour palette according to value of (hidden) field

ITWhisperer_0-1612694217279.png

 

Vignesh-107
Path Finder

Its working fine with the'| makeresults' but the column with months are dynamic based on the time range we select . Is it possible to apply the same 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The short answer to your question is yes it is possible.

Everything before the first transpose is to set up the data. It looks like you have got the data from a chart command, so the first transpose is to flip the data so that the rows are based on the dates. I suspect your data may already be available in this format or something close to it so perhaps this part can be changed by removing or at least moving your chart command until after the autoregress and determining whether the value is increasing or decreasing. Without seeing your actual query, it is difficult to tell you how to refactor it though. 

The essential elements of my suggestion that you would probably need to use are the converting the date to epoch format so it can be sorted (although you may already have the date in this format), the autoregress to get the previous month, the result in the second element of the multi-value fields, the colouring based on that element and the hiding of that result using the stylesheet.

0 Karma

Vignesh-107
Path Finder

Yes i have tried it but i am not getting the expected thing i am getting most of the columns as empty

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Can you share what you have tried to see if we can spot what might be wrong?

0 Karma

Vignesh-107
Path Finder

| index=sample check=ERROR
| stats first(_time) as _time by SampleCode, Message
| rex mode=sed field=Message
"s/failed: //g
s/Order.ScenarioId/Order-ScenarioId/
s/([0-9])[\.|\_]([0-9])/\1\2/
s/[0-9]+//
s/(PREOK)?(ID)?([A-Z]+_[A-Z]+_?[A-Z]+)?(\b([A-Z]{1,}\b))?/\1\2\3/g
s/ / /"
| rex field=ComplianceAcknowledgementMessage "^(?<ResponseMsg>[ a-zA-Z \-,]+)"
| bin span=mon _time
| stats count by _time ResponseMsg
| sort ResponseMsg _time
| autoregress count as previousCount p=1
| autoregress ResponseMsg as previousMsg p=1
| eval sign=if(ResponseMsg=previousMsg,count-previousCount,null)
| eval sign = sign/abs(sign)
| fillnull value=0
| eval sign=if(sign < 0, "RED", if(sign > 0, "GREEN", "YELLOW"))
| eval count=mvappend(count,sign)
| fields - sign previousCount previousMsg
| eval _time = strftime(_time,"%b %y")
| xyseries ResponseMsg _time count

bg-color.PNG

 

you can see this the output i am getting and the its not coming in correct order Jan21 and Feb 21 should be coming at last

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The issue is the eval _time to a string before the xyseries so the order is not by epoch time.  However, if you remove this, the columns in the xyseries become numbers (the epoch time). To resolve this without disrupting the order is to use transpose twice, renaming the column values in between.

| index=sample check=ERROR
| stats first(_time) as _time by SampleCode, Message
| rex mode=sed field=Message
"s/failed: //g
s/Order.ScenarioId/Order-ScenarioId/
s/([0-9])[\.|\_]([0-9])/\1\2/
s/[0-9]+//
s/(PREOK)?(ID)?([A-Z]+_[A-Z]+_?[A-Z]+)?(\b([A-Z]{1,}\b))?/\1\2\3/g
s/ / /"
| rex field=ComplianceAcknowledgementMessage "^(?<ResponseMsg>[ a-zA-Z \-,]+)"
| bin span=mon _time
| stats count by _time ResponseMsg
| sort ResponseMsg _time
| autoregress count as previousCount p=1
| autoregress ResponseMsg as previousMsg p=1
| eval sign=if(ResponseMsg=previousMsg,count-previousCount,null)
| eval sign = sign/abs(sign)
| fillnull value=0
| eval sign=if(sign < 0, "RED", if(sign > 0, "GREEN", "YELLOW"))
| eval count=mvappend(count,sign)
| fields - sign previousCount previousMsg
| xyseries ResponseMsg _time count
| transpose 0 header_field=ResponseMsg
| eval column=strftime(column,"%Y-%m")
| transpose 0 header_field=column column_name=ResponseMsg
0 Karma

Vignesh-107
Path Finder

Tried with the query you gave but its not working as expected, When i check for last 30 days of data we are seeing most empty for Feb month and colors need to be applied based on previous months data. But we are seeing the yellow colors Bg-color on March month.

 

30days.PNG

 

This the Feb and March data

Feb and Mar.PNG

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Can you share your current query?

0 Karma

Vignesh-107
Path Finder

The same query that you gave above

| index=sample check=ERROR
| stats first(_time) as _time by SampleCode, Message
| rex mode=sed field=Message
"s/failed: //g
s/Order.ScenarioId/Order-ScenarioId/
s/([0-9])[\.|\_]([0-9])/\1\2/
s/[0-9]+//
s/(PREOK)?(ID)?([A-Z]+_[A-Z]+_?[A-Z]+)?(\b([A-Z]{1,}\b))?/\1\2\3/g
s/ / /"
| rex field=ComplianceAcknowledgementMessage "^(?<ResponseMsg>[ a-zA-Z \-,]+)"
| bin span=mon _time
| stats count by _time ResponseMsg
| sort ResponseMsg _time
| autoregress count as previousCount p=1
| autoregress ResponseMsg as previousMsg p=1
| eval sign=if(ResponseMsg=previousMsg,count-previousCount,null)
| eval sign = sign/abs(sign)
| fillnull value=0
| eval sign=if(sign < 0, "RED", if(sign > 0, "GREEN", "YELLOW"))
| eval count=mvappend(count,sign)
| fields - sign previousCount previousMsg
| xyseries ResponseMsg _time count
| transpose 0 header_field=ResponseMsg
| eval column=strftime(column,"%Y-%m")
| transpose 0 header_field=column column_name=ResponseMsg

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

And what query did you use to get the Feb and March data you posted?

0 Karma

Vignesh-107
Path Finder

The query i gave above bring the result which is check for last 30 days of data

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...