Display the top 3 entries of a table from one panel to our block in same dashboard

[Splunk_noobie]

Hi All,

I have a panel displaying tabular data.

I want to display the top 3 rows from that table in my template block. How do we achieve this?

[Splunk_noobie]

@ITWhisperer  There is one flaw in the code. When we click on the row twice, it prints the data in the template without checking for duplicates. 

I was thinking of storing the dashboard Id in a token and add a script in our HTML code to check for duplicates. But not sure if this approach is correct. 

Any thoughts? 

[ITWhisperer]

The way I would probably approach it is to store some key information about what has been added in another token and then add an additional comparison to see if the new data matches any of the stored keys.

[Splunk_noobie]

Hi @ITWhisperer , 

Please find the XML code below: 

<form>
  <label>Support - Dashboard performance</label>
  <description>Support - Dashboard performance with reports</description>
  <fieldset submitButton="true" autoRun="true">
    <input token="input_index">      <!-- input1 -->
      <default>ENTER_AN_INDEX</default>
    <input token="userId">       <!-- input2 -->
      <default>*</default>
    </input>
    <input type="text" token="entityType" searchWhenChanged="true">   <!-- input3 -->
      <label>entityType</label>
      <default>*</default>
      <initialValue>*</initialValue>
    </input>
    <input type="text" token="verb" searchWhenChanged="true">    <!-- input4 -->
      <label>verb</label>
      <default>*</default>
      <initialValue>*</initialValue>
    </input>
    <input type="time" searchWhenChanged="false">       <!-- input5 -->
      <default>
        <earliestTime>-24h@h</earliestTime>
        <latestTime>now</latestTime>
      </default>
    </input>
    <input type="text" token="span" searchWhenChanged="true">       <!-- input6 -->
      <label>Timechart Span</label>
      <default>1h</default>
    </input>
  </fieldset>
  
<!-- Row displaying 2 Panels : DashboardId & Report ID-->
  <row>
    <panel>
      <table>
        <title>Dashboard performance</title>     <!-- Panel to display dashboard ID. Once we click on a dashboardID it shows required data in customer_comment_da  --> 
        <search>
          <query>$input_index$ `logRecordType(D,R)` earliest=$earliest$ latest=$latest$ | stats count, count(eval(runTime &gt; 2000)) as infoThresholdCount, count(eval(runTime * 1 &gt; 2000)) as warnThresholdCount, count(eval(runTime * 1 * 1 &gt; 5000)) as errorThresholdCount, avg(runTime) as averageRunTime, max(runTime) as maxRunTime, avg(cpuTime) as averageCpuTime, max(cpuTime) as maxCpuTime, avg(waitTime) as averageWaitTime, max(waitTime) as maxWaitTime by dashboardId, userId | eval averageRunTime = round(averageRunTime, 2) | sort -averageRunTime </query>
        </search>
        <option name="count">10</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="rowNumbers">false</option>
        <option name="wrap">true</option>
        <drilldown target="_blank">
          <eval token="earliest_str">$row.earliest_str$</eval>
          <eval token="latest_str">$row.latest_str$</eval>
          <eval token="earliest_str">$row.earliest$</eval>
          <eval token="tempEarliest">$earliest$-19800</eval>
          <!-- Temp converting IST to UTC -->
          <eval token="earliestUTC">if(isnull($earliestUTC$), strftime($tempEarliest$,"%m/%d/%y %H:%M:%S") , $earliestUTC$)</eval>
          <eval token="latest_str">$row.latest$</eval>
          <eval token="tempLatest">$latest$-19800</eval>
          <!-- Temp converting IST to UTC -->
          <eval token="latestUTC">if(isnull($latestUTC$), strftime($tempLatest$,"%m/%d/%y %H:%M:%S"), $latestUTC$)</eval>
          <set token="dashTok">$row.dashboardId$</set>

<!-- Code to display multiple rows data in template -->
          <eval token="customer_comment_da">if(isnull($customer_comment_da$),"Dashboard ID : ".$row.dashboardId$."
User ID : ".$row.userId$."
Count : ".$row.count$." 
Average runTime : ".$row.averageRunTime$." ms 
Max runTime : ".$row.maxRunTime$." ms
",$customer_comment_da$."
Dashboard ID : ".$row.dashboardId$."
User ID : ".$row.userId$."
Count : ".$row.count$." 
Average runTime : ".$row.averageRunTime$." ms
Max runTime : ".$row.maxRunTime$." ms
")</eval>																	
<!-- End Code to display multiple rows data in template -->

<!-- Panel that runs when we click on the dashboard id. We are using using $dashTok$ from above panel to run the below query -->
          <set token="drilldown_display">block</set>
        </drilldown>
      </table>
    </panel>
    <panel>
      <table>
        <title>Reports based on dashboard ID</title>
        <search>
          <query>$input_index$ `logRecordType(D,R)` earliest=$earliest$ latest=$latest$ dashboardId=$dashTok$ | table reportId runTime | sort -runTime</query>
        </search>
        <option name="count">10</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="wrap">true</option>
        <option name="drilldown">row</option>
        <drilldown target="_blank">
          <eval token="earliest_str">$row.earliest_str$</eval>
          <eval token="latest_str">$row.latest_str$</eval>
          <eval token="earliest_str">$row.earliest$</eval>
          <eval token="tempEarliest">$earliest$-19800</eval>
          <!-- Temp converting IST to UTC -->
          <eval token="earliestUTC">if(isnull($earliestUTC$), strftime($tempEarliest$,"%m/%d/%y %H:%M:%S") , $earliestUTC$)</eval>
          <eval token="latest_str">$row.latest$</eval>
          <eval token="tempLatest">$latest$-19800</eval>
          <!-- Temp converting IST to UTC -->
          <eval token="latestUTC">if(isnull($latestUTC$), strftime($tempLatest$,"%m/%d/%y %H:%M:%S"), $latestUTC$)</eval>

<!-- Storing top reportid using customer_comment_report token and displaying in the template --> 
          <eval token="customer_comment_report">if(isnull($customer_comment_report$),"<!-- Code to display multiple rows data in template -->Report Id : ".$row.reportId$."
RunTime : ".$row.runTime$." ms
",$customer_comment_report$."
Report Id : ".$row.reportId$."
RunTime : ".$row.runTime$." ms
")</eval>
          <!-- End Code to display multiple rows data in template -->
          <set token="drilldown_display">block</set>
        </drilldown>
      </table>
    </panel>
  </row>
  
<!-- below block shows template extracted from above queries -->
  <row depends="$drilldown_display$">
    <panel>
      <!-- template with row data -->
      <html>
        <h1 class="SectionHeader">Case Updation Template</h1>
        <div style="float:left; width:calc(95% - 100px);" class="pageInfo">
          <pre>
Hi Team,

We have received alert for dashboards. Please see below fro more details: 

Upon checking, we found below dashboards took high run time on an average:

$customer_comment_da$

$customer_comment_report$


Please review the above mentioned operations at your end.


Thanks,
Splunk Team
  </pre>                                   
        </div>
      </html>
    </panel>
  </row>
</form>

The expected result is to display dashboard ID and its data (for the row clicked) and also the top reportIds corresponding to that dashboardId. 

However i am able to manage to get data seperately under $customer_comment_da$ and $customer_comment_report$ seperately which are not linked to each other. 

[ITWhisperer]

customer_comment_report is populated by the drilldown in the second table; instead of adding to customer_comment_report why not extend the customer_comment_da token so it has a row selected from the first table followed by selected corresponding rows from the second table, then another row from the first table followed by the selected corresponding rows from the second table?

[Splunk_noobie]

@ITWhisperer 

I had the exact same thought and implemented the below code. 

[1]

Here I tokenized the elements I needed from Dashboard panel (dashTok, dashUserId,dashCount,dashAvgTime,dashMaxTime) and used them in $customer_comment_report$ ad it works fine giving below results. 

But the issue with this approach is its good for one dashboard and its corresponding report. When i click on second dashboard, it doesnt append 2nd dashboard id, its count etc and its corresponding reports. 

<!-- Row displaying 2 Panels : DashboardId & Report ID-->
  <row>
    <panel>
      <table>
        <title>Dashboard performance</title>     <!-- Panel to display dashboard ID. Once we click on a dashboardID it shows required data in customer_comment_da  --> 
        <search>
          <query>$input_index$ `logRecordType(D,R)` earliest=$earliest$ latest=$latest$ | stats count, count(eval(runTime &gt; 2000)) as infoThresholdCount, count(eval(runTime * 1 &gt; 2000)) as warnThresholdCount, count(eval(runTime * 1 * 1 &gt; 5000)) as errorThresholdCount, avg(runTime) as averageRunTime, max(runTime) as maxRunTime, avg(cpuTime) as averageCpuTime, max(cpuTime) as maxCpuTime, avg(waitTime) as averageWaitTime, max(waitTime) as maxWaitTime by dashboardId, userId | eval averageRunTime = round(averageRunTime, 2) | sort -averageRunTime </query>
        </search>
        <option name="count">10</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="rowNumbers">false</option>
        <option name="wrap">true</option>
        <drilldown target="_blank">
          <eval token="earliest_str">$row.earliest_str$</eval>
          <eval token="latest_str">$row.latest_str$</eval>
          <eval token="earliest_str">$row.earliest$</eval>
          <eval token="tempEarliest">$earliest$-19800</eval>
          <!-- Temp converting IST to UTC -->
          <eval token="earliestUTC">if(isnull($earliestUTC$), strftime($tempEarliest$,"%m/%d/%y %H:%M:%S") , $earliestUTC$)</eval>
          <eval token="latest_str">$row.latest$</eval>
          <eval token="tempLatest">$latest$-19800</eval>
          <!-- Temp converting IST to UTC -->
          <eval token="latestUTC">if(isnull($latestUTC$), strftime($tempLatest$,"%m/%d/%y %H:%M:%S"), $latestUTC$)</eval>
          <set token="dashTok">$row.dashboardId$</set>     <!-- tokenize dashboard components from panel 1 -->
          <set token="dashUserId">$row.userId$</set>
          <set token="dashCount">$row.count$</set>
          <set token="dashAvgTime">$row.averageRunTime$</set>
          <set token="dashMaxTime">$row.maxRunTime$</set>   <!-- End of tokenize dashboard components from panel 1 -->
<!-- Code to display multiple rows data in template -->
          <eval token="customer_comment_da">if(isnull($customer_comment_da$),"Dashboard ID : ".$row.dashboardId$."
User ID : ".$row.userId$."
Count : ".$row.count$." 
Average runTime : ".$row.averageRunTime$." ms 
Max runTime : ".$row.maxRunTime$." ms
",$customer_comment_da$."
Dashboard ID : ".$row.dashboardId$."
User ID : ".$row.userId$."
Count : ".$row.count$." 
Average runTime : ".$row.averageRunTime$." ms
Max runTime : ".$row.maxRunTime$." ms
")</eval>																	
<!-- End Code to display multiple rows data in template -->

<!-- Panel that runs when we click on the dashboard id. We are using using $dashTok$ from above panel to run the below query -->
          <set token="drilldown_display">block</set>
        </drilldown>
      </table>
    </panel>
    <panel>
      <table>
        <title>Reports based on dashboard ID</title>
        <search>
          <query>$input_index$ `logRecordType(D,R)` earliest=$earliest$ latest=$latest$ dashboardId=$dashTok$ | table reportId runTime | sort -runTime</query>
        </search>
        <option name="count">10</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="wrap">true</option>
        <option name="drilldown">row</option>
        <drilldown target="_blank">
          <eval token="earliest_str">$row.earliest_str$</eval>
          <eval token="latest_str">$row.latest_str$</eval>
          <eval token="earliest_str">$row.earliest$</eval>
          <eval token="tempEarliest">$earliest$-19800</eval>
          <!-- Temp converting IST to UTC -->
          <eval token="earliestUTC">if(isnull($earliestUTC$), strftime($tempEarliest$,"%m/%d/%y %H:%M:%S") , $earliestUTC$)</eval>
          <eval token="latest_str">$row.latest$</eval>
          <eval token="tempLatest">$latest$-19800</eval>
          <!-- Temp converting IST to UTC -->
          <eval token="latestUTC">if(isnull($latestUTC$), strftime($tempLatest$,"%m/%d/%y %H:%M:%S"), $latestUTC$)</eval>

<!-- Storing top reportid using customer_comment_report token and displaying in the template --> 
            <eval token="customer_comment_report">if(isnull($customer_comment_report$),"<!-- Code to display multiple rows data in template -->DashboardId : ".$dashTok$."
                    Count : ".$dashCount$."
                    User ID : ".$dashUserId$."
                    Avg runTime : ".$dashAvgTime$."
                    Max runTime : ".$dashMaxTime$."
                    <br/>
                    Reports for the above dashboard consuming high runTime : 
                    ------------------------
                    Report ID  runTime (ms)
                    ------------------------
                    ".$row.reportId$." ".$row.runTime$."
                    ",$customer_comment_report$."
                    ".$row.reportId$." ".$row.runTime$."
                    ") 
            </eval>
          <!-- End Code to display multiple rows data in template -->
          <set token="drilldown_display">block</set>
        </drilldown>
      </table>
    </panel>
  </row>
  
<!-- below block shows template extracted from above queries -->
  <row depends="$drilldown_display$">
    <panel>
      <!-- template with row data -->
      <html>
        <h1 class="SectionHeader">Case Updation Template</h1>
        <div style="float:left; width:calc(95% - 100px);" class="pageInfo">
          <pre>
Hi Team,

We have received alert for dashboards. Please see below fro more details: 

Upon checking, we found below dashboards took high run time on an average:


$customer_comment_report$


Please review the above mentioned operations at your end.


Thanks,
Splunk Team
  </pre>                                   
        </div>
      </html>
    </panel>
  </row>

[2]
Coming to your approach you suggested, of bringing report ids from 2nd panel to customer_comment_da in 1st panel.

How do i tokenize the top 3 or 4 rows of report id and append it with customer_comment_da token?

Ideal output expected:

[1]

DashboardId : 01T5b0000004eST
Count : 9
User ID : 00536532002Z2ov
Avg runTime : 95860.33 ms
Max runTime : 602874 ms

Reports for the above dashboard consuming high runTime :
------------------------
Report ID runTime (ms)
------------------------
00O5b0000099BUT 602874
00O5b0000099BLM 77054
00O5b0000099BTC 76588
00O5b0000099GUH 30119

[2]
DashboardId : 01P5b0000004eST
Count : 6
User ID : 00536500002Z2kl
Avg runTime : 55720 ms
Max runTime : 501324 ms

Reports for the above dashboard consuming high runTime :
------------------------
Report ID runTime (ms)
------------------------
00O5b0000066BUT 80052
00O5b0000066BLM 44520

[Splunk_noobie]

Hii @ITWhisperer 

One simple refresh functionality that we need to add to the below HTML panel

> As discussed, We append row data from the previous panel and store it in <customer_commet_report> token
> Now I want to add a refresh button on this panel So that if the user clicks the wrong row, we can refresh this HTML panel using a refresh button just for this panel rather than refreshing the whole dashboard.

> I tried using this code below in my HTML panel but i guess I'm missing something. Can you help ?

Attempt to add refresh button in Panel : 
<input type="link" token="refresh">
<label></label>
<choice value="Yes">Refresh?</choice>
<change>
<condition value="Yes">
<unset token="customer_coment_report"></unset>
</condition>
</change>
</input>

HTML Panel Code

<row depends="$drilldown_display$">
    <panel>
      <!-- template with row data -->
      <html>
        <h1 class="SectionHeader">Case Updation Template</h1>
        <div style="float:left; width:calc(95% - 100px);" class="pageInfo">
          <pre>
Hi Team,

We have received alert for dashboards. Please see below fro more details: 

Upon checking, we found below dashboards took high run time on an average:


$customer_comment_report$


Please review the above mentioned operations at your end.


Thanks,
Splunk Team
  </pre>                                   
        </div>
      </html>
    </panel>
  </row>

[ITWhisperer]

There are a couple of things wrong here:

1. The second drilldown should be extending customer_comment_da not customer_comment_report
2. The if function in this drilldown should have an else part to actually extend customer_comment_da
3. The html panel should use customer_comment_da not customer_comment_report

[Splunk_noobie]

@ITWhisperer  Just tried what you suggested. And I am a bit confused if this is what you meant? 

Customer_comment_da

 <eval token="customer_comment_da">if(isnull($customer_comment_da$),"<!-- Code to display multiple rows data in template -->Dashboard ID : ".$row.dashboardId$."
User ID : ".$row.userId$."
Count : ".$row.count$." 
Average runTime : ".$row.averageRunTime$." ms 
Max runTime : ".$row.maxRunTime$." ms
",$customer_comment_da$."
Dashboard ID : ".$row.dashboardId$."
User ID : ".$row.userId$."
Count : ".$row.count$." 
Average runTime : ".$row.averageRunTime$." ms
Max runTime : ".$row.maxRunTime$." ms
")</eval>

customer_comment_report

<eval token="customer_comment_report">if(isnull($customer_comment_report$),"<!-- Code to display multiple rows data in template -->".$customer_comment_da$."
<br/>
Reports for the above dashboard consuming high runTime : 
------------------------
Report ID  runTime (ms)
------------------------
".$row.reportId$." ".$row.runTime$."
",$customer_comment_da$."
".$row.reportId$." ".$row.runTime$."
") 
</eval>

[Splunk_noobie]

@ITWhisperer  Here's an update. Tried to understand better what you said and implemented this. 

> Appending $customer_comment_report$ to $customer_comment_da$ 
> displaying $customer_comment_da$ in HTML panel

<eval token="customer_comment_da">if(isnull($customer_comment_da$),"<!-- Code to display multiple rows data in template -->Dashboard ID : ".$row.dashboardId$."
User ID : ".$row.userId$."
Count : ".$row.count$." 
Average runTime : ".$row.averageRunTime$." ms 
Max runTime : ".$row.maxRunTime$." ms
<br/>
".$customer_comment_report$."
",$customer_comment_da$."
Dashboard ID : ".$row.dashboardId$."
User ID : ".$row.userId$."
Count : ".$row.count$." 
Average runTime : ".$row.averageRunTime$." ms
Max runTime : ".$row.maxRunTime$." ms
".$customer_comment_report$."
")</eval>

If the above code is fine, then I feel we are almost there. just a couple of small challenges here...

> The first output of $customer_comment_da$ displays $customer_comment_report$ component as null since as per programming flow, reports are yet to be generated in next step of code.  

>  On clicking the 2nd and third dashboards,  the $customer_comment_report$ seems to store report ids from older dashboards as well. I guess we'll have to make the token null so that fresh report ids are populated for new dashboardId 

[ITWhisperer · Accepted solution]

In the first panel, unset the customer_comments_details token

 <eval token="customer_comment_da">if(isnull($customer_comment_da$),"<!-- Code to display multiple rows data in template -->Dashboard ID : ".$row.dashboardId$."
User ID : ".$row.userId$."
Count : ".$row.count$." 
Average runTime : ".$row.averageRunTime$." ms 
Max runTime : ".$row.maxRunTime$." ms
",$customer_comment_da$."
Dashboard ID : ".$row.dashboardId$."
User ID : ".$row.userId$."
Count : ".$row.count$." 
Average runTime : ".$row.averageRunTime$." ms
Max runTime : ".$row.maxRunTime$." ms
")</eval>
<unset token="customer_comment_details"/>

and, in the second panel, add the header text for the details if the token is unset, then set the token

<eval token="customer_comment_da">if(isnull($customer_comment_da$),if(isnull($customer_comment_detail$),"<!-- Code to display multiple rows data in template -->"."
<br/>
Reports for the above dashboard consuming high runTime : 
------------------------
Report ID  runTime (ms)
------------------------
".$row.reportId$." ".$row.runTime$."
",$row.reportId$." ".$row.runTime$."
"),if(isnull($customer_comment_detail$),$customer_comment_da$."
<br/>
Reports for the above dashboard consuming high runTime : 
------------------------
Report ID  runTime (ms)
------------------------
".$row.reportId$." ".$row.runTime$."
",$customer_comment_da$.$row.reportId$." ".$row.runTime$."
"))
</eval>
<set token="customer_comment_details"/>

[Splunk_noobie]

@ITWhisperer  You are a legend. Well implemented the logic using nested if 👍


Just a quick question. 
Can I store the results generated in our reports panel (reported and runTime) and display the table as it is in our HTML panel? Rather than accessing each row data and appending?

[ITWhisperer]

I am unaware of a way to do this. The drilldown only really has access to the row that was clicked, not the whole table of results. Also, I suspect you would run into trouble trying to store the whole result set in a token.

[Splunk_noobie]

@ITWhisperer  any thoughts on how to display data from the tabular panel to our block of text?

[ITWhisperer]

Since you only have 3 rows that you want to use, but you only have access to the first row in a set of results, why not reformat the table so that all 3 rows are on the first row, then you should be able to access all the values you need and set tokens accordingly. You may want to put the search in a non-displaying area of the dashboard.

| makeresults | eval _raw="Report      Count     Comments
Report 1    22         abc
Report 2    786        def
Report 3    10,037     ghi
Report 4    719        jkl"
| multikv forceheader=1
| fields - _raw _time linecount
| fields - _mkv*


| head 3
| eval row="values"
| stats values(*) as * by row
| transpose 0 header_field=row
| mvexpand values
| streamstats count by column
| eval column=column."_".count
| fields - count
| transpose 0 header_field=column
| where column="values"
| fields - column

[Splunk_noobie]

Hi @ITWhisperer 

Thank you for your solution, but let me rephrase my question. 

Below is the Splunk result in our dashboard panel

Dashboard      Count     Comments
DA ID 1    22         abc
DA ID 2    786        def
DA ID 3    10,037     ghi
DA ID 4    719        jkl

Upon clicking the first row (or any row in the table) we get a result in another panel for that corresponding dashboard id. 

Report      runTime
Report 1    100 ms
Report 2    200 ms
Report 3    300 ms
Report 4    400 ms

now my basic requirement is to display results  from both the panels above, in my block as follows :

Below dashboards took high run time on an average:
[1]
Dashboard ID : 01ZYM00000aprt
User ID : 0053600000056ZP
Count : 3
Average runTime : 95860.33 ms 
Max runTime : 602874 ms

Below are the reports in the above dashboard that's taking high runTime. 
ReportId | runTime (ms)
------------------------
00O5b0000000TUF 602874
00O5b0000000POL 501030
00O5b0000000YOP 456100

[2]
Dashboard ID : 02200000000apmt
User ID : 005360000009999
Count : 1
Average runTime : 61830.33 ms 
Max runTime : 902148 ms

Below are the reports in the above dashboard that's taking high runTime. 
ReportId | runTime (ms)
------------------------
00O5b0000000FOO 802440
00O5b0000000HOM 701555

So I was able to store info related to dashboard ID in one token $dashTok$ (from panel 1) and the reports generated in 2nd token $repToken$ (from Panel 2)  and tried getting results as shown above. But no luck. Could you help me?

[ITWhisperer]

How have you set up the tokens (can you share the dashboard XML code)?