How to color duplicate cell values dashboard table...

genesiusj · ‎02-23-2022

Hello,

Need to color cells in a dashboard table based on duplicate cell values (2 or more) within the same row.

Here is the formatting code for the attached example.

<format type="color">
   <colorPalette type="sharedList"></colorPalette>
   <scale type="sharedCategory"></scale>
</format>

Thanks and God bless,
Genesius

ITWhisperer · ‎03-02-2022

You could try adding a multivalue to the cells which have duplicates in the row and using that to colour the cells (using CSS to hide the extra value)

| untable Field1 FieldName value
| eventstats count by Field1 value
| eval value=if(count>1,mvappend(value,"DUPLICATE"),value)
| xyseries Field1 FieldName value

genesiusj · ‎03-03-2022

@ITWhisperer

Thank you. I should have mentioned that my code uses | transpose. Here is the code.

| dbxquery connection="DB2" query="select distinct IDN, UIB, DTE_UIB, NME_LAST_CLMT, NME_FIRST_CLMT, NME_MIDDLE_CLMT, EMAIL, ADD_STREET_MAIL, ADD_CITY_MAIL, ADD_STATE_MAIL, ADD_ZIP_MAIL, PHONE, ADD_STREET_RES, ADD_CITY_RES, ADD_STATE_RES, ADD_ZIP_RES from CLMT where ((TO_CHAR(PHONE) like '%9087777777%') or (ADD_STREET_MAIL like '%1 MAIN STREET%')) " 
| eval zipCodeM=if(len(ADD_ZIP_MAIL)=9 or len(ADD_ZIP_MAIL)=5,substr(ADD_ZIP_MAIL,1,5),substr(printf("%09d",ADD_ZIP_RES),1,5)) 
| eval zipCodeR=if(len(ADD_ZIP_RES)=9 or len(ADD_ZIP_RES)=5,substr(ADD_ZIP_RES,1,5),substr(printf("%09d",ADD_ZIP_RES),1,5)) 
| table IDN, UIB, DTE_UIB, NME_LAST_CLMT, NME_FIRST_CLMT, NME_MIDDLE_CLMT, EMAIL, ADD_STREET_MAIL, ADD_CITY_MAIL, ADD_STATE_MAIL, ADD_ZIP_MAIL, zipCodeM, PHONE, ADD_STREET_RES, ADD_CITY_RES, ADD_STATE_RES, ADD_ZIP_RES, zipCodeR 
| eval Name=if(isnotnull(NME_MIDDLE_CLMT),trim(NME_LAST_CLMT).", ".trim(NME_FIRST_CLMT)." ".trim(NME_MIDDLE_CLMT),trim(NME_LAST_CLMT).", ".trim(NME_FIRST_CLMT)) 
| eval IDN = printf("%07d",IDN)
| eval CCODE=IDN.",".UIB.",".DTE_UIB 
| `convert_to_String(CCODE)` 
| eval cCode=String." ".Name 
| rex field=cCode mode=sed "s/ /\n/"
| fields - Name, ADD_ZIP_MAIL, ADD_ZIP_RES, String, CCODE 
| transpose 0 column_name="Field" header_field=cCode

I am not familiar with the untable and xyseries commands. As I am using transpose, would they still function properly?
Here is the XML.

  <row>
    <panel>
      <html>
        <style>
          .table th {
            background-color: black !important;
            color: white !important;
          }
        </style>
      </html>
      <table>
        <search>
          <query> 
 :
 :   <SPL IS IN THE ABOVE CODE SAMPLE BOX>
 :
          <earliest>1583038800</earliest>
          <latest>1645623637</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">100</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">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
        <format type="color" field="Field">
          <colorPalette type="list">[#000000]</colorPalette>
        </format>
      </table>
    </panel>
  </row>

Thanks and God bless,
Genesius

genesiusj · ‎03-01-2022

I found a partial answer.

The column named Field - Note the order of the format tags matters.

I hope one of the fezzes can figure out the other issue: cells with single values should not be colored.

Thanks and God bless,
Genesius

How to color duplicate cell values dashboard table?

other

Introducing the Splunk Community Dashboard Challenge!

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Get Your Exclusive Splunk Certified Cybersecurity Defense Engineer Certification at ...