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>

Screenshot - 2_23_2022 , 10_10_02 AM.png 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?

Say goodbye to manually analyzing phishing and malware threats with Splunk Attack ...

AppDynamics is now part of Splunk Ideas

Advanced Splunk Data Management Strategies