topic Re: piechart drilldown in Dashboards & Visualizations

piechart drilldown

Mike6960 — Tue, 29 Sep 2020 16:32:58 GMT

I have read a lot of other questions about this matter but i just can't get it running.
Ik have this search":
index=xxxx
| stats values earliest(G_S) AS G_S values earliest(A_Z) AS A_Z values earliest(D_A) AS D_A_I count by ZMV
| eval eG_S=strptime(G_S,"%Y-%m-%d %H:%M:%S.%N"), eA_Z=strptime(A_Z, "%Y-%m-%d %H:%M:%S.%N")

| eval eD_A_I=coalesce(strptime(D_A_I, "%Y-%m-%d %H:%M:%S.%N"),now())
| eval days=floor((eD_A_I-eA_Z)/86400)
| stats count as daycount by days |eval days = abs(days)
| eval days = if(days<=14,"Binnen KPI","Buiten KPI")
| stats sum(daycount) as daycount by days
The results are presented in a piechart, when i click i want to see the individual events. I have tried to do this with a token but i am stuck (again) . We are running version 6.5.2

Re: piechart drilldown

kamlesh_vaghela — Thu, 02 Nov 2017 17:12:15 GMT

Can you please use below pie chart drilldown example??

<chart>
        <search>
          <query>index=_internal  | stats count as AA by sourcetype</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.drilldown">all</option>
        <drilldown>
          <link target="_blank">search?earliest=$earliest$&amp;latest=$latest$&amp;q= index=_internal sourcetype="$click.value$" </link>
        </drilldown>
      </chart>

Let me know for other drilldown help.

Happy Splunking

Re: piechart drilldown

Mike6960 — Thu, 02 Nov 2017 17:19:37 GMT

Hi, i dont think i understand your answer. Where do i put my search and where do i define which search splunk needs to execute when i click on the piechart?

Re: piechart drilldown

Mike6960 — Mon, 06 Nov 2017 07:21:20 GMT

sadly, this does not work, i replaced my own link for your suggested one:

      <drilldown>
       <link target="_blank">search?earliest=$earliest$&amp;latest=$latest$&amp;q= index=_internal sourcetype="$click.value$" </link>
     </drilldown>

But this does not work

Re: piechart drilldown

niketn — Mon, 06 Nov 2017 09:45:12 GMT

@Mike6960, I am not sure about the syntax | stats values earliest(G_S) AS G_S ... may be it is a typo. However, I would like to point that the slices for your pie chart are based on data you calculate in your query hence the same is not available in your base search or raw event. This implies when you perform your drilldown you can present the values calculated in your first stats command not the raw events.

If following is your base search (PS: for saving as dashboard you would need to escape greater than > and less than < symbols using > and < respectively).

index=xxxx 
| stats earliest(G_S) AS G_S earliest(A_Z) AS A_Z earliest(D_A) AS D_A_I count by ZMV 
| eval eG_S=strptime(G_S,"%Y-%m-%d %H:%M:%S.%N"), eA_Z=strptime(A_Z, "%Y-%m-%d %H:%M:%S.%N") 
| eval eD_A_I=coalesce(strptime(D_A_I, "%Y-%m-%d %H:%M:%S.%N"),now())
| eval days=floor((eD_A_I-eA_Z)/86400)
| stats count(eva(abs(days<=14))) as "Binnen KPI" count(eva(abs(days>14))) as "Buiten KPI" by days 
| transpose column_name="days"
|  rename "row 1" as count

You can create the following drilldown token:

    <drilldown>
      <set token="tokDays">$click.value$</set>
    </drilldown>

And then pass the same on to another panel with table visualization using tokDays. (PS: for saving as dashboard you would need to escape less than < symbols using <)

index=xxxx 
| stats earliest(G_S) AS G_S earliest(A_Z) AS A_Z earliest(D_A) AS D_A_I count by ZMV 
| eval eG_S=strptime(G_S,"%Y-%m-%d %H:%M:%S.%N"), eA_Z=strptime(A_Z, "%Y-%m-%d %H:%M:%S.%N") 
| eval eD_A_I=coalesce(strptime(D_A_I, "%Y-%m-%d %H:%M:%S.%N"),now())
| eval days=floor((eD_A_I-eA_Z)/86400)
|  eval days = abs(days)
|  eval days = if(days<=14,"Binnen KPI","Buiten KPI")
|  search days="$tokDays$"
|  fieldformat eD_A_I=strftime(eD_A_I,"%Y-%m-%d %H:%M:%S.%N")
|  fieldformat eA_Z=strftime(eA_Z,"%Y-%m-%d %H:%M:%S.%N")
|  table log_level eD_A_I eA_Z days count

Re: piechart drilldown

Mike6960 — Mon, 06 Nov 2017 10:15:20 GMT

Ok, so actually what you are saying is that it is not possible? "If following is your base search.. " , this is not my search, because i need a piechart with two values. Do i understand you correctly when i say that you suggest to make a new panel to show the results??

Re: piechart drilldown

Mike6960 — Mon, 06 Nov 2017 10:49:36 GMT

Ok, sorry, note to myself not to be impatient. I've used my own search, used your token answer and added a new panel. Is it possible to only show the panel when the token has a value, so only when the piechart is clicked?

Re: piechart drilldown

niketn — Mon, 06 Nov 2017 14:01:45 GMT

Yes you can use depends attribute with token which can toggle display based on whether it is set or not. The depends attribute can be applied to dashboard's input or visualization elements like <row>, <panel>, <table>,<chart> etc. whichever you need to toggle with corresponding token.

Following is the snippet for your drilldown table where entire row can be hidden with depends:

<row depends="$tokDays$">   
   <panel>
      <title>Drilldown Table For ($tokDays$)</title>
      <table>
      <search>
         <query>....
          ....

Re: piechart drilldown

niketn — Mon, 06 Nov 2017 14:09:35 GMT

Whichever fields from your event data you are interested in you can use values(<yourFieldName>) or list(<yourFieldName>) in your first stats command on the second line of SPL. Once you do table drilldown they will also be displayed (as multivalued if they have multiple values and you are using values() or list()). Don't forget the add the additional field in the final table command of drilldown query.

Refer to various statistical methods for their purpose and differences, so that you can use the one you need appropriately: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonStatsFunctions#Types_of_statistical_and_charting_functions

PS: More fields you include in first stats command more performance impact it will have. So try to retain only those which are necessary.

Re: piechart drilldown

Mike6960 — Mon, 06 Nov 2017 15:06:53 GMT

The stats in my search i use to get the earliest dates, i would not know another way to accomplish this. Thank you for all your help. I will never really get splunk I am afraid. I am just getting by by google en splunk answers...

Re: piechart drilldown

kamlesh_vaghela — Mon, 06 Nov 2017 16:05:27 GMT

Can you share your sample XML code ?

Re: piechart drilldown

Mike6960 — Mon, 20 Nov 2017 10:24:49 GMT

Hai niketnilay,

Another question, what if i want to open a new page for the results instead of a panel

Re: piechart drilldown

niketn — Mon, 20 Nov 2017 12:31:13 GMT

@Mike6960, you would need to use <link target="_blank"> as others have suggested. The code inside the drilldown link will vary as per what is your target, whether it is search or another dashboard within Splunk or some external link altogether.

You can refer to documentation for various drilldown link options: http://docs.splunk.com/Documentation/Splunk/latest/Viz/DrilldownIntro#Choose_a_drilldown_action
PS: If you are on Splunk Enterprise 6.6 or higher, basic drilldown options would be available in UI through Edit Panel option (no coding required).

You can also get Splunk Dashboard Examples App from Splunkbase for going through examples showcasing the drilldown options: https://splunkbase.splunk.com/app/1603/

Re: piechart drilldown

Mike6960 — Mon, 20 Nov 2017 12:47:00 GMT

Thank you, since last weekend we run version 7. But in the drilldowneditor its still required to work with tokens, this is quite challenging for me

Re: piechart drilldown

Mike6960 — Mon, 20 Nov 2017 12:54:26 GMT

like this?

     <drilldown>
       <link target="_blank"><set token="tokDays">$click.value$</set> </link>
     </drilldown>