Color the Table row Cell based on Other Row Cell value

[askkawalkar]

Hi All,
I would like to achieve attached kind of color coding in splunk table.
Requirement :

In a particular column field

IF Row 1="" and Row 2="" and Row 3="" then
        Put background color of all 3 row cells to white.
ELSE IF Row 1!="" and Row 2="" and Row 3="" then           // Row 1 cell contains value
        Put background color of all 3 row cells to Yellow.
ELSE IF Row 1!="" and Row 2!="" and Row 3!="" then          // All cell contains value 
        Put Row 1 and Row 2 background color to green and Row 3 cell color to Blue

Please refer attached image for better understanding of requirement.

[niketn · Accepted solution]

@askkawalkar your Table Cell Color requirement seems complex as it has dependency with other rows and columns both at the same time. If you have grasped how @kamlesh_vaghela 's Table Cell Coloring JS works here is how you can modify your query for things to work:

Step 1: Following Creates Dummy Data as per your question. You should replace Step 1 code with your existing search. PS: You should also let us know your current query as an additional transpose (which is expensive command) in Step 3 can be avoided.

| makeresults 
| eval data="row1,randomevalue1,randomvalue2, ,randomvalue4;row2,contains, , ,contains;row3,pqr, , ,contains;row4, ,randomvalue3,randomvalue5,randomvalue6;row5, ,contains,contains, ;row6, ,contains,pqr, ;" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="," 
| eval Header=mvindex(data,0),field1=mvindex(data,1),field2=mvindex(data,2),field3=mvindex(data,3),field4=mvindex(data,4) 
| foreach field* 
    [| eval <<FIELD>>=trim(<<FIELD>>)] 
| table Header field* 

Step 2: Following section creates groups of three rows as 1, 2, 3. Also gives each group an id as minimum event number of that group (which is calculated using streamstats and followed by filldown)

| streamstats count as event_no 
| sort 0 event_no 
| eval group_row_no=if(event_no%3=0,3,event_no%3) 
| eval group_no=case(group_row_no=1,event_no) 
| filldown group_no 
| eval Header="group".group_no."_group_row_no".group_row_no 
| table Header field* 

Step 3: Transpose is used to convert Row to Column as foreach template needs to be applied for Events in each group of Rows.

| transpose 0 header_field="Header" column_name="Header" 
| foreach "group*_group_row_no1" 
    [| eval "<<FIELD>>"=case(<<FIELD>>=="" AND group<<MATCHSTR>>_group_row_no2=="" AND 'group<<MATCHSTR>>_group_row_no3'=="" ,<<FIELD>>."|White",
        <<FIELD>>!="" AND group<<MATCHSTR>>_group_row_no2=="" AND 'group<<MATCHSTR>>_group_row_no3'=="" ,<<FIELD>>."|Yellow",
        <<FIELD>>!="" AND group<<MATCHSTR>>_group_row_no2!="" AND 'group<<MATCHSTR>>_group_row_no3'!="" ,<<FIELD>>."|Green",
        true(),<<FIELD>>)] 
| foreach "group*_group_row_no2" 
    [| eval "<<FIELD>>"=case(match(group<<MATCHSTR>>_group_row_no1,"|White"),<<FIELD>>."|White",
        match(group<<MATCHSTR>>_group_row_no1,"|Yellow"),<<FIELD>>."|Yellow",
        match(group<<MATCHSTR>>_group_row_no1,"|Green"),<<FIELD>>."|Green",
        true(),<<FIELD>>)]
| foreach "group*_group_row_no3" 
    [| eval "<<FIELD>>"=case(match(group<<MATCHSTR>>_group_row_no1,"|White"),<<FIELD>>."|White",
        match(group<<MATCHSTR>>_group_row_no1,"|Yellow"),<<FIELD>>."|Yellow",
        match(group<<MATCHSTR>>_group_row_no1,"|Green"),<<FIELD>>."|Blue",
        true(),<<FIELD>>)]

Step 4: Final transpose is applied to revert back the table to a format which can be consumed by the JS for Table Cell Coloring.

| transpose 0 header_field="Header" column_name="Header"

PS: I have applied the logic for Color dependency based on your explanation.
1) Color for Row 1 needs to be decided based on which colors of Row 2 and Row 3 can be derived.
2) Table can have N rows but in multiple of 3 as three rows are mutually dependent for coloring logic.
3) sort 0 event_no is enforcing sorting of events. It may not be required if you have similar sort present already in your current search.
4) As stated earlier depending on your current search an additional transpose in Step 3 can be avoided if foreach template can be applied directed on sorted raw events.

For details on the commands used in this example do refer Splunk Docs.
Please try out and confirm!

[askkawalkar]

@niketnilay , Thank you for your response.. I will try and will let you know, is it works or not..

Thank You,
Ankush

[askkawalkar]

Hi @niketnilay ,
It worked, Thank you for help ! and Thanks for providing logic.

I have made some small changes in Step 3 query to match the color code. I am posting the Changed code for other people reference below.

Changes :
1. To check Null values function used - isnull().
2. Additional condition added.

Step 3 : With Changes:

|  transpose 0 header_field="Header" column_name="Header"
|  foreach "group*_group_row_no1" 
     [| eval "<<FIELD>>"=case(isnull(<<FIELD>>) AND isnull(group<<MATCHSTR>>_group_row_no2) AND isnull(group<<MATCHSTR>>_group_row_no3) ," |White",
         isnotnull(<<FIELD>>) AND isnull(group<<MATCHSTR>>_group_row_no2) AND isnull(group<<MATCHSTR>>_group_row_no3) ,<<FIELD>>."|Yellow",
         isnotnull(<<FIELD>>) AND isnotnull(group<<MATCHSTR>>_group_row_no2) AND isnotnull(group<<MATCHSTR>>_group_row_no3) ,<<FIELD>>."|Green",
         true(),<<FIELD>>)]
 | foreach "group*_group_row_no2" 
     [| eval "<<FIELD>>"=case(isnull(group<<MATCHSTR>>_group_row_no2) and match(group<<MATCHSTR>>_group_row_no1,"dummy|Yellow")," |Yellow",
         isnull(group<<MATCHSTR>>_group_row_no2) and match(group<<MATCHSTR>>_group_row_no1,"dummy|White")," |White",
         match(group<<MATCHSTR>>_group_row_no1,"dummy|Green"),<<FIELD>>."|Green",
         match(group<<MATCHSTR>>_group_row_no1,"dummy|Yellow"),<<FIELD>>."|Yellow",
         match("group<<MATCHSTR>>_group_row_no1","|White"),<<FIELD>>."|White",
         true(),<<FIELD>>)]
 | foreach "group*_group_row_no3" 
     [| eval "<<FIELD>>"=case(isnull(group<<MATCHSTR>>_group_row_no3) and match(group<<MATCHSTR>>_group_row_no1,"dummy|Yellow")," |Yellow",
         isnull(group<<MATCHSTR>>_group_row_no3) and match(group<<MATCHSTR>>_group_row_no1,"dummy|White")," |White",
         match(group<<MATCHSTR>>_group_row_no1,"dummy|Green"),<<FIELD>>."|Blue",
         match(group<<MATCHSTR>>_group_row_no1,"dummy|Yellow"),<<FIELD>>."|Yellow",
         match(group<<MATCHSTR>>_group_row_no1,"|White"),<<FIELD>>."|White",
         true(),<<FIELD>>)]

Thanks to @kamlesh_vaghela for CSS and JS.
Also Please refer below link for CSS and JS which will be used to color code the cell -
https://answers.splunk.com/answers/661894/how-to-color-cell-contents-with-css-and-js.html

[niketn]

@askkawalkar Glad it worked! Thanks for an interesting challenge as this is the first question I noticed on Splunk Answers for Row based Table Cell Color logic.

Do Accept one of the answers to mark this question as answered and Up Vote the answers/comments that helped!

[kamlesh_vaghela]

@askkawalkar

You can achieve this using JS and CSS. Can you please check below answer for same?

https://answers.splunk.com/answers/661894/how-to-color-cell-contents-with-css-and-js.html

[askkawalkar]

@kamlesh_vaghela ,
Thank you for your response - I have gone through this and applied the same, but for this i need to make changes in data which loading it to Splunk, I am trying to avoid that scenario.

From the JS I can get current cell value using below code -

render: function($td, cell) {
var label = cell.value.split("|")[0];
var val = cell.value.split("|")[1];
...
}

My requirement will be fulfilled if I get solution to retrieve the data of next 2 rows and
I tried to do some changes in JS, but unable to retrieve data of next 2 row cell. Is there any way to get the next row in the same ?

[kamlesh_vaghela]

@askkawalkar

You can use streamstats command to access previous record. So you have made change in search only.
Check below search.

| makeresults count=10 | eval number=1 | accum number | sort - number | streamstats window=2 first(number) as number_next