Need to color code cells by creating a condition

[Mrig342]

Hi All,

I have got logs like below:

</tr>
<tr>
<td ><b><font color=blue>Asia</font></b></td>
<td >Samsung_AA</td>
<td ><b><font color=green>Singapore</font></b></td>
<td ><b><font color="green">UP</font></b></td>
<td >1100</td>
<td >311-1000</td>
<td >311-1000</td>
<td >0-200000</td>
<td >3172-3</td>
<td >55663</td>
<td >NC</td>
<td >3.983-20000</td>
<td >11112-20000</td>
<td >6521-10000</td>

I used below query to get the below table:

... | rex field=_raw "\<tr\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\w+\>(?P<Region>[^\<]+)\<\/\w+\>\<\/b\>\<\/td\>"
| rex field=_raw "\<tr\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\w+\>[^\<]+\<\/\w+\>\<\/b\>\<\/td\>\s+\<td\s\>(?P<VPN_Name>[^\<]+)\<\/td\>"
| rex field=_raw "\<tr\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\w+\>[^\<]+\<\/\w+\>\<\/b\>\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\w+\>(?P<Country>[^\<]+)\<\/\w+\>\<\/b\>\<\/td\>"
| rex field=_raw "\<tr\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\w+\>[^\<]+\<\/\w+\>\<\/b\>\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\w+\>[^\<]+\<\/\w+\>\<\/b\>\<\/td\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\"\w+\"\>(?P<VPN_Status>[^\<]+)\<\/\w+\>\<\/b\>\<\/td>"
| rex field=_raw "\<tr\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\w+\>[^\<]+\<\/\w+\>\<\/b\>\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\w+\>[^\<]+\<\/\w+\>\<\/b\>\<\/td\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\"\w+\"\>[^\<]+\<\/\w+\>\<\/b\>\<\/td>\s+\<td\s\>(?P<Spooled>[^\<]+)\<\/td\>\s+\<td\s\>(?P<Conn_Max>[^\<]+)\<\/td\>\s+\<td\s\>(?P<Conn_SMF_Max>[^\<]+)\<\/td\>\s+\<td\s\>(?P<Conn_Rest_Max>[^\<]+)\<\/td\>\s+\<td\s\>(?P<Queue_Topic>[^\<]+)\<\/td\>\s+\<td\s\>(?P<SMF_SSL>[^\<]+)\<\/td\>\s+\<td\s\>(?P<Rest_SSL>[^\<]+)\<\/td\>\s+\<td\s\>(?P<Spool_Usage_Max>[^\<]+)\<\/td\>\s+\<td\s\>(?P<Ingress_Usage_Max>[^\<]+)\<\/td\>\s+\<td\s\>(?P<Egress_Usage_Max>[^\<]+)\<\/td\>"
| eval Time_Stamp=strftime(_time, "%b %d, %Y %I:%M:%S %p")
| replace "UAT2-L2" with "NGC" in Region | replace "UAT2-L1" with "GC" in Region | search Region="Asia" | search VPN_Status="UP"
| table Time_Stamp,VPN_Name,Spooled,Conn_Max,Conn_SMF_Max,Conn_Rest_Max,Queue_Topic,Spool_Usage_Max,Ingress_Usage_Max,Egress_Usage_Max
| dedup VPN_Name

Time_Stamp VPN_Name Spooled Conn_Max Conn_SMF_Max Conn_Rest_Max Queue_Topic Spool_Usage_Max Ingress_Usage_Max Egress_Usage_Max

Oct 16, 2023 03:51:08 AM	Samsung_AB	0	1-500	1-500	0-200000	3-2	0.000-5000	0-10000	0-10000
Oct 16, 2023 03:51:08 AM	Samsung_AA	1100	311-1000	311-1000	0-200000	3172-3	3.983-20000	11112-20000	6521-10000

In this table, I want to color-code the cells of the columns (Conn_Max, Conn_SMF_Max, Conn_Rest_Max, Spool_Usage_Max, Ingress_Usage_max & Egress_Usage_Max), where if first part of the field-value is greater than or equal to 50% & 80% of the second part. For e.g. if Conn_Max is 6500-10000 then it should be in yellow and if it is 8500-10000 then it should be in red  color.

Please help me to modify the query or source code so that I can get the required cells color coded as per my requirements.

Your kind inputs are highly appreciated. Thank You..!!

[bowesmana]

The only way you can make colour coding on cells like that is to use the format expression, but the expression you would need does not appear to work, i.e.

<format type="color" field="Conn_Max">
  <colorPalette type="expression">case(tonumber(mvindex(split(value,"-"), 0)) &gt; (tonumber(mvindex(split(value,"-"), 1)) * .8), "#FF0000", tonumber(mvindex(split(value,"-"), 0)) &gt; (tonumber(mvindex(split(value,"-"), 1)) * .5), "#FFFFCC", 1==1, "#00FF00")</colorPalette>
</format>

What this attempts to do is to split the numbers and calculate the values, but it does not work - I believe there is a bug when using multivalue eval expressions.

Otherwise, I don't believe it's possible to do it this way, you would have to do it differently