topic Re: Format all columns in a table based on field name instead of field value in Dashboard Studio in Dashboards & Visualizations

Format all columns in a table based on field name instead of field value in Dashboard Studio

Ariane — Tue, 11 Jul 2023 01:56:05 GMT

I have a table in Dashboard Studio that shows percentage values, where the rows are a client code, and the columns are the values of the the field "username". How do I set the column format based on the field name ("username") instead of the field value ("Jenny Staffmember")?

I want to add units, as well as colour formatting of the text, and by default it's done by column label, which is the value of the field it's split by, instead of the name of the field, which results in a lot of repeated config which needs to be updated every time a new user turns up.

Re: Format all columns in a table based on field name instead of field value in Dashboard Studio

ITWhisperer — Tue, 11 Jul 2023 06:56:15 GMT

Please share the search for your table, some anonymised sample data and a visualisation / mock-up of your expected results.

Re: Format all columns in a table based on field name instead of field value in Dashboard Studio

Ariane — Wed, 12 Jul 2023 00:51:19 GMT

This is the search:

index="myindex" sourcetype="timesheets_json" OR sourcetype="tasks_json" work_order="$task_selector$"
| stats values(username) as username values(time_spent) as time_spent values(time_estimate) as estimate values(work_order) as work_order values(list_name) as list_name values(task_name) values(status) as status by task_id
| where ( status="complete" AND estimate>0)
| eval time_perc_est=round((time_spent/estimate)*100), code=substr(list_name,1,9)
| chart avg(time_perc_est) as "Actual vs Estimated Time (%)" by code username useother=f

The data comes from 2 JSON indexes, one looks a bit like this:
"timestamp": 1689067863, "id": "3578049312326190828", "task_id": "860r9v3p6", "username": "User1", "billable": false, "end": 1689067863, "start": 1689064497, "at": 1689067863, "space_id": "7032145", "list_name": "ABCDEF000 Company Name Pty Ltd", "task_name": "Clear Email", "due_date": 1689012000, "start_date": 1689012000, "date_created": 1688976115, "client_type": "Priority", "pt_support": "Someone", "pt": "Someone Else", "work_order": "Client Comms"

And the other:
"due_date": 1688666400, "start_date": 1688666400, "date_created": 1688545863, "date_done": 1688717285, "time_estimate": 300, "status": "complete", "time_spent": 458, "client_type": "Blah Blah", "pt_lead": "Someone", "pt": "Someone Else", "work_order": "Client Comms", "task_id": "860r9v3p6"

I want to end up with something like this:

Client	User1	User2	User3	…
ABCDEF000	25%	150%
ABCDEF001		75%	300%
…

(Somewhat random colour coding there, but you get the idea). The number of columns, and names of the columns will change over time, obviously (and with different searches). The table is being generated, but I can't work out how to format the columns using the field name ("username"), instead of each column individually ("User1", "User2 etc) which requires a lot of copy/pasta, and ongoing manual maintenance every time a new person is onboarded.

Re: Format all columns in a table based on field name instead of field value in Dashboard Studio

Ariane — Wed, 12 Jul 2023 06:49:57 GMT

ARRRGGG!!!

I wrote a very long reply to this, and posted, and it reported it was posted successfully, but it seems not to have actually done so.

I'm not ignoring your question, I just don't have the spare time right now to completely reproduce all of that, which was not speedy to produce in the first place. I will come back and try again when I have the time, and am less frustrated.

Re: Format all columns in a table based on field name instead of field value in Dashboard Studio

Ariane — Wed, 12 Jul 2023 13:10:57 GMT

Here is a second attempt at providing more information. I've reduced some of the duplication by using a single ColumnFormatEditorConfig, but I can't work out how to identify multiple columns in one columnFormat definition.

Following is the code as it stands.

"viz_cost_detail": {

"type": "splunk.table",

"dataSources": {

"primary": "ds_cost_detail"

"title": "Cost detail by Client for $client_filter$",

"options": {

"headerVisibility": "fixed",

"columnFormat": {

"Task": {

"width": 428

"Person1": {

"data": "> table | seriesByName(\"Person1\") | formatByType(Team_MemberColumnFormatEditorConfig)"

"Person2": {

"data": "> table | seriesByName(\"Person2\") | formatByType(Team_MemberColumnFormatEditorConfig)"

}

"count": 120

"context": {

"Team_MemberColumnFormatEditorConfig": {

"number": {

"thousandSeparated": false,

"unitPosition": "after",

"precision": 1,

"unit": "hrs"

}

"description": "Uses time range and Client inputs"

}

"ds_cost_detail": {

"type": "ds.search",

"options": {

"queryParameters": {

"earliest": "$time_filter.earliest$",

"latest": "$time_filter.latest$"

"name": "client_cost_detail"

}

Essentially I'm looking for the equivalent of a default entry for the column format section, so that I don't have to manually repeat this entry:

"Person2": {

"data": "> table | seriesByName(\"Person2\") | formatByType(Team_MemberColumnFormatEditorConfig)"

}

for every single staff member, and every time a new staff member comes on board, which is unworkable. Any insight is greatly appreciated, thanks.

Re: Format all columns in a table based on field name instead of field value in Dashboard Studio

Ariane — Thu, 13 Jul 2023 00:23:00 GMT

Oh, this morning this reply is here! It wasn't yesterday afternoon! 😂

Re: Format all columns in a table based on field name instead of field value in Dashboard Studio

Ariane — Thu, 13 Jul 2023 00:25:36 GMT

This is another use case for the same issue, with a simpler search

Re: Format all columns in a table based on field name instead of field value in Dashboard Studio

ITWhisperer — Thu, 13 Jul 2023 07:36:21 GMT

I am not sure there is a way to do this in Dashboard Studio (yet). Try using SimpleXML / Classic dashboards

(I don't use Studio for anything serious as it is not good enough for my needs.)

Re: Format all columns in a table based on field name instead of field value in Dashboard Studio

Ariane — Thu, 13 Jul 2023 11:30:11 GMT

OK, thanks. For my purposes, the things DS does better are also important (among other things, I need to be able to format the UI so that it's useable by non-technical, non-Splunk people, which means explanatory text, carefully managed layout etc). I knew DS was still kinda in the dev stage, but confess I thought it had got a little further along than this. I'm genuinely not sure what course I should take, but that's not a question for a community forum. Thanks for your response!

Re: Format all columns in a table based on field name instead of field value in Dashboard Studio

ITWhisperer — Thu, 13 Jul 2023 12:05:34 GMT

There is quite a lot you can do with Classic by using CSS and HTML panels, so I guess it depends on how urgent your need is. 😎