Dashboards & Visualizations

how to change the query dynamically based on the input

xvxt006
Contributor

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>
Tags (1)
0 Karma
1 Solution

gyslainlatsa
Motivator

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.

View solution in original post

xvxt006
Contributor

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

0 Karma

xvxt006
Contributor

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.

0 Karma

ramdaspr
Contributor

changed to an answer instead..

0 Karma

gyslainlatsa
Motivator

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.

View solution in original post

xvxt006
Contributor

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.

0 Karma

gyslainlatsa
Motivator

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.

0 Karma

ramdaspr
Contributor

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.

xvxt006
Contributor

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

0 Karma

xvxt006
Contributor

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?

0 Karma

ramdaspr
Contributor

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

0 Karma

xvxt006
Contributor

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>
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!