Dashboards & Visualizations
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

Simple XML Dashboard & DS Dashboard - Table Column Colour

madhav_dholakia
Contributor
‎02-27-2024 11:32 PM

--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
Reply
1 Solution
Solved! Jump to solution
Solution

madhav_dholakia
Contributor
‎03-01-2024 08:30 AM

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
Reply
Solved! Jump to solution
Solution

madhav_dholakia
Contributor
‎03-01-2024 08:30 AM

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
Reply
Get Updates on the Splunk Community!

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Get Inspired! We’ve Got Validation that Your Hard Work is Paying Off

We love our Splunk Community and want you to feel inspired by all your hard work! Eric Fusilero, our VP of ...

What's New in Splunk Enterprise 9.4: Features to Power Your Digital Resilience

Hey Splunky People! We are excited to share the latest updates in Splunk Enterprise 9.4. In this release we ...