How to improve my approach of building a panel with two searches that only differ in time modifiers, then calculate the ratio between both results?

[imarinkov]

I am a newbie to Splunk and have a question on best approach.

I am doing a group of panels with two panels containing single value views and one with an HTML view. The single views do the same search with only the earliest and latest modifiers (feel free to correct me on the term) being different. They calculate single values I wish to find the ratio between.
The HTML view panel contains said ratio.

This is an example of a pair of searches:

<panel>
  <title>Yesterday</title>
  <single>
    <search id="metric_yesterday">
      <query>index="myindex" source="*.log" | where $first_refinement$ | where $second_refinement$ | where $third_refinement$ | fields field1 |  stats AVG(field1) as f1_yesterday  | eval f1_yesterday = round(f1_yesterday,0)</query>
      <earliest>-1d@d</earliest>
      <latest>@d</latest>
    </search>
    <!-- options omitted for brevity -->
  </single>
</panel>
<panel>
  <title>Last week</title>
  <single>
    <search id="metric_week">
      <query>index="myindex" source="*.log" | where $first_refinement$ | where $second_refinement$ | where $third_refinement$ | fields field1 |  stats AVG(field1) as f1_last_week  | eval f1_last_week = round(f1_last_week,0)</query>
      <earliest>-w@w+1d</earliest>
      <latest>-w@w+8d</latest>
    </search>
    <!-- options omitted for brevity -->
  </single>
</panel>

In order to save the results of the searches for reuse, I am using JavaScript. Basically,

var firstSearch = splunkjs.mvc.Components.get("metric_yesterday");
var secondSearch = splunkjs.mvc.Components.get("metric_week");

I wait for data to arrive and then save the results to tokens:

firstSearch.data("results").on("data", function (results) {
       var result = results._data.rows[0][0];
        tokens.set("first_search_result", result);
        if (tokens.get('second_search_result') != undefined) {
           calculateRatio();
        }
});

I have a similar function for the second value. When both tokens have been set, the calculateRatio() function will be called to calculate and display the ratio.

What I refer to as ratio is the percent the second value is greater than the first one. Like this:

ratio = 100 - Math.round((second_field/ first_field) * 100);

To show the ratio, I use jQuery to set the value of the HTML view. I am using a div ID that matches the ID of the token that contains the ratio, in order to make the task easier.

My approach seems a bit overcomplicated and I am not sure it's the most efficient one. Hence, I'd like to ask the following questions:

• I have two near identical searches - is there some way I can have one and reuse it with different time modifiers?
• Is there a better way of saving search results and reusing them to calculate the ratio?
• Since I am saving the ratio in a token, is there an XML-only way to have the single value view show its value without extra JS? The token would obviously be undefined initially and the view will need an update as the ratio calculation is complete
• Examples show using trendInterval to display a trend. Would this be a better approach to accomplish what I want?

[alacercogitatus · Accepted solution]

So these searches look like they can be done in a single search.

index="myindex" source="*.log"  $first_refinement$ $second_refinement$  $third_refinement$ earliest=-w@w+1d latest=-w@w+8d |fields field1| stats avg(field1) as week |appendcols [ search  index="myindex" source="*.log"  $first_refinement$ $second_refinement$  $third_refinement$ earliest=-1d@d latest=@d  | fields field1  | stats avg(field1) as yesterday ]  | eval ratio = 100 - round((week / yesterday) * 100, 0)

Now you can have a single panel in a Simple XML Form (to capture the refinements).

[imarinkov]

Thank you for the answer, @alacercogitatus! I will try it out and let you know of the outcome.

[alacercogitatus]

What do you mean "ratio"? Also, please include the searches, there might be an SPL way to calculate your "ratio"s without having to resort to JavaScript and HTML Views.

[imarinkov]

Hi, @alacercogitatus, thank you for your comment! I have updated the question to include samples of the searches and how the ratio is calculated. Tell me if you need any additional info.