topic Re: how to change the query dynamically based on the input in Dashboards & Visualizations

how to change the query dynamically based on the input

xvxt006 — Sun, 22 Mar 2015 23:18:50 GMT

Hi,

I am trying the drill down feature in splunk. It uses a static query to get the sourcetype and then we can drill-down based on the sourcetype selected from the results.

Is it possible to use a dynamic query - For example in the text field at the top user can enter a metric, say clientip. Then i would get the count by clientip in the master and when i drill down, it would get detailed metrics. In this way i am not restricting only to sourcetype but i can enter clientip, useragent or any other metric based on the count.

I am using the below code but drill down is not working..any suggestions please...

<label>In-Page Drilldown with Perma-linking</label>


    <!--
        Enter a metric to drill down
     -->
    <input type="text" token="metric" searchWhenChanged="true" />



<fieldset submitButton="false">
    <!--
        Create an input to store the drilldown value. It will be hidden using custom javascript when
        the dashboard is loaded.
     -->
    <input type="text" token="value" searchWhenChanged="true" />
</fieldset>
<row>
    <table id="master">
        <title>Master</title>
        <searchString>sourcetype=access_combined_wcookie host=pr*| stats count by $metric$</searchString>
        <earliestTime>-60m@m</earliestTime>
        <latestTime>now</latestTime>
        <!-- Set the type of of drilldown, since we will always consume the same field, use row-->
        <option name="drilldown">row</option>
        <drilldown>
            <!-- Use set to specify the new token to be created.
                 Use any token from the page or from the click event to produce the value needed. -->
            <set token="value">$row.metric$</set>
            <!-- If we also set the form.sourcetype the input will get updated too -->
            <set token="form.value">$row.metric$</set>
        </drilldown>
    </table>
</row>
<row>
    <!-- depends is the way we tell the content to only show when the token has a value.
         Hint: use comma separated values if the element requires more than one token. -->
    <chart id="detail" depends="$value$">
        <title>Detail: $value$</title>
        <searchTemplate>sourcetype=access_combined_wcookie host=pr* $value$=$value.row$| timechart count</searchTemplate>
        <earliestTime>-60m@m</earliestTime>
        <latestTime>now</latestTime>
    </chart>
</row>

Re: how to change the query dynamically based on the input

xvxt006 — Mon, 23 Mar 2015 03:41:13 GMT

i have tried this but the row value is not showing up the actual value in the drill down.

<label>In-Page Drilldown with Perma-linking</label>


    <!--
        Enter a metric to drill down
     -->
    <input type="text" token="metric" searchWhenChanged="true" />
 <input type="text" token="value" searchWhenChanged="true" />




<row>
    <table id="master">
        <title>Master</title>
        <searchString>sourcetype=access_combined_wcookie host=pr*| stats count by $metric$</searchString>
        <earliestTime>-60m@m</earliestTime>
        <latestTime>now</latestTime>
        <!-- Set the type of of drilldown, since we will always consume the same field, use row-->
        <option name="drilldown">row</option>
        <drilldown>
            <!-- Use set to specify the new token to be created.
                 Use any token from the page or from the click event to produce the value needed. -->
                <set token="value">$row.($metric$)$</set>
            <!-- If we also set the form.sourcetype the input will get updated too -->
            <set token="form.value">$row.($metric$)$</set>
        </drilldown>
    </table>
</row>
<row>
    <!-- depends is the way we tell the content to only show when the token has a value.
         Hint: use comma separated values if the element requires more than one token. -->
    <chart id="detail" depends="$value$">
        <title>Detail: $value$</title>
        <searchTemplate>sourcetype=access_combined_wcookie host=pr* $metric$=$value$| timechart count</searchTemplate>
        <earliestTime>-60m@m</earliestTime>
        <latestTime>now</latestTime>
    </chart>
</row>

Re: how to change the query dynamically based on the input

ramdaspr — Mon, 23 Mar 2015 05:49:08 GMT

You can try with

<drilldown>
             <!-- Use set to specify the new token to be created.
                  Use any token from the page or from the click event to produce the value needed. -->
                 <set token="metricname">$click.value$</set>
                 <set token="metricvalue">$row.count$</set>
             <!-- If we also set the form.sourcetype the input will get updated too -->

             <set token="form.value">$row.($metric$)$</set>
         </drilldown>

and sourcetype=access_combined_wcookie host=pr* $metricname$=$metricvalue$| timechart count

I tried something similar in a test dashboard and it seems to work fine.

Re: how to change the query dynamically based on the input

gyslainlatsa — Mon, 23 Mar 2015 10:10:33 GMT

hi xvxt006,
here's what I think to be able to solve your problem.
at the end of the request from the master, you can add the following line: | rename $metric$ AS field, which will allow you to rename all the fields that you are entering into metric with the same name field that will be easy to manage. Here is the full source code and it works 90 percent

<form>
<label>In-Page Drilldown with Perma-linking</label>


 <fieldset submitButton="false">

    <!--
         Enter a metric to drill down
      -->
     <input type="text" token="metric" searchWhenChanged="true" />


     <!--
         Create an input to store the drilldown value. It will be hidden using custom javascript when
         the dashboard is loaded.
      -->
     <input type="text" token="value" searchWhenChanged="true" />
 </fieldset>
 <row>
     <table id="master">
         <title>Master: (field=$metric$)</title>
         <searchString>sourcetype=access_combined_wcookie | stats count by $metric$ |rename $metric$ AS field </searchString>
         <earliestTime>0</earliestTime>
         <latestTime>now</latestTime>
         <!-- Set the type of of drilldown, since we will always consume the same field, use row-->
         <option name="drilldown">row</option>
         <drilldown>
             <!-- Use set to specify the new token to be created.
                  Use any token from the page or from the click event to produce the value needed. -->
             <set token="value">$row.field$</set>
             <!-- If we also set the form.sourcetype the input will get updated too -->
             <set token="form.value">$row.field$</set>
         </drilldown>
     </table>
 </row>
 <row>
     <!-- depends is the way we tell the content to only show when the token has a value.
          Hint: use comma separated values if the element requires more than one token. -->
     <chart id="detail" depends="$value$">
         <title>Detail: $value$</title>
         <searchTemplate>sourcetype=access_combined_wcookie $metric$=$value$ | timechart count</searchTemplate>
         <earliestTime>0</earliestTime>
         <latestTime>now</latestTime>
     </chart>
 </row>

</form>

if you use splunk 6.2.x, don't forget to replace earliestTime or latestTime with earliest and latest
try it and let me know if this solved your problem.
please forgive my english.

Re: how to change the query dynamically based on the input

xvxt006 — Mon, 23 Mar 2015 12:50:13 GMT

Thanks both the solutions are working. One question, based on the metric i have selected, can change the query too? meaning if i have response time, count would not make sense right. i would want timechart avg(time) something like that.

Re: how to change the query dynamically based on the input

xvxt006 — Mon, 28 Sep 2020 19:19:40 GMT

Thank you. This worked with small change for me. sourcetype=access_combined_wcookie host=pr* $metric$="$metricname$"

Re: how to change the query dynamically based on the input

xvxt006 — Mon, 23 Mar 2015 13:00:53 GMT

Looks like i could not award points if i don't select it as an answer. I wish i could do that. Anyways thanks for your help.

Re: how to change the query dynamically based on the input

ramdaspr — Mon, 23 Mar 2015 23:06:49 GMT

changed to an answer instead..

Re: how to change the query dynamically based on the input

xvxt006 — Mon, 23 Mar 2015 23:27:52 GMT

i also have another question along this...if i want to change the query based on the metric..for example if i have a metric which uses avg instead of a count (for example response time), is it possible to change the query to use timechart avg(metric) instead of timechart count?

Re: how to change the query dynamically based on the input

ramdaspr — Mon, 23 Mar 2015 23:44:49 GMT

timechart avg($metric$) should work just fine if thats what you are looking for.

Re: how to change the query dynamically based on the input

xvxt006 — Tue, 24 Mar 2015 01:22:47 GMT

True but how to select which query to execute? I am lookig for kind of "if" clause where I can say if these are the metrics execute this query else execute this query

Re: how to change the query dynamically based on the input

gyslainlatsa — Tue, 24 Mar 2015 12:54:34 GMT

hi xvxt006,
this is my email gyslainko@gmail.com, I come from Cameroon and I'm going to look at what you specify below. in case of further collaboration, you can write me.
please forgive my english.