How to format a table column based on its value using json?

[genesiusj]

Hello, 

Using dashboard studio (DS), and having an issue formatting a table with a single column based on its value.

The field values look something like this.

CPU: 37.1% C MEM: 60.2% W NET: 3.6% N

Here is the pseudocode for selecting the background color of the cell based on its value.
If
the value contains at least one "% C" the background color of the cell needs to be red.
Else if
the value contains at least one "% W", the background color of the cell needs to be yellow.
Else if
the value does not contain "% C" AND does not contain "% W",  the background color of the cell needs to be green.
End if.

According to some web page I read about DS, you cannot perform any inline CSS or JS functions. I don't believe I need JS for this simple use case. I read here, Selector and formatting functions, that I can use matchValue(). After resolving all the json errors (brackets and braces missing), no change in the color of cell background.

Thanks in advance and God bless,
Genesius

[PaulPanther]

@genesiusj 

Check out  following page <yoursplunk.com>/app/splunk-dashboard-studio/example-hub-table-chart-coloring in your Splunk Dashboard Studio examples.

There is a good example that should help you!

[genesiusj]

@PaulPanther 

Many questions. My code did not work.

		"viz_qRFCeLJ6": {
			"type": "splunk.table",
			"options": {
				"columnFormat": {
					"Tags": {
						"rowBackgroundColors": "> table | seriesByName('hypervHostMaxMetrics') | matchValue(hypervHostMaxMetricsRowBackgroundColorsEditorConfig)"
					}
				}
			},
			"context": {
				"hypervHostMaxMetricsRowBackgroundColorsEditorConfig": [
					{
						"match": "*S1*",
						"value": "#1F4D5B"
					},
					{
						"match": "*S2*",
						"value": "#D81E5B"
					}
				]
			},

1. How do I search for wildcards? From my original post, the data will look something like this:
   HOST-S1-123456
   CPU 30.0% W
   MEM 14.0% N
   NET 85.0% C
   Because hypervHostMaxMetrics contains at least 1 occurrence of % C, the background should be red. If there were no % C, but at least 1 occurrence of %W, the background should be orange. And if there are no occurrences of % C and no occurrences of % W, meaning there are 3 occurrences of % N, the background should be green.
   In the above-supplied code, I am using the S1 or S2 as these are in the HOST because there is only one occurrence of either of these per cell. Where the % C, W, N might have 1, 2, or 3 occurrences. troubleshooting wildcards and not the occurrences of the match.
2. I am only using the json for rowBackgroundColors, rowColors, and countRowColorsEditorConfig pertaining to the string. The numeric color formatting is not required.
   In Classic I used the <format> tags to select the color based on the % C, W, or N using a case statement/command.

        <format type="color" field="Hyper-V Host Max Metrics">
          <colorPalette type="expression">case (match(value,"% C"), "#FF0000",match(value,"% W"), "#FFA500",match(value,"% N"), "#008000")</colorPalette>
        </format>
​

How do I do the same in DS?

• I changed match to matchValue as explained in the Selector and formatting functions page I referred to in the original post. But this did not work either.

Thanks again for your help.
God bless,
Genesius

[PaulPanther]

@genesiusj Okay, got it. Probably it is not possible to use wildcards yet. I'm trying to get feedback regarding it from splunk.

I would suggest to use case command in your search and create a new field that you then use to set the color.

[genesiusj]

@PaulPanther 

In Studio, using a case statement in the SPL results with an additional value in the field. I use a case statement in Classic; however, this is within the format tags. No additional values.

I could perhaps modify this value's font color to the color of the overall cell based on the case statement.
Hmmm! Let me think about that one.

Thanks and God bless,
Genesius

[genesiusj]

@PaulPanther 

I'm going to show some love right now and hand out the karma. I will hopefully get a chance today to review and implement it, and mark as the resolution. Always something going on. As I told my staff this morning.

" I didn't know, 😋 , we had a DS Examples app. Reel Cool!"

Thanks and God bless,
Genesius

[PaulPanther]

@genesiusj

I chatted with the visualizations team; a good workaround for this situation is using tokens. So, to answer your question, Dashboard Studio doesn't support color settings for columns based on a string that includes asterisks, but tokens will achieve a similar, if not the same result.

The following are two different examples. The first is an example of using tokens for column coloring. The second example uses dynamic options syntax for column coloring.

Example 1 uses $someToken$ as a match value. When you click on a cell in the table, the token will update the title of the panel as well as the color.

{
    "layout": {
        "type": "absolute",
        "structure": [
            {
                "item": "viz_tuAdxK4V",
                "type": "block",
                "position": {
                    "x": 0,
                    "y": 0,
                    "w": 300,
                    "h": 300
                }
            }
        ]
    },
    "title": "",
    "description": "",
    "visualizations": {
        "viz_tuAdxK4V": {
            "type": "splunk.table",
            "options": {
                "columnFormat": {
                    "user": {
                        "data": "> table | seriesByName(\"user\") | formatByType(userColumnFormatEditorConfig)",
                        "rowColors": "> table | seriesByName('user') | pick(userRowColorsEditorConfig)",
                        "rowBackgroundColors": "> table | seriesByName(\"user\") | matchValue(userRowBackgroundColorsEditorConfig)"
                    }
                }
            },
            "dataSources": {
                "primary": "ds_mvjznCpn"
            },
            "context": {
                "userColumnFormatEditorConfig": {
                    "string": {
                        "unitPosition": "after"
                    }
                },
                "userRowColorsEditorConfig": [
                    "#b5b5b5"
                ],
                "userRowBackgroundColorsEditorConfig": [
                    {
                        "match": "admin",
                        "value": "#5C33FF"
                    },
                    {
                        "match": "$someToken$",
                        "value": "#207865"
                    }
                ]
            },
            "eventHandlers": [
                {
                    "type": "drilldown.setToken",
                    "options": {
                        "tokens": [
                            {
                                "token": "someToken",
                                "key": "value"
                            }
                        ]
                    }
                }
            ],
            "title": "$someToken$"
        }
    },
    "dataSources": {
        "ds_mvjznCpn": {
            "type": "ds.search",
            "options": {
                "queryParameters": {
                    "earliest": "-24h@h",
                    "latest": "now"
                },
                "query": "index=_internal \n| stats count by user"
            },
            "name": "Search_1"
        }
    }
}

Example 2

{
    "layout": {
        "type": "absolute",
        "structure": [
            {
                "item": "viz_tuAdxK4V",
                "type": "block",
                "position": {
                    "x": 0,
                    "y": 0,
                    "w": 300,
                    "h": 300
                }
            }
        ]
    },
    "title": "",
    "description": "",
    "visualizations": {
        "viz_tuAdxK4V": {
            "type": "splunk.table",
            "options": {
                "columnFormat": {
                    "user": {
                        "data": "> table | seriesByName(\"user\") | formatByType(userColumnFormatEditorConfig)",
                        "rowColors": "> table | seriesByName('user') | pick(userRowColorsEditorConfig)",
                        "rowBackgroundColors": "> table | seriesByName(\"user\") | matchValue(userRowBackgroundColorsEditorConfig)"
                    }
                }
            },
            "dataSources": {
                "primary": "ds_mvjznCpn"
            },
            "context": {
                "userColumnFormatEditorConfig": {
                    "string": {
                        "unitPosition": "after"
                    }
                },
                "userRowColorsEditorConfig": [
                    "#b5b5b5"
                ],
                "userRowBackgroundColorsEditorConfig": [
                    {
                        "match": "admin",
                        "value": "#5C33FF"
                    },
                    {
                        "match": "$someToken$",
                        "value": "#207865"
                    },
                    {
                        "match": "> someValue",
                        "value": "#AD3F20"
                    }
                ],
                "someValue": "> primary | seriesByIndex(0) | lastPoint()"
            },
            "eventHandlers": [
                {
                    "type": "drilldown.setToken",
                    "options": {
                        "tokens": [
                            {
                                "token": "someToken",
                                "key": "value"
                            }
                        ]
                    }
                }
            ],
            "title": "$someToken$"
        }
    },
    "dataSources": {
        "ds_mvjznCpn": {
            "type": "ds.search",
            "options": {
                "queryParameters": {
                    "earliest": "-24h@h",
                    "latest": "now"
                },
                "query": "index=_internal \n| stats count by user"
            },
            "name": "Search_1"
        }
    }
}

[genesiusj]

@PaulPanther 

Apologies for the delay in replying. Priorities constantly shift where I work.

I did not try with Example 1 because the cell color needs to change after the results from the search appear on the dashboard. They are not the result of an input (no token to be set).

Concerning Example 2. Is user the column name where the string is to be found? Here is my code. I'm only using one color to match. Since every cell contains the string CPU the cell should be changed to that color. Once I know that works, then I can add other colors and other values for those colors.

		"viz_tiles_3": {
			"type": "splunk.table",
			"title": "Hyper-V Host Max Metrics - 3",
			"dataSources": {
				"primary": "ds_main_tiles"
			},
			"options": {
				"hypervHostMaxMetrics": {
					"data": "> table | seriesByName(\"hypervHostMaxMetrics\") | formatByType(hypervHostMaxMetrics_ColumnFormatEditorConfig)",
					"rowColors": "> table | seriesByName('hypervHostMaxMetrics') | pick(hypervHostMaxMetrics_RowColorsEditorConfig)",
					"rowBackgroundColors": "> table | seriesByName(\"hypervHostMaxMetrics\") | matchValue(hypervHostMaxMetrics_RowBackgroundColorsEditorConfig)"
				}
			},
			"context": {
				"hypervHostMaxMetrics_ColumnFormatEditorConfig": {
					"string": {
						"unitPosition": "after"
					}
				},
				"hypervHostMaxMetrics_RowColorsEditorConfig": [
					"#b5b5b5"
				],
				"hypervHostMaxMetrics_RowBackgroundColorsEditorConfig": [
					{
						"match": "CPU",
						"value": "#5C33FF"
					}
				],
				"someValue": "> primary | seriesByIndex(0) | lastPoint()"
			}
		}

Thanks and God bless,
Genesius

 

[PaulPanther]

@genesiusj Could you provide me a sample event  to test it by myself? Thank you!

[genesiusj]

@PaulPanther 

Thank you. I will check these out and get back to you after the weekend.
God bless,
Genesius