topic Re: How do I edit my XML to drill down from a table to a timechart? in Splunk Search

How do I edit my XML to drill down from a table to a timechart?

dbcase — Thu, 04 Aug 2016 02:25:59 GMT

Hi,

First time doing drill downs, so pardon the newbie question. I'm having a tough time grasping the drilldown concept.

I have this XML:

<row>
    <panel>
      <title>Connectivity - June 2016 to Date</title>
      <table>
        < title >Cell Connectivity< /title  >
        < search >
          <query >index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" |eval mso = upper(substr(mso,1,1)).lower(substr(mso,2)) |chart sparkline(avg(stat_val)) as Trend first(stat_val) as "Current Cell Connectivity %" by mso</query>
          <earliest>0</earliest>
          <latest></latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers" >false</option>
        <option name="dataOverlayMode" >none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
        <format type="sparkline" field="Trend">
          <option name="lineColor">#5379af</option>
          <option name="fillColor">#CCDDFF</option>
          <option name="lineWidth">1</option>
          <option name="maxSpotColor">#00FF00</option>
          <option name="minSpotColor">#FF0000</option>
          <option name="spotRadius">3</option>
          <option name="height">25px</option>
        </format>
        <format field="Trend" type="sparkline">
          <option name="spotRadius">3</option>
          <option name="minSpotColor">#FF0000</option>
          <option name="maxSpotColor">#00FF00</option>
          <option name="height">25px</option>
          <option name="fillColor">#CCDDFF</option>
          <option name="lineWidth">1</option>
          <option name="lineColor">#5379af</option>
        </format>
        <format field="Trend" type="sparkline">
          <option name="spotRadius">3</option>
          <option name="minSpotColor">#FF0000</option>
          <option name="maxSpotColor">#00FF00</option>
          <option name="height">25px</option>
          <option name="fillColor">#CCDDFF</option>
          <option name="lineWidth">1</option>
          <option name="lineColor">#5379af</option>
        </format>
        <format field="Trend" type="sparkline">
          <option name="spotRadius">3</option>
          <option name="minSpotColor">#FF0000</option>
          <option name="maxSpotColor">#00FF00</option>
          <option name="height">25px</option>
          <option name="fillColor">#CCDDFF</option>
          <option name="lineWidth">1</option>
          <option name="lineColor">#5379af</option>
        </format>
        <format field="Trend" type="sparkline">
          <option name="spotRadius">3</option>
          <option name="minSpotColor">#FF0000</option>
          <option name="maxSpotColor">#00FF00</option>
          <option name="height">25px</option>
          <option name="fillColor">#CCDDFF</option>
          <option name="lineWidth">1</option>
          <option name="lineColor">#5379af</option>
        </format>
        <format field="Trend" type="sparkline">
          <option name="spotRadius">3</option>
          <option name="minSpotColor">#FF0000</option>
          <option name="maxSpotColor">#00FF00</option>
          <option name="height">25px</option>
          <option name="fillColor">#CCDDFF</option>
          <option name="lineWidth">1</option>
          <option name="lineColor">#5379af</option>
        </format>
        <format field="Trend" type="sparkline">
          <option name="spotRadius">3</option>
          <option name="minSpotColor">#FF0000</option>
          <option name="maxSpotColor">#00FF00</option>
          <option name="height">25px</option>
          <option name="fillColor">#CCDDFF</option>
          <option name="lineWidth">1</option>
          <option name="lineColor">#5379af</option>
        </format>
      </table>

    </panel>
  </row>

I'd like to be able to click on any one of the resulting rows to drill down to a timechart that runs this query

index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" |eval mso = upper(substr(mso,1,1)).lower(substr(mso,2)) |timechart (avg(stat_val)) as Trend by mso

Re: How do I edit my XML to drill down from a table to a timechart?

dbcase — Thu, 04 Aug 2016 02:29:51 GMT

Wow, that really didn't format well.... Let's try that again

Here is the XML (with all the options removed this time)

<panel>
  <title>Connectivity - June 2016 to Date</title>
  <table>
    <title>Cell Connectivity</title>
    <search>
      <query>index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" |eval mso = upper(substr(mso,1,1)).lower(substr(mso,2)) |chart sparkline(avg(stat_val)) as Trend first(stat_val) as "Current Cell Connectivity %" by mso</query>
      <earliest>0</earliest>
      <latest></latest>
    </search>

  </table>

</panel>

Re: How do I edit my XML to drill down from a table to a timechart?

sundareshr — Thu, 04 Aug 2016 02:33:16 GMT

Is the query for your timechart dependent on any values from the table?

Re: How do I edit my XML to drill down from a table to a timechart?

dbcase — Thu, 04 Aug 2016 02:36:02 GMT

Yes it would be. I guess it would read something like this

index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" %variable.from.table% |eval mso = upper(substr(mso,1,1)).lower(substr(mso,2)) |timechart (avg(stat_val)) as Trend by mso

Re: How do I edit my XML to drill down from a table to a timechart?

sundareshr — Thu, 04 Aug 2016 02:49:25 GMT

I'm not quite sure which value you want, from the table, but hopefully, this will give you some idea how it is done

*XML for the table

     <panel>
       <title>Connectivity - June 2016 to Date</title>
       <table>
         <title>Cell Connectivity</title>
         <search>
           <query>index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" |eval mso = upper(substr(mso,1,1)).lower(substr(mso,2)) |chart sparkline(avg(stat_val)) as Trend first(stat_val) as "CurrentCellConnectivity" by mso</query>
           <earliest>0</earliest>
           <latest></latest>
         </search>
        <drilldown>
            <set token="tablevariable">$row.CurrentCellConnectivity$</set>
           </drilldown>
       </table>
     </panel>

*Query for timechart*

  index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" "$tablevariable$" |eval mso = upper(substr(mso,1,1)).lower(substr(mso,2)) |timechart (avg(stat_val)) as Trend by mso

http://docs.splunk.com/Documentation/Splunk/6.4.2/Viz/tokens#Define_tokens_for_conditional_operations_with_the_.3Cdrilldown.3E_element

Re: How do I edit my XML to drill down from a table to a timechart?

dbcase — Thu, 04 Aug 2016 02:51:56 GMT

Good question. It would be the mso field

Re: How do I edit my XML to drill down from a table to a timechart?

dbcase — Thu, 04 Aug 2016 02:53:15 GMT

Where would the query for timechart go in the XML? Sorry, very newbie at this 🙂

Re: How do I edit my XML to drill down from a table to a timechart?

sundareshr — Thu, 04 Aug 2016 03:01:06 GMT

The timechart will new new panel, like the table, create a new panel (Add Panel) for timechart and use the query.

And for the drilldown in your table, if you want the name of the mso column, use this instead $click.name2$ So your set token will be

<set token="tablevariable">$click.name2$</set>

Re: How do I edit my XML to drill down from a table to a timechart?

dbcase — Thu, 04 Aug 2016 15:02:18 GMT

Ok, I'm a bit closer but still not there yet

I have the below but 2 problems. #1 there is a blank area that says "awaiting input" or something like that and more importantly when I click on a row, there is problem #2, which is it doesn't return anything. Sorry for all the hand holding on this.....

Customer Statistics

<panel>
  <title>Connectivity - June 2016 to Date</title>
  <table>
    <title>Cell Connectivity</title>
    <search>
      <query>index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" |eval mso = upper(substr(mso,1,1)).lower(substr(mso,2)) |chart sparkline(avg(stat_val)) as Trend first(stat_val) as "Current Cell Connectivity %" by mso|rename mso as Customer</query>
      <earliest>0</earliest>
      <latest></latest>
    </search>
    <drilldown>
          <set token="tablevariable">$click.name2$</set>
    </drilldown>

  </table>


    <chart>
      <search>
        <query>
     index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" $tablevariable$ |eval mso = lower(substr(mso,1,1)).lower(substr(mso,2)) |timechart avg(stat_val) as Trend 
        </query>
      </search>
    </chart>
</panel>

Re: How do I edit my XML to drill down from a table to a timechart?

sundareshr — Thu, 04 Aug 2016 15:29:35 GMT

The "Awaiting input" is because you have a token $tablevariable$ in your query that hasn't been set. That gets set, when you click on a row. To fix it, you can change your panel tag for the timechart to this

<panel depends="$tablevariable$">

What this does is, it will keep the panel hidden till the token value is set.

For #2, try this change to your query

index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" |eval mso = lower(substr(mso,1,1)).lower(substr(mso,2)) |where mso="$tablevariable$" | timechart avg(stat_val) as Trend

Re: How do I edit my XML to drill down from a table to a timechart?

dbcase — Tue, 29 Sep 2020 10:28:28 GMT

Hmmm, ok just focusing on #2 for now

Updated code looks like the below, but still no results (very strange). What am I missing? Is there a way to display the tablevariable token so I can verify the contents?

Customer Statistics

 <title>Cell Connectivity</title>
 <search>
   <query>index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" |chart sparkline(avg(stat_val)) as Trend first(stat_val) as "Current Cell Connectivity %" by mso</query>
   <earliest>0</earliest>
   <latest></latest>
 </search>

<drilldown>
       <set token="tablevariable">$click.name2$</set>
 </drilldown>

 <chart>
   <search>
     <query>

index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" |where mso="$tablevariable$" | timechart avg(stat_val) as Trend

Re: How do I edit my XML to drill down from a table to a timechart?

sundareshr — Thu, 04 Aug 2016 16:25:50 GMT

When you hover over the timechart panel. you will see a magnifying glass on the bottom left corner of the panel. Click on that. It will open a new window with the search. You can see the value for the token there

Re: How do I edit my XML to drill down from a table to a timechart?

dbcase — Tue, 29 Sep 2020 10:28:33 GMT

Ah ha!

The search query is using the field name instead of the content. mso should be something like bhn or comcast

index=mso_statistics sourcetype=ic_connectivity_5min-too_small stat_name = "cell" |where mso="mso" | timechart avg(stat_val) as Trend

Re: How do I edit my XML to drill down from a table to a timechart?

dbcase — Thu, 04 Aug 2016 16:33:24 GMT

changing click.name2 to click.value2 seems to do the trick for the search!

Re: How do I edit my XML to drill down from a table to a timechart?

sundareshr — Thu, 04 Aug 2016 17:22:41 GMT

Perfect. Please accept this to close out the question