topic Merge the rows and give different colors in Dashboards & Visualizations

Merge the rows and give different colors

anooshac — Wed, 27 Nov 2019 04:00:30 GMT

Hello,
I am new to splunk. I have a requirement where I need to merge the rows in a table which are of repeating data and give different color to those merged rows. I explored alot but failed to get the answer. Can anyone please help me in this.

Re: Merge the rows and give different colors

renjith_nair — Wed, 27 Nov 2019 05:32:03 GMT

@anooshac,
What you mean by merging rows? Do you mean deleting the duplicates or making a multi value of the rows ?
Would be great if you could provide some sample/dummy input and expected output (please mask confidential data)

Re: Merge the rows and give different colors

anooshac — Wed, 27 Nov 2019 05:43:38 GMT

Yes. I want to delete the duplicates.
Consider the table below which has 2 fields.
Field1 Field2
A. 1
A. 1
A. 2
B. 3
B. 3
B. 2

I want to avoid repetition of values and make the data appear in a single cell. And I want to give colors for rows which are being merged. I have merged the rows using streamstats but I am not getting how to color those merged cells. Please help me in this!
Sorry in the table which I have posted the data of field1 and field2 doesn't have gap.

Re: Merge the rows and give different colors

renjith_nair — Wed, 27 Nov 2019 05:59:54 GMT

@anooshac,

Based on the example ,

"your search" |stats count as Merged by Field1,Field2

This should combine the rows by deleting duplicates and provides you a count of merged rows. Color of the cells can be set if Merged > 1

Here is a run anywhere example

<dashboard>
  <label>Merge Rows</label>
  <row>
    <panel>
      <table>
        <search>
          <query>|makeresults|eval field1="A A A B B B"|makemv field1|mvexpand field1
|appendcols [|makeresults|eval field2="1 1 2 3 3 2"|makemv field2|mvexpand field2]
|stats count as Merged by field1,field2</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="count">
          <colorPalette type="list">[#FFFFFF,#D93F3C,#D93F3C]</colorPalette>
          <scale type="threshold">2,1000</scale>
        </format>
        <format type="color" field="Merged">
          <colorPalette type="list">[#FFFFFF,#D93F3C]</colorPalette>
          <scale type="threshold">2</scale>
        </format>
      </table>
    </panel>
  </row>
</dashboard>

Reference : https://docs.splunk.com/Documentation/SplunkCloud/latest/Viz/TableFormatsFormatting

If you want to customize the table rendering, please have a look at the Dashboard Examples

Re: Merge the rows and give different colors

anooshac — Wed, 27 Nov 2019 06:23:50 GMT

Thanks for the answer. Sorry but I don't want to get the count. I just want to make the above table to look like this.
Field1 Field2
A 1

B. 3

And for the field1 which is being merged with cells I want to give different colors for different values. That is consider value"A" I want to give some color to those 3 cells which are being merged and for cells which are being merged with values "B" different color and so on. And I have alot of data in field1 as well as field2 this is just a dummy data.If you have some suggestions please help me . Thanks alot and really sorry for not explaining it properly.
I don't know why some values are in rectangular box. I am using my cell phone.

Re: Merge the rows and give different colors

renjith_nair — Wed, 27 Nov 2019 07:06:48 GMT

@anooshac ,

Are you trying something similar to

<dashboard>
  <label>Merge Rows</label>
  <row>
    <panel>
      <table>
        <search>
          <query>|makeresults|eval field1="A A A B B B"|makemv field1|mvexpand field1
|appendcols [|makeresults|eval field2="1 1 2 3 3 2"|makemv field2|mvexpand field2]
|stats values(field2) as field2 by field1|nomv field2</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="count">
          <colorPalette type="list">[#FFFFFF,#D93F3C,#D93F3C]</colorPalette>
          <scale type="threshold">2,1000</scale>
        </format>
        <format type="color" field="Merged">
          <colorPalette type="list">[#FFFFFF,#D93F3C]</colorPalette>
          <scale type="threshold">2</scale>
        </format>
        <format type="color" field="field2">
          <colorPalette type="map"></colorPalette>
        </format>
        <format type="color" field="field1">
          <colorPalette type="map">{"A":#65A637,"B":#A2CC3E}</colorPalette>
        </format>
      </table>
    </panel>
  </row>
</dashboard>