Set Colors On Cells Of A Table By Comparing With The Previous Cell Values Of Each An Every Row
Today we are back with another interesting Splunk dashboard customization, this time in a table. So at first let’s try to understand what we are trying to do today.
So we have this data,
Here we are basically monitoring a number of messages is generating each month. So now we will compare the number of messages is generating for a month compared to the previous month. Suppose for “Message 0” in Nov-20 we have messages and in Dec-20 we had 1, that means that we have more message in dec-20 compare to the previous month, so in this case we need the “green” color in the “Dec-20” cell for “Message 0”. Then as you go to the next cell “Jan-21” we see messages, so this time we have less number of messages compared to previous month (Dec-20), so in this case we need the “red” color in the “Jan-21” cell for “Message 0”. Now if we have the same number of messages compared to the previous month like “Jan-21” and “Feb-21” then in that case we will fill with “yellow” in the “Feb-21” cell for “Message 0”. In the case of a starting cell we also fill with “yellow”. So my first column “Nov-20” will always fill with “Yellow”.
So let’s start the proceeding,
First of all we will create the query and our main goal will be appending the color name to each cell that we wanna fill with.
Try this query out,
index="demo" sourcetype="message_data" | table Messages,Nov_20,Dec_20,Jan_21,Feb_21 | 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
Result:
Explanation:
As you all can see that this is a long query so we will go line by line.
Line1 & line2: We took the data from demo index and message_data sourcetype, then using table command we took all of our required fields.
Line3: using the transpose command we changed the orientation of the specified fields in the x-axis we took all message fields and into y-axis we took months.
Line4 & line5: using eval command we converted the “Month” into epoch time and sorted it in descending order.
Line6: using “autoregress” command we copied the 1st previous value from the month field and put that into a new field called next.
Line7 & line8: using the mvappend function we appended values from the next and Month field into the months field and then using the fields command we excluded the next field.
Line9: then using mvexpand we expanded the month’s field
Line10: using stats comment we listed all the values for months. This step will append all the message values with the previous message value in one cell. That’s what we need.
Line12: from here, we are using foreach command, which means whatever will be applicable for all fields.
Line13 to Line18: we are actually appending the color names which suited for that cell comparing by the previously appended values(line 10).
Line20 to Line24: we are rearranging our data with the Month field and finally it will look like this.
Now we will save this into a dashboard named as “Color based on the previous row in the table”
Now in the dashboard, we will click on Edit and Source.
And do all of these modifications as shown.
And then Save the dashboard and see the coloring is working perfectly or not.
As you can see whenever the value is more than the previous cell it’s filled with green and if it is less than filled with red if it is the same then it’s yellow.
The complete source code of the dashboard is given below. Click on the Source Code to download the file.
Source Code
The query we have used at first there from line13 to line 18 can be slightly difficult if you are new to Splunk. It makes it simpler we have another alternative query that is slightly bigger but can be understandable very easily.
index="demo" sourcetype="message_data" | table Messages,Nov_20,Dec_20,Jan_21,Feb_21 | 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 <<FIELD>>_a=(tonumber(mvindex('<<FIELD>>',0)) - tonumber(mvindex('<<FIELD>>', 1))) |eval "Message 00"=mvappend(if('Message 0_a' < 0, "RED", if('Message 0_a' > 0, "GREEN", "YELLOW")),mvindex('Message 0',0)), "Message 01"=mvappend(if('Message 1_a' < 0, "RED", if('Message 1_a' > 0, "GREEN", "YELLOW")),mvindex('Message 1',0)), "Message 02"=mvappend(if('Message 2_a' < 0, "RED", if('Message 2_a' > 0, "GREEN", "YELLOW")),mvindex('Message 2',0)), "Message 03"=mvappend(if('Message 3_a' < 0, "RED", if('Message 3_a' > 0, "GREEN", "YELLOW")),mvindex('Message 3',0)), "Message 04"=mvappend(if('Message 4_a' < 0, "RED", if('Message 4_a' > 0, "GREEN", "YELLOW")),mvindex('Message 4',0)), "Message 05"=mvappend(if('Message 5_a' < 0, "RED", if('Message 5_a' > 0, "GREEN", "YELLOW")),mvindex('Message 5',0)), "Message 06"=mvappend(if('Message 6_a' < 0, "RED", if('Message 6_a' > 0, "GREEN", "YELLOW")),mvindex('Message 6',0)), "Message 07"=mvappend(if('Message 7_a' < 0, "RED", if('Message 7_a' > 0, "GREEN", "YELLOW")),mvindex('Message 7',0)), "Message 08"=mvappend(if('Message 8_a' < 0, "RED", if('Message 8_a' > 0, "GREEN", "YELLOW")),mvindex('Message 8',0)) ] |fields Month "Message 00" "Message 01" "Message 02" "Message 03" "Message 04" "Message 05" "Message 06" "Message 07" "Message 08" | eval Month=mvindex(Month,0) | sort Month | eval Month=strftime(Month, "%b %y") | transpose 0 header_field=Month column_name=Messages
Happy Splunking!!
