Set chart colors to value in data

[kcepull2]

I'm trying to set the color of pie segments based on a lookup table. I've seen all sorts of answers that let you use the charting.fieldColors to set the colors, but the examples are all static values.

Here's what I have:
1. My events have a color name in them (e.g. "RED", "DARK BLUE", "MAROON").
2. I've done a search and chart commands to count the number of events by color, and that is what I'm charting in the pie chart. (In other words, each pie slice is the count of events with each color.)
3. I want the color of the pie slice to match the color name that slice represents. E.g. if the pie slice is showing a count of RED events, I want that pie slice to be red.
4. I have a lookup table that has the color names and the hex codes for that color (e.g. RED is 0xFF0000).
5. I can use the lookup table to add the color's hex code to the chart data so that each row in the data contains Color, Count, Hex (e.g. "RED", 323, "0xFF0000").
6. What I can't figure out is how to use either the lookup table or the added hex code in the event to set the pie slice colors. Basically, instead of a static list of values and colors in charting.fieldColors, I want to set the color dynamically based on the data itself (e.g. set it to the hex color string in the data).

Thanks for any suggestions!

Karl

[kcepull2]

Thanks, gokadroid!

This was a great suggestion, and a creative way to force the query to run and populate a token.

I think I'm close, but for some reason, the $colourToken$ isn't getting set. I tried putting it in a

[gokadroid]

Is it the colour token that is not getting set, or is it being set incorrectly. The correct format of colour token is:

"BLUE": 0x0000FF, "GREEN": 0x00FF00, "RED": 0xFF00FF, "YELLOW": 0xFFFF00

Take special note of how the field name is within double quotes followed by a colon and the the hex code followed by a comma before the next one starts "BLUE": 0x0000FF,.

Once all the above Names and codes came into my string in valuesP all I needed to do is take care of following tags of dropdown:

<fieldForLabel>valuesP</fieldForLabel>
       <fieldForValue>valuesP</fieldForValue>
       <selectFirstChoice>true</selectFirstChoice>

Thereafter this piece of code in chart should work (take a note of { } around my token name:

<option name="charting.fieldColors">{$colourToken$}</option>

[kcepull2]

So, my reply got cut off - I had sample code and everything!? 😞

My apologies. It actually worked just fine. I forgot that in EDIT mode, the token names show up. With the 6.5 UI where you can switch between UI and Source w/o saving, I wasn't seeing the change. Once I actually clicked SAVE, it all worked just fine.

So, false alarm. Your suggestion worked perfectly!

Thanks!!!!

Karl

PS: Here's the code I ended up using:

...
    <input type="dropdown" token="colourToken" searchWhenChanged="true" depends="$hideToken$">
      <label>Hidden DropDown</label>
      <search>
        <query>index=traffic sourcetype=traffic_data 
| eval colourCode="\"".replace('row.color',",","\\,")."\": ".CarColorHex 
| stats delim=", " values(colourCode) as valuesP | mvcombine valuesP
| table valuesP</query>
      </search>
      <fieldForLabel>valuesP</fieldForLabel>
      <fieldForValue>valuesP</fieldForValue>
      <selectFirstChoice>true</selectFirstChoice>
    </input>
...
     <chart>
        <search base="top10colors"></search>
...
        <option name="charting.fieldColors">{$colourToken$}</option>
...
      </chart>

[gokadroid · Accepted solution]

Let me have a stab at it, with something similar I achieved where I didn't need a lookup as I generated the codes based on the fields which appeared in events.

My data had events where one "Name" field value only had one "Code" field value. i.e. Name1 always had Code1 ( Name RED always had Code #FF00FF) in all the events. :

Oct 31 2016 00:00:00; Name= RED; Code= #FF00FF; IP=10.120.10.22
Oct 31 2016 00:00:01; Name= GREEN; Code= #00FF00; IP=110.120.10.22
Oct 31 2016 00:00:02; Name= BLUE; Code= #0000FF; IP=210.120.10.22
Oct 31 2016 00:00:03; Name= YELLOW; Code= #FFFF00; IP=180.120.10.22

Based on above events this is what I followed:

1) Create a hidden drop down input element.
2) In this dropdown's dynamic query create a string value, I called it valuesP, that will save all colourNames and colourCodes. These Name and Codes will be picked up from data field values of "Name" and "Code".
3) Format this valuesP string to suit the charting.fieldColors option, eg. for me the codes in valuesP were coming in #pppppp format but charting.fieldColors required colour in 0xpppppp format. (Might not be applicable for you)
4) Field valuesP was table(d) in dropdown, and set for fieldForLabel and fieldForVlaue. This sets the dropdown token which will be used to display colours. I called my dropdown token as colourToken .
5) Use the colourToken token in the charting.fieldColors option of chart.

Here is the dropdown code which was used.

 <input type="dropdown" token="colourToken" searchWhenChanged="true" depends="$hideToken$">
      <label>Hidden DropDown</label>
      <search>
        <query>... | eval colourCode="\"".Name."\": ".Code 
                   | stats values(colourCode) as valuesP 
                   | mvcombine valuesP 
                   | rex field=valuesP mode=sed "s/ \"/, \"/g
                                                 s/\#/0x/g" 
                   | table valuesP
      </query>
      </search>
      <fieldForLabel>valuesP</fieldForLabel>
      <fieldForValue>valuesP</fieldForValue>
      <selectFirstChoice>true</selectFirstChoice>
    </input>

Things to note in dropdown code:
- A dummy token called $hideToken$ was used to keep dropdown hidden all the time as this token will never be set.
- Set the selectFirstChoice = true (just for safety, even though only one string is tabled in query, might not be required though)
- Dropdown token colourToken was used in the charting option of pie chart as:
<option name="charting.fieldColors">{$colourToken$}</option>

NOTE
- My base query that was used to create the pie chart created fields called "RED", "YELLOW" and so on (a static part which requires me to be aware of what colours will be coming in my data) as charting.fieldColors works on fields. Here was my query to plot pie chart:

...| stats count(eval(Name=="RED")) as RED, count(eval(Name=="BLUE")) as BLUE, count(eval(Name=="YELLOW")) as YELLOW, count(eval(Name=="GREEN")) as GREEN

Hope it helps 🙂