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
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>
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>
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;
}
}
});
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
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?
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.
Then the first example I posted should work. I will double check.
What version of Splunk are developing on?
Enterprise 6.4
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>
Note the use of finalized
instead of done
.
This does grab only the top row for me in 6.4
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.
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.
I have used A as an example. Name could be different. So sorting the column won't solve my problem.
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.
Is this table on a dashboard? How do you intend to use the stored value?
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.
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?
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.