Splunk Search

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

dbcase
Motivator

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

sundareshr
Legend

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_operation...

View solution in original post

sundareshr
Legend

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_operation...

dbcase
Motivator

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

0 Karma

dbcase
Motivator

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

0 Karma

sundareshr
Legend

Perfect. Please accept this to close out the question

0 Karma

dbcase
Motivator

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


0 Karma

sundareshr
Legend

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

0 Karma

dbcase
Motivator

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>
0 Karma

sundareshr
Legend

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
0 Karma

dbcase
Motivator

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

0 Karma

sundareshr
Legend

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>
0 Karma

dbcase
Motivator

Good question. It would be the mso field

0 Karma

sundareshr
Legend

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

0 Karma

dbcase
Motivator

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
0 Karma

dbcase
Motivator

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>
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...