Dashboards & Visualizations

Simple XML Dashboard & DS Dashboard - Table Column Colour

madhav_dholakia
Contributor

--updated this question to achieve the same behavior on DS Dashboard

Hello, 

I have a table viz on my dashboards (simple XML and DS Dashboards) - sample data as given below.

 

 

| makeresults format=csv data="cust, sla
Cust1,85
Cust2,96
Cust3,99
Cust4,89
Cust5,100" 
| fields cust, sla

 

 

 

madhav_dholakia_0-1709105245618.png

How can I colour code "sla" column based on given conditions in both Simple XML (without using javascript) and DS dashboards?

if (cust IN (Cust1,Cust3,Cust4) AND sla>=90) OR (cust IN (Cust2,Cust5) AND sla>=95) -> Green

if (cust IN (Cust1,Cust3,Cust4) AND sla>=85 AND sla<90) OR (cust IN (Cust2,Cust5) AND sla>=90 AND sla<95) -> Amber

if (cust IN (Cust1,Cust3,Cust4) AND sla<85) OR (cust IN (Cust2,Cust5) AND sla<90) -> Red

Thank you.

Regards,

Madhav

 

Labels (3)
0 Karma
1 Solution

madhav_dholakia
Contributor

below response from Antony Bowesman on Slack Channel worked like a charm for Simple XML Dashboard!

The challenge you have it to set the colour depending on the values from two different columns, which is not possible without a hack or JS. The hack is something like this

 

 

 

 

<panel>
      <html depends="$hidden$">
        <style>
          #coloured_cell2 table tbody td div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
      <table id="coloured_cell2">
        <title>Colouring a table cell based on it's relative comparison to another cell</title>
        <search>
          <query>| makeresults
| fields - _time
| eval r=mvrange(1,6,1)
| mvexpand r
| eval cust="Cust".r
| fields - r
| eval sla=random() % 100
| eval type=case((match(cust,"Cust1|Cust3|Cust4") AND sla&gt;=90) OR (match(cust,"Cust2|Cust5") AND sla&gt;=95), 0,
                 (match(cust,"Cust1|Cust3|Cust4") AND sla&gt;=85 AND sla&lt;90) OR (match(cust,"Cust2|Cust5") AND sla&gt;=90 AND sla&lt;95), 1,
                 (match(cust,"Cust1|Cust3|Cust4") AND sla&lt;85) OR (match(cust,"Cust2|Cust5") AND sla&lt;90), 2)

| eval sla=mvappend(sla, type)
| table cust sla type</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">100</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="sla">
          <colorPalette type="expression">case(mvindex(value, 1) == "0", "#00FF00", mvindex(value, 1) == "1", "#FFFF00", true(), "#FF0000")</colorPalette>
        </format>
        <drilldown>
          <set token="explode_search_id">coloured_cell2</set>
        </drilldown>
      </table>
    </panel>

 

 

 

 

what this shows is that you have to make the column you want to colour a multivalue field, where you set the second value of that field the other condition you want to check with the expression. The second value of the field is 'hidden' through the use of CSS (note id=coloured_cell).

You will need to set the colour type in the search, as there are problems handling complex multivalue statements in the colorPalette expressions, so here it is 0 for green, 1 for amber and 2 for red.

------------------------------------------------------

below response from Lizzy Li on Slack Channel worked like a charm for DS Dashboard!

 

I would recommend doing that logic in the search and adding another column which specifies the color. Then you make it so that sla is colored based on the value in the color field

here i am coloring the product field based on whether there is inventory available. source code looks something like this:

 

{
    "type": "splunk.table",
    "dataSources": {
        "primary": "ds_khzrqtty"
    },
    "title": "Table hiding internal fields - colored by inventory",
    "options": {
        "columnFormat": {
            "_inventory": {
                "data": "> table | seriesByName(\"_inventory\") | formatByType(_inventoryColumnFormatEditorConfig)",
                "rowColors": "> table | seriesByName(\"_inventory\") | rangeValue(_inventoryRowColorsEditorConfig)"
            },
            "product": {
                "data": "> table | seriesByName(\"product\") | formatByType(productColumnFormatEditorConfig)",
                "rowColors": "> table | seriesByName(\"_inventory\") | rangeValue(_inventoryRowColorsEditorConfig)"
            }
        },
        "showInternalFields": false
    },
    "context": {
        "_inventoryColumnFormatEditorConfig": {
            "number": {
                "thousandSeparated": false,
                "unitPosition": "after"
            }
        },
        "_inventoryRowColorsEditorConfig": [
            {
                "value": "#D41F1F",
                "to": 1
            },
            {
                "value": "#118832",
                "from": 1
            }
        ],
        "productColumnFormatEditorConfig": {
            "string": {
                "unitPosition": "after"
            }
        }
    },
    "description": "Has inventory = green",
    "showProgressBar": false,
    "showLastUpdated": false,
    "hideWhenNoData": false
}

 

as you can see, i also made _inventory an internal field so that i could hide it from the display but still use it to color other fields

 

Thank you.

View solution in original post

0 Karma

madhav_dholakia
Contributor

below response from Antony Bowesman on Slack Channel worked like a charm for Simple XML Dashboard!

The challenge you have it to set the colour depending on the values from two different columns, which is not possible without a hack or JS. The hack is something like this

 

 

 

 

<panel>
      <html depends="$hidden$">
        <style>
          #coloured_cell2 table tbody td div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
      <table id="coloured_cell2">
        <title>Colouring a table cell based on it's relative comparison to another cell</title>
        <search>
          <query>| makeresults
| fields - _time
| eval r=mvrange(1,6,1)
| mvexpand r
| eval cust="Cust".r
| fields - r
| eval sla=random() % 100
| eval type=case((match(cust,"Cust1|Cust3|Cust4") AND sla&gt;=90) OR (match(cust,"Cust2|Cust5") AND sla&gt;=95), 0,
                 (match(cust,"Cust1|Cust3|Cust4") AND sla&gt;=85 AND sla&lt;90) OR (match(cust,"Cust2|Cust5") AND sla&gt;=90 AND sla&lt;95), 1,
                 (match(cust,"Cust1|Cust3|Cust4") AND sla&lt;85) OR (match(cust,"Cust2|Cust5") AND sla&lt;90), 2)

| eval sla=mvappend(sla, type)
| table cust sla type</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">100</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="sla">
          <colorPalette type="expression">case(mvindex(value, 1) == "0", "#00FF00", mvindex(value, 1) == "1", "#FFFF00", true(), "#FF0000")</colorPalette>
        </format>
        <drilldown>
          <set token="explode_search_id">coloured_cell2</set>
        </drilldown>
      </table>
    </panel>

 

 

 

 

what this shows is that you have to make the column you want to colour a multivalue field, where you set the second value of that field the other condition you want to check with the expression. The second value of the field is 'hidden' through the use of CSS (note id=coloured_cell).

You will need to set the colour type in the search, as there are problems handling complex multivalue statements in the colorPalette expressions, so here it is 0 for green, 1 for amber and 2 for red.

------------------------------------------------------

below response from Lizzy Li on Slack Channel worked like a charm for DS Dashboard!

 

I would recommend doing that logic in the search and adding another column which specifies the color. Then you make it so that sla is colored based on the value in the color field

here i am coloring the product field based on whether there is inventory available. source code looks something like this:

 

{
    "type": "splunk.table",
    "dataSources": {
        "primary": "ds_khzrqtty"
    },
    "title": "Table hiding internal fields - colored by inventory",
    "options": {
        "columnFormat": {
            "_inventory": {
                "data": "> table | seriesByName(\"_inventory\") | formatByType(_inventoryColumnFormatEditorConfig)",
                "rowColors": "> table | seriesByName(\"_inventory\") | rangeValue(_inventoryRowColorsEditorConfig)"
            },
            "product": {
                "data": "> table | seriesByName(\"product\") | formatByType(productColumnFormatEditorConfig)",
                "rowColors": "> table | seriesByName(\"_inventory\") | rangeValue(_inventoryRowColorsEditorConfig)"
            }
        },
        "showInternalFields": false
    },
    "context": {
        "_inventoryColumnFormatEditorConfig": {
            "number": {
                "thousandSeparated": false,
                "unitPosition": "after"
            }
        },
        "_inventoryRowColorsEditorConfig": [
            {
                "value": "#D41F1F",
                "to": 1
            },
            {
                "value": "#118832",
                "from": 1
            }
        ],
        "productColumnFormatEditorConfig": {
            "string": {
                "unitPosition": "after"
            }
        }
    },
    "description": "Has inventory = green",
    "showProgressBar": false,
    "showLastUpdated": false,
    "hideWhenNoData": false
}

 

as you can see, i also made _inventory an internal field so that i could hide it from the display but still use it to color other fields

 

Thank you.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...