Dashboards & Visualizations

How to store values in token from specific cell of a table ?

AKG1_old1
Builder

Hello,

We generate table of data and want to extract data from specific cell of table and store it in token.

Example Table:
Column1 Average Standard Deviation Median
All a b c
A x y z
B e f g

I want to extract the value of Median of Row A i.e. "z" and store it to some token.

Regards
Ankit

Tags (1)
0 Karma
1 Solution

sundareshr
Legend

Here's how you can get it to work. First you need to add the value you want to a hidden column in your results set. You can do this by using the eventstats command. Then in your use the $result.hiddenfield$ token to extract that value. Here is a run-anywhere sample. You should be able to modify the query to your needs.

<dashboard>
  <label>Test</label>
  <row>
    <panel>
      <table>
        <title>Value from first row: $e$</title>
        <search>
          <query>index=_internal | stats count by sourcetype | eventstats values(eval(if(sourcetype="splunkd", count, null()))) as _c</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <finalized>
            <set token="e">$result._c$</set>
          </finalized>
        </search>
        <option name="wrap">undefined</option>
        <option name="rowNumbers">undefined</option>
        <option name="drilldown">row</option>
      </table>
    </panel>
  </row>
</dashboard>

View solution in original post

sundareshr
Legend

Here's how you can get it to work. First you need to add the value you want to a hidden column in your results set. You can do this by using the eventstats command. Then in your use the $result.hiddenfield$ token to extract that value. Here is a run-anywhere sample. You should be able to modify the query to your needs.

<dashboard>
  <label>Test</label>
  <row>
    <panel>
      <table>
        <title>Value from first row: $e$</title>
        <search>
          <query>index=_internal | stats count by sourcetype | eventstats values(eval(if(sourcetype="splunkd", count, null()))) as _c</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <finalized>
            <set token="e">$result._c$</set>
          </finalized>
        </search>
        <option name="wrap">undefined</option>
        <option name="rowNumbers">undefined</option>
        <option name="drilldown">row</option>
      </table>
    </panel>
  </row>
</dashboard>

Vachel
Explorer
Great solutions! It also helped me, thanks Sundareshr
0 Karma

rjthibod
Champion

You can either do this a couple of ways based on the use case

In JS
If you want to do this programatically, you will have to write custom Javascript. Let's assume you have a table defined in XML and the search is called "median_search".

   <row>
     <panel>
       <table>
         <search id="median_search">
           <query> ... | your search | ... </query>
         </search>
       </table>
     </panel>
   </row>

The corresponding Javascript to get the median value would look something like this,

    var median_search = mvc.Components.getInstance("median_search");

    var searchResults = median_search.data('results', {
      output_mode: 'json_rows',
      count: 0
    });

    searchResults.on("data", function() {
      if (searchResults.hasData()) {
         var results = searchResults.data().rows;
         var length = results.length;
         var column1 = searchResults.data().fields.indexOf("Column1");
         var medianColumn = searchResults.data().fields.indexOf("Median");
         var searchTerm = "The Column1 VALUE YOU ARE LOOKING FOR";

        for (var j = 0; j < length; j++) {
          if (results[j][column1] === searchTerm) {
            submittedTokenModel.set("median_token", parseFlow(results[j][medianColumn]));
            break;
          }
      }
});

AKG1_old1
Builder

Thank you for reply but I think problem of using $row.Median$ is that it will store the median of all the rows.

I need to extract only for one row i.e. A.

for example:

$A.Median$

I know the above example will not work. But can we do similar like that.

Thanks
Ankit

0 Karma

rjthibod
Champion

The above example should only grab the value of the first row per the specification link. Are you seeing it do otherwise?

Regardless, the first example only works if you want the Median value for the first row. Do you know if you always want the first row, or do you need a flexible way to chose a specific row? If the latter, do you need the user to select the row or should software decide which row?

0 Karma

AKG1_old1
Builder

I want the second option. i don't want user to decide which row.

Values in 1st column is constant i.e. A. Every time I want to store the value of median for A.

0 Karma

rjthibod
Champion

Then the first example I posted should work. I will double check.

What version of Splunk are developing on?

0 Karma

AKG1_old1
Builder

Enterprise 6.4

0 Karma

rjthibod
Champion

OK, I got it working on 6.4.

  <row>
    <panel>
      <table>
        <search>
          <query> ... | your search | ... </query>
          <finalized>
                <set token="median_token">$result.Median$</set>
          </finalized>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="count">10</option>
      </table>
    </panel>
  </row>
0 Karma

rjthibod
Champion

Note the use of finalized instead of done.

This does grab only the top row for me in 6.4

0 Karma

AKG1_old1
Builder

Its not working, currently, I am using the same thing. its showing the median of 1st row. I want to store median for specific value. It can be displayed on different rows.

0 Karma

rjthibod
Champion

Oh, I misunderstood you when you said "Every time I want to store the value of median for A" taking that to mean A would be the first row.

Unless you are willing to sort the results so that the result for "A" is on top, then you will have to use JavaScript if you plan to show other results.

I can share the JavaScript if you need that.

0 Karma

AKG1_old1
Builder

I have used A as an example. Name could be different. So sorting the column won't solve my problem.

0 Karma

rjthibod
Champion

Then you need to share how you plan to identify which row to pull the median value, because that has to get translated into the JS that extracts the value from the multi-dimensional array that stores the table of results.

0 Karma

sundareshr
Legend

Is this table on a dashboard? How do you intend to use the stored value?

0 Karma

AKG1_old1
Builder

yes, we run query to populate these tables on dashboard.

I want to grep that value of specific cell. So i can store that data to some csv file using token.

0 Karma

sundareshr
Legend

How do you determine the row you want to get the value from? Is that a user selection or pre-determined? Also is the Median pre-calculated in the table or would that need to be calculated based on the values returned?

0 Karma

AKG1_old1
Builder

the first column is constant. from above example: I want the value of median for A.

For Median, I have a query which calculate it and populate in the table.

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 ...