Dashboards & Visualizations

Need help doing drilldown from linechart

3DGjos
Communicator

Hello, I need to make a drilldown from a linechart (with a timespan of 7d), the thing is that the drilldown needs to go whenever (day, in time) the user clicked. The linechart also has 3 lines coming from a few counts. The drilldown has to link to 3 different dashboards, (which I already have) depending on what line the user clicks on. I Only managed to achieve a drilldown but when clicking on the Legends.

Here is my timechart so far: (in the next version i will switch to tstats):

   <row>
    <panel>
      <chart>
        <search>
          <query> index=myindex action=failure
     | timechart count AS count1 span=1d
     | appendcols
        [search index=myindex action=blocked
         | timechart count AS count2 span=1d]
    | appendcols [ search index=myindex action=success OR action=failure
    | lookup mylookupforspecialusers user OUTPUT group
    | search `time_restricted_groups`
    | eval day = strftime(_time, "%A")
    | eval hour = strftime(_time, "%H")
    | eval ilicit_time = case((day="Sunday" OR day ="Saturday"),1 ,(hour  >= 18), 1, (hour <= 8),1, 1==1, null())
    | timechart span=1d count(ilicit_time) AS count3
    | appendcols [ search index=myindex OR index=myindex2 action=success   
    | eventstats dc(src) AS distinct     by user 
    | where distinct>1
    | timechart span=1d dc(user) AS count4]]
    | eval count5 = count3+count4
    | timechart span=1d values(count1) AS "Failed logins" values(count2) AS "Total blocked accounts" values(count5) AS "User behavior"
  <earliest>-7d@h</earliest>
          <latest>now</latest>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">collapsed</option>
        <option name="charting.axisTitleY.visibility">collapsed</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">auto</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">line</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">zero</option>
        <option name="charting.chart.showDataLabels">all</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">1</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">1</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisStart</option>
        <option name="charting.legend.mode">seriesCompare</option>
        <option name="charting.legend.placement">top</option>
 <option name="charting.lineWidth">7</option>
        <option name="height">316</option>
        <option name="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <drilldown target="NewWindow">
          <condition field="Failed logins">
            <link>/app/myapp/BCRA-DSB-009-mydashboard1?form.limit=$click.value2$</link>
          </condition>
          <condition field="Total blocked accounts">
            <link>/app/myapp/mydashboard2?form.limit=$click.value2$</link>
          </condition>
          <condition field="User behavior">
            <link>/app/myapp/mydashboard3?form.limit=$click.value2$</link>
          </condition>
        </drilldown>
      </chart>
    </panel>
  </row>   

I need help with the XML and the tokens, specially when passing time to the 3 dashboards from the 3 lines, over time. For example, if I mouseover and click on the first line on the segment corresponding to "Mon 22", the drilldown has to re-direct to the line's corresponding dashboard, and the info loaded on it has to correspond to that day.

Thanks!

Thanks!!

0 Karma
1 Solution

niketnilay
Legend

@3DGjos , your timechart produces results with _time as Epoch time hence conversion with strptime is not required. I have created the following run anywhere example with Splunk's _internal index that seems to work fine for me.

        <drilldown>
           <eval token="tokEarliest">$row._time$</eval>
           <eval token="tokLatest">$tokEarliest$ + $row._span$</eval>
        </drilldown>

PS: You should consider using single main search to pull all action i.e. success and failure and then use query to segregate data rather than using appendcols which will have subsearch limitation.
For the following example I am showing string time in panel header to confirm that data time window is correct as per timechart series clicked. If you really need string time then you may have to configure your Account time zone or adjust your current time zone (for example Indian Standard Time is 5 hours 30 minutes ahead of UTC, so I had to adjust -19800 seconds for calculating tokEarliestString and tokLatestString using eval. (Read about this behavior of eval in dashboard token which uses client timezone: https://docs.splunk.com/Documentation/Splunk/latest/Viz/tokens#Define_token_filtering_and_formatting). For the simplicity of code I have removed the -19800 after taking the screenshot.

alt text

Following is the simple XML dashboard code.

<dashboard>
  <label>Timechart Drilldown</label>
  <row>
    <panel>
      <title>Click Series for drilldown</title>
      <chart>
        <search>
          <query>index=_internal log_level=ERROR 
| timechart span=1h count as ERROR
| appendcols 
    [| search index=_internal log_level=WARN 
    | timechart span=1h count as WARN]</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <drilldown>
           <eval token="tokEarliest">$row._time$</eval>
           <eval token="tokLatest">$tokEarliest$ + $row._span$</eval>
           <eval token="tokEarliestString">strftime($tokEarliest$,"%Y-%m-%d %H:%M:%S")</eval>
           <eval token="tokLatestString">strftime($tokLatest$,"%Y-%m-%d %H:%M:%S")</eval>
           <set token="tokClickedField">$click.name2$</set>
        </drilldown>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">line</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
  <row depends="$tokClickedField$">
    <panel>
      <title>$tokResultCount$ results for $tokClickedField$ between $tokEarliestString$ - $tokLatestString$</title>
      <chart>
        <search>
          <query>index=_internal log_level=$tokClickedField$</query>
          <earliest>$tokEarliest$</earliest>
          <latest>$tokLatest$</latest>
          <sampleRatio>1</sampleRatio>
          <progress>
            <set token="tokResultCount">$job.resultCount$</set>
          </progress>
        </search>
      </chart>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketnilay
Legend

@3DGjos , your timechart produces results with _time as Epoch time hence conversion with strptime is not required. I have created the following run anywhere example with Splunk's _internal index that seems to work fine for me.

        <drilldown>
           <eval token="tokEarliest">$row._time$</eval>
           <eval token="tokLatest">$tokEarliest$ + $row._span$</eval>
        </drilldown>

PS: You should consider using single main search to pull all action i.e. success and failure and then use query to segregate data rather than using appendcols which will have subsearch limitation.
For the following example I am showing string time in panel header to confirm that data time window is correct as per timechart series clicked. If you really need string time then you may have to configure your Account time zone or adjust your current time zone (for example Indian Standard Time is 5 hours 30 minutes ahead of UTC, so I had to adjust -19800 seconds for calculating tokEarliestString and tokLatestString using eval. (Read about this behavior of eval in dashboard token which uses client timezone: https://docs.splunk.com/Documentation/Splunk/latest/Viz/tokens#Define_token_filtering_and_formatting). For the simplicity of code I have removed the -19800 after taking the screenshot.

alt text

Following is the simple XML dashboard code.

<dashboard>
  <label>Timechart Drilldown</label>
  <row>
    <panel>
      <title>Click Series for drilldown</title>
      <chart>
        <search>
          <query>index=_internal log_level=ERROR 
| timechart span=1h count as ERROR
| appendcols 
    [| search index=_internal log_level=WARN 
    | timechart span=1h count as WARN]</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <drilldown>
           <eval token="tokEarliest">$row._time$</eval>
           <eval token="tokLatest">$tokEarliest$ + $row._span$</eval>
           <eval token="tokEarliestString">strftime($tokEarliest$,"%Y-%m-%d %H:%M:%S")</eval>
           <eval token="tokLatestString">strftime($tokLatest$,"%Y-%m-%d %H:%M:%S")</eval>
           <set token="tokClickedField">$click.name2$</set>
        </drilldown>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">line</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
  <row depends="$tokClickedField$">
    <panel>
      <title>$tokResultCount$ results for $tokClickedField$ between $tokEarliestString$ - $tokLatestString$</title>
      <chart>
        <search>
          <query>index=_internal log_level=$tokClickedField$</query>
          <earliest>$tokEarliest$</earliest>
          <latest>$tokLatest$</latest>
          <sampleRatio>1</sampleRatio>
          <progress>
            <set token="tokResultCount">$job.resultCount$</set>
          </progress>
        </search>
      </chart>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

3DGjos
Communicator

@niketnilay thanks a lot! it worked!!

I followed your example and finally got it, or almost. I replaced the drilldown of your example with my link and worked like a charm, like this:

<drilldown target="NewWindow">
         <condition field="Failed logins">
             <eval token="tokEarliest">$row._time$</eval>
          <eval token="tokLatest">$tokEarliest$ + $row._span$</eval>
          <eval token="tokEarliestString">strftime($tokEarliest$,"%Y-%m-%d %H:%M:%S")</eval>
          <eval token="tokLatestString">strftime($tokLatest$,"%Y-%m-%d %H:%M:%S")</eval>
          <set token="tokClickedField">$click.name2$</set>
            <link>
              <![CDATA[/app/myapp/mydrilldowndashboard?form.time.earliest=$tokEarliest$&form.time.latest=$tokLatest$]]>
            </link>
          </condition>

And it worked just fine, now i have to add 3 more, to complete the triple conditional drilldown. The only thing I still have to figure out, is how to pass the string token value (tokEarliestString and latest string) to my target dashboard's title.

But the hardest part is done, many thanks!!

P.S:
Thanks for your advice, this drilldown is a small part of an app we are developing. In a later stage, we are planning to optimize our searches with base searches and tstats, but we are still far from that point. B efore going into the optimization stage, first we have to make something showable, so expect more questions by me, lol.

niketnilay
Legend

@3DGjos I am glad you got the solution working. For adding token to a Dashboard title you can try a work-around using Simple XML that I have suggested in the following post: https://answers.splunk.com/answers/712013/how-do-you-display-the-date-in-a-dashboard-title.html

Please try out and confirm! Up-vote the comment/Answers that helped.

Do send your queries our way. I bet you will get the best assistance from this amazing community 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

3DGjos
Communicator

Thanks for your answers, im trying the workaround, vut it doesn't seems clear to me where to put the tokens,

i've got the following error:

invalid child="eval" is not allowed in node "conditional drilldown"

when putting:

   <drilldown target="NewWindow">
   <eval token="drilldown_earliest">strptime($row._time$,"%Y-%m-%d %H:%M:%S")</eval>
          <eval token="drilldown_latest">strptime($row._time$,"%Y-%m-%d %H:%M:%S") + $row._span$</eval>
           <condition field="Failed logins">
             <link>/app/myapp/BCRA-DSB-009-mydashboard1?form.limit=$click.value2$</link>
           </condition>
           <condition field="Total blocked accounts">
             <link>/app/myapp/mydashboard2?form.limit=$click.value2$</link>
           </condition>
           <condition field="User behavior">
             <link>/app/myapp/mydashboard3?form.limit=$click.value2$</link>
           </condition>
         </drilldown>

can you help me? thanks in advance!

0 Karma

niketnilay
Legend

@3DGjos you would need to add eval to each condition block.

     <drilldown target="NewWindow">
        <condition field="Failed logins">
          <link>/app/myapp/BCRA-DSB-009-mydashboard1?form.limit=$click.value2$</link>
          <eval token="drilldown_earliest">strptime($row._time$,"%Y-%m-%d %H:%M:%S")</eval>
          <eval token="drilldown_latest">strptime($row._time$,"%Y-%m-%d %H:%M:%S") + $row._span$</eval>
        </condition>
        <condition field="Total blocked accounts">
          <link>/app/myapp/mydashboard2?form.limit=$click.value2$</link>
          <eval token="drilldown_earliest">strptime($row._time$,"%Y-%m-%d %H:%M:%S")</eval>
          <eval token="drilldown_latest">strptime($row._time$,"%Y-%m-%d %H:%M:%S") + $row._span$</eval>
        </condition>
        <condition field="User behavior">
          <link>/app/myapp/mydashboard3?form.limit=$click.value2$</link>
          <eval token="drilldown_earliest">strptime($row._time$,"%Y-%m-%d %H:%M:%S")</eval>
          <eval token="drilldown_latest">strptime($row._time$,"%Y-%m-%d %H:%M:%S") + $row._span$</eval>
        </condition>
      </drilldown>

Please try and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

3DGjos
Communicator

Hello, @niketnilay , I managed to make the drilldown work! Thanks!

the only way it worked was like this:

   <drilldown target="NewWindow">
          <condition field="Logins">
            <eval token="drilldown_earliest">strptime($row._time$,"%Y-%m-%d %H:%M:%S")</eval>
            <eval token="drilldown_latest">strptime($row._time$,"%Y-%m-%d %H:%M:%S") + $row._span$</eval>
            <link>
              <![CDATA[/app/myapp?form.limit=$click.value2$&form.time.earliest=$drilldown_earliest$&form.time.latest=$drilldown_latest$]]>
            </link>

I just have this problem, when I click on the lines, the earliest and latest on the drilldown dashboards is really weird, it takes me to the year 1555. I think it may be the token value? I'm not sure.

thanks!

0 Karma

somesoni2
Revered Legend

You can use $earliest$ and $latest$ token to capture the time of clicked line.

earliest/latest Time range of the clicked chart segment, or if not applicable, the time range of the search.

See more information here: https://docs.splunk.com/Documentation/Splunk/6.3.3/Viz/PanelreferenceforSimplifiedXML#chart_.28event...
See the example here:
https://docs.splunk.com/Documentation/Splunk/6.3.3/Viz/Dynamicdrilldownindashboardsandforms#Dashboar...

niketnilay
Legend

@somesoni2 $latest$ token does not seem to work as expected with timechart drilldown. So there is a workaround to use _time and _span instead. Refer to the following answer post with the workaround: https://answers.splunk.com/answers/587132/drilldown-pass-the-earliest-and-latest-from-a-time.html

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.