topic Re: How to color the columns based on previous column value in Splunk Search

How to color the columns based on previous column value

Vignesh-107 — Sat, 06 Feb 2021 04:52:02 GMT

Messages	Nov 20	Dec 20	Jan 20	Feb 20
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

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

Nov 20 Dec 20

0	1(green bg)
11	0(red bg)
1	0(red bg)
9	5(red bg)
1	1(yellow bg)
1	1(yellow bg)
0	1(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

Re: How to color the columns based on previous column value

ITWhisperer — Sun, 07 Feb 2021 10:37:07 GMT

<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('<<FIELD>>',0)) - tonumber(mvindex('<<FIELD>>', 1))) | eval sign = sign / abs(sign) | fillnull value=0 sign | eval sign=if(sign < 0, "RED", if(sign > 0, "GREEN", "YELLOW")) | eval <<FIELD>>=mvappend(mvindex('<<FIELD>>', 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

Re: How to color the columns based on previous column value

Vignesh-107 — Tue, 09 Feb 2021 10:10:25 GMT

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

Re: How to color the columns based on previous column value

ITWhisperer — Tue, 09 Feb 2021 10:26:02 GMT

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.

Re: How to color the columns based on previous column value

Vignesh-107 — Tue, 09 Feb 2021 16:36:56 GMT

This the query i am trying to use

Re: How to color the columns based on previous column value

ITWhisperer — Tue, 09 Feb 2021 17:45:08 GMT

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

Re: How to color the columns based on previous column value

Vignesh-107 — Wed, 24 Mar 2021 10:36:12 GMT

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

Re: How to color the columns based on previous column value

ITWhisperer — Wed, 24 Mar 2021 11:04:41 GMT

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

Re: How to color the columns based on previous column value

Vignesh-107 — Wed, 24 Mar 2021 11:20:28 GMT

| 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

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

Re: How to color the columns based on previous column value

ITWhisperer — Wed, 24 Mar 2021 14:03:26 GMT

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

Re: How to color the columns based on previous column value

Vignesh-107 — Fri, 26 Mar 2021 13:43:11 GMT

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.

This the Feb and March data

Re: How to color the columns based on previous column value

ITWhisperer — Fri, 26 Mar 2021 14:17:12 GMT

Can you share your current query?

Re: How to color the columns based on previous column value

Vignesh-107 — Fri, 26 Mar 2021 14:55:45 GMT

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

Re: How to color the columns based on previous column value

ITWhisperer — Fri, 26 Mar 2021 15:04:49 GMT

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

Re: How to color the columns based on previous column value

Vignesh-107 — Sat, 27 Mar 2021 08:31:20 GMT

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