Splunk Search

How to format table range per week automatically?

efavreau
Motivator

Reading through the documentation here: http://docs.splunk.com/Documentation/Splunk/7.0.2/Viz/TableFormatsFormatting

I am able to Format table columns in stats, using Ranges.

We have a weekly report where the stats look back several weeks, and I want the formatting to apply to each week. I can do this manually, and it looks great.

The issue is when I run the report next week, the latest week doesn't have the formatting and I must manually re-apply it. (see screenshot of it not being manually applied the last 3 weeks)

Is there a way around this manual work, so the color-ranges will apply automatically?

Can this be done in reports, and/or in dashboards, and in straight Splunk? Or do would I have to go to custom solutions to do this?

6 week chart with 3 manually formatted weeks based on range, and 3 weeks not formatted

###

If this reply helps you, an upvote would be appreciated.
0 Karma
1 Solution

niketn
Legend

@efavreau, best option is handle such scenarios with Simple XML JavaScript Extension which allows you to access SplunkJS Stack and you can apply custom code as per your needs. Such code if written properly can be reused across your App/Enterprise.

It is possible to handle this use case in Simple XML as well. But since it will involve additional code, you should also explain as to why you are trying to refrain from JavaScript Code.

Simple XML code does not seem to work if the same dashboard is refreshed with a different set of dates, it seems to work only on fresh dashboard load. Which implies you will have to provide the date selection in one dashboard and then use <link> <drilldown> option to open the dashboard in a new window.

Following code is a run anywhere dashboard example on top of Splunk's _internal index. It assumes you need to color 7 date columns. Hence sets 7 tokens (more than 7 implies setting more tokens and less than 7 implies the dates which are available will have color set).

The actual implementation depends on the SPL you are using to display 7 days of data. My example uses timechart with span of 1 day to plot count of errors for each Components (set the limit to 10, but can be increased). Then the results are reversed using transpose command. Post processing is used to run timechart command only once and then first search with transpose is used to get the date column names using row 1, row 2 ... row 7 to access _time field and assign to tokens tok1, tok2... tok7 respectively. The second search is used to display the results in tabular format.

Date field names assigned to $tok1$, $tok2$... $tok7$ are used to set the color for respective column in the displayed table dynamically. For example for $tok1$

        <format type="color" field="$tok1$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>

alt text

alt text
Following is the run anywhere dashboard example code used for above screenshot.
PS: New window needs to be opened for each change of date dropdown. So change date, copy the URL, open a new browser and paste the URL to test.

<form>
  <label>Column Color by range without Column name</label>
  <search id="baseTimeChart">
    <query>index=_internal sourcetype=splunkd log_level!="INFO" component!="Conf*" $tokDays$
| timechart span=1d count by component useother=f usenull=f limit=10
| eval _time=strftime(_time,"%Y-%m-%d")
    </query>
    <sampleRatio>1</sampleRatio>
  </search>
  <search base="baseTimeChart">
    <query>| transpose 0
| search column="_time"
    </query>
    <progress>
      <set token="tok1">$result.row 1$</set>
      <set token="tok2">$result.row 2$</set>
      <set token="tok3">$result.row 3$</set>
      <set token="tok4">$result.row 4$</set>
      <set token="tok5">$result.row 5$</set>
      <set token="tok6">$result.row 6$</set>
      <set token="tok7">$result.row 7$</set>
    </progress>
  </search>
  <fieldset submitButton="false" autoRun="false">
    <input type="dropdown" token="tokDays" searchWhenChanged="true">
      <label>Select 7 Days</label>
      <choice value="earliest=-7d@d latest=@d">Last 7 day</choice>
      <choice value="earliest=-1w@w latest=@w">Previous Week</choice>
      <choice value="earliest=-2w@w latest=-1w@w">Two weeks Prior</choice>
      <choice value="earliest=@w latest=now()">Current Week</choice>
      <change>
        <unset token="tok1"></unset>
        <unset token="tok2"></unset>
        <unset token="tok3"></unset>
        <unset token="tok4"></unset>
        <unset token="tok5"></unset>
        <unset token="tok6"></unset>
        <unset token="tok7"></unset>
      </change>
      <default>earliest=-2w@w latest=-1w@w</default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <title>tok1-$tok1$, tok2-$tok2$, tok3-$tok3$, tok4-$tok4$, tok5-$tok5$, tok6-$tok6$, tok7-$tok7$</title>
        <search base="baseTimeChart">
          <query>| transpose 0 header_field=_time column_name=component
| search component!="_*"</query>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">true</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="$tok1$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok2$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok3$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok4$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok5$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok6$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok7$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
      </table>
    </panel>
  </row>
</form>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

efavreau
Motivator

@niketn  I miss you, my friend. I remember this started a great bunch of conversations between us that included a hug at .conf19.

I want to give a shout out to @kaeleyt for providing my go-to solution for this problem:
https://community.splunk.com/t5/Splunk-Search/How-to-add-colors-to-a-table-for-dynamic-columns/m-p/4...

After looking further, I found this line in the documentation, https://docs.splunk.com/Documentation/Splunk/latest/Viz/TableFormatsXML:
"If you do not specify a field, the format rule is applied to the entire table. "

So the magic is not specifying a field in the line:

 

 <format type="color">

 


I also want to provide, like Niket taught me by example, to include a run-anywhere example implementing the solution.

 

<dashboard version="1.1">
<label>Erics Column Test</label>
<row>
<panel>
<title>Data Example</title>
<table>
<search>
<query>index=_internal sourcetype=splunkd log_level!=INFO earliest=-7m@m latest=now
| eval Time=strftime(_time,"%Y-%m-%d %H:%M")
| chart count as Error by component Time</query>
<earliest>-1h@h</earliest>
<latest>now</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="list">[#118832,#1182F3,#CBA700,#D94E17,#D41F1F]</colorPalette>
<scale type="threshold">0,30,70,100</scale>
</format>
</table>
</panel>
</row>
</dashboard>

 

 

###

If this reply helps you, an upvote would be appreciated.
0 Karma

niketn
Legend

@efavreau, best option is handle such scenarios with Simple XML JavaScript Extension which allows you to access SplunkJS Stack and you can apply custom code as per your needs. Such code if written properly can be reused across your App/Enterprise.

It is possible to handle this use case in Simple XML as well. But since it will involve additional code, you should also explain as to why you are trying to refrain from JavaScript Code.

Simple XML code does not seem to work if the same dashboard is refreshed with a different set of dates, it seems to work only on fresh dashboard load. Which implies you will have to provide the date selection in one dashboard and then use <link> <drilldown> option to open the dashboard in a new window.

Following code is a run anywhere dashboard example on top of Splunk's _internal index. It assumes you need to color 7 date columns. Hence sets 7 tokens (more than 7 implies setting more tokens and less than 7 implies the dates which are available will have color set).

The actual implementation depends on the SPL you are using to display 7 days of data. My example uses timechart with span of 1 day to plot count of errors for each Components (set the limit to 10, but can be increased). Then the results are reversed using transpose command. Post processing is used to run timechart command only once and then first search with transpose is used to get the date column names using row 1, row 2 ... row 7 to access _time field and assign to tokens tok1, tok2... tok7 respectively. The second search is used to display the results in tabular format.

Date field names assigned to $tok1$, $tok2$... $tok7$ are used to set the color for respective column in the displayed table dynamically. For example for $tok1$

        <format type="color" field="$tok1$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>

alt text

alt text
Following is the run anywhere dashboard example code used for above screenshot.
PS: New window needs to be opened for each change of date dropdown. So change date, copy the URL, open a new browser and paste the URL to test.

<form>
  <label>Column Color by range without Column name</label>
  <search id="baseTimeChart">
    <query>index=_internal sourcetype=splunkd log_level!="INFO" component!="Conf*" $tokDays$
| timechart span=1d count by component useother=f usenull=f limit=10
| eval _time=strftime(_time,"%Y-%m-%d")
    </query>
    <sampleRatio>1</sampleRatio>
  </search>
  <search base="baseTimeChart">
    <query>| transpose 0
| search column="_time"
    </query>
    <progress>
      <set token="tok1">$result.row 1$</set>
      <set token="tok2">$result.row 2$</set>
      <set token="tok3">$result.row 3$</set>
      <set token="tok4">$result.row 4$</set>
      <set token="tok5">$result.row 5$</set>
      <set token="tok6">$result.row 6$</set>
      <set token="tok7">$result.row 7$</set>
    </progress>
  </search>
  <fieldset submitButton="false" autoRun="false">
    <input type="dropdown" token="tokDays" searchWhenChanged="true">
      <label>Select 7 Days</label>
      <choice value="earliest=-7d@d latest=@d">Last 7 day</choice>
      <choice value="earliest=-1w@w latest=@w">Previous Week</choice>
      <choice value="earliest=-2w@w latest=-1w@w">Two weeks Prior</choice>
      <choice value="earliest=@w latest=now()">Current Week</choice>
      <change>
        <unset token="tok1"></unset>
        <unset token="tok2"></unset>
        <unset token="tok3"></unset>
        <unset token="tok4"></unset>
        <unset token="tok5"></unset>
        <unset token="tok6"></unset>
        <unset token="tok7"></unset>
      </change>
      <default>earliest=-2w@w latest=-1w@w</default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <title>tok1-$tok1$, tok2-$tok2$, tok3-$tok3$, tok4-$tok4$, tok5-$tok5$, tok6-$tok6$, tok7-$tok7$</title>
        <search base="baseTimeChart">
          <query>| transpose 0 header_field=_time column_name=component
| search component!="_*"</query>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">true</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="$tok1$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok2$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok3$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok4$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok5$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok6$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
        <format type="color" field="$tok7$">
          <colorPalette type="list">[#65A637,#D93F3C]</colorPalette>
          <scale type="threshold">10</scale>
        </format>
      </table>
    </panel>
  </row>
</form>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

efavreau
Motivator

It appears this xml solution will work. Thank you for investing in the detailed answer for me and others!

###

If this reply helps you, an upvote would be appreciated.

niketn
Legend

@efavreau, first off I am glad that this approach to use Simple XML is actually working out for you 🙂

For your point 1 and 2, either admins should be willing to deploy the static files (JavaScript/CSS) or else developers should be experienced enough with Splunk Web Framework and Splunk HTML Dashboard. So that everything (JavaScript/CSS) can be embedded within dashboard without needing additional external static files (instead of XML your dashboard will be HTML file).

As far as training is concerned, the specific solution for this problem (Table Cell Highlighting example has been part of Splunk Dashboard Examples app as long as I have know Splunk :). (The table cell color formatting option got introduced only in Splunk 6.5 onward.) Even Splunk How to Channel on Youtube video can get you started with JavaScript Extension in less than 10 minutes.

A Splunk App is just files sitting in various (specific) directories, so any Version Control software should be sufficient for code management including the static files.

No doubt Splunk has really great features for data visualizations and keeps getting better with every release and based on popular feature requests ;). However, the true potential of dashboard can be leveraged only with web development tools/techniques like JavaScript, CSS, HTML, Custom Visualization API etc. All of them are supported for Splunk App development and still turnaround time for such custom codes is still pretty less compared to any other Web Development Tools.

However, to your point if the custom development time is less but at the same time friction/delays in deployments is more then obviously Simple XML would be way better 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

mayurr98
Super Champion

efavreau
Motivator

Thank you. This would appear to be a custom solution, and not a viable one for me to implement. If this is the only option, I appreciate it. Maybe a different answer will be presented.

###

If this reply helps you, an upvote would be appreciated.
0 Karma

mayurr98
Super Champion

lets see wait for it..if anyone gives a better solution then well and good. Else accept the one which helped you to close this question.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...