Dashboards & Visualizations

Color the Table row Cell based on Other Row Cell value

askkawalkar
Path Finder

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.

alt text

0 Karma
1 Solution

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

askkawalkar
Path Finder

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

Thank You,
Ankush

0 Karma

askkawalkar
Path Finder

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
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
0 Karma
Get Updates on the Splunk Community!

Thanks for the Memories! Splunk University, .conf24, and Community Connections

Thank you to everyone in the Splunk Community who joined us for .conf24 – starting with Splunk University and ...

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...