Splunk Search

Drilldown using fields not displayed in original table

grahamcampbell
Explorer

I've searched and found all sorts of advice and links to articles, but nothing has worked. Granted I am a Splunk newbie, so I am more than willing to accept I've done something completely daft - in which case, I welcome all feedback about how I might improve my dashboard, not just the drilldown.

I have a dashboard that looks kinda like this:

  <row>
    <panel>
      <table>
        <title>Top 20 Daily Viewers</title>
        <search>
          <query>sourcetype=wiki_users site="BLAH" username!="-" | stats count by username | lookup FRIENDLY_USERNAMES samaccountname as username | eval displayname=if(isnull(displayname),username,displayname) | sort -count | head 20 | table displayname, count | rename displayname AS "User name", count AS "Number of times of viewed"</query>
          <earliest>@d</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">row</option>
      </table>
    </panel>
  </row>

The lookup takes a user name like gcampbe1 and replaces it with Graham Campbell (Docs) and so on.

What I now need is to be able to click on Graham Campbell (Docs) to see exactly which articles in the wiki he's been looking at, not just his daily count of articles. But because I've used the lookup, I can't pass Graham Campbell (Docs) using the click.value token because I need gcampbe1, which is in the username field.

I thought I would be able to use $row.username$ in the drilldown link to a follow-on query, but that never works. Instead, the literal string "$row.username$" gets passed to the drilldown search, which of course produces nothing. Here's how I formatted that:

<option name="drilldown">row</option>
<drilldown>
    <link>search?q=sourcetype=confluence_access site="BLAH" username="$row.username$" | stats count by url</link>
</drilldown>

This is driving me absolutely nuts now so I would really appreciate any and all input here.

Cheers,
G

1 Solution

cmerriman
Super Champion

you can actually use <fields> in the table simple XML to display certain fields but keep others available in your search.
https://docs.splunk.com/Documentation/Splunk/7.0.3/Viz/PanelreferenceforSimplifiedXML#table
try something like this

 <row>
     <panel>
       <table>
         <title>Top 20 Daily Viewers</title>
         <search>
           <query>sourcetype=wiki_users site="BLAH" username!="-" | stats count by username|eval username_orig=username | lookup FRIENDLY_USERNAMES samaccountname as username | eval displayname=if(isnull(displayname),username,displayname) | sort -count | head 20 | table displayname, username_orig, count | rename displayname AS "User name", count AS "Number of times of viewed"</query>
           <earliest>@d</earliest>
           <latest>now</latest>
         </search>
        <fields>["User name","Number of times of viewed"]</fields>
            <drilldown>
                <link target="_blank">search?q=sourcetype=confluence_access site="BLAH" username="$row.username_orig$" | stats count by url</link>
            </drilldown>
       </table>
     </panel>
   </row>

View solution in original post

cmerriman
Super Champion

you can actually use <fields> in the table simple XML to display certain fields but keep others available in your search.
https://docs.splunk.com/Documentation/Splunk/7.0.3/Viz/PanelreferenceforSimplifiedXML#table
try something like this

 <row>
     <panel>
       <table>
         <title>Top 20 Daily Viewers</title>
         <search>
           <query>sourcetype=wiki_users site="BLAH" username!="-" | stats count by username|eval username_orig=username | lookup FRIENDLY_USERNAMES samaccountname as username | eval displayname=if(isnull(displayname),username,displayname) | sort -count | head 20 | table displayname, username_orig, count | rename displayname AS "User name", count AS "Number of times of viewed"</query>
           <earliest>@d</earliest>
           <latest>now</latest>
         </search>
        <fields>["User name","Number of times of viewed"]</fields>
            <drilldown>
                <link target="_blank">search?q=sourcetype=confluence_access site="BLAH" username="$row.username_orig$" | stats count by url</link>
            </drilldown>
       </table>
     </panel>
   </row>

View solution in original post

grahamcampbell
Explorer

Thanks for replying cmerriman.

I'm unclear on the description of the fields element: "Restrict search results to these fields. The order of the listed fields determines table column order."

I don't want to apply any restriction to the returned results of my search other than what I've already specified in the query string.

I just want to be able to use the original value of the username field in the drilldown, either by simply specifying $row.username$ as the token or by somehow doing a reverse lookup in the drilldown from friendly name back to system user name.

The XML in your response had the effect of producing a table with no results, with two columns titled displayname and count. Clearly my newness to Splunk is interfering with my understanding here, but I don't see how this would have worked?

0 Karma

cmerriman
Super Champion

As I had mentioned, the <fields> element allows you to only show certain fields from your search instead of all fields - much like in SPL using |fields foo bar. Don't really think of it as a restriction, more like hiding certain fields and showing certain other fields from your search, that way you can still use fields and field values in drilldowns, even though they are not shown.

Oh, I see where I had a mistake in my XML. There was the rename in the search and that should be used in the <fields> element, whereas I was using the original field names. Try replacing it with these.

 <fields>["User name","Number of times of viewed"]</fields>
0 Karma

grahamcampbell
Explorer

Hey cmerrian, a followup - every row I click on to create the drilldown search always picks the first row's username and only ever that first row's username? Any idea why? I've copied your XML exactly (with the necessary adjustments) and I cannot figure this one out.

Doesn't matter which row I click, the username token always takes the first result's value and passes it to the drilldown. Any ideas?

0 Karma

cmerriman
Super Champion

Sorry, i edited the original answer. It should work now. $result.<fieldname>$ uses the first valueof that field. $row.<fieldname>$ will use the value of that field in the row clicked on - also, you don't have to set a token before hand with that one. I tested it out in a dashboard on my own instance and it should work.

0 Karma

grahamcampbell
Explorer

Yes! This is the elegant implementation I was looking for all along. Thank you very much again, this has made my day!

0 Karma

grahamcampbell
Explorer

Ah I see now, I was reading the description of the element a bit more literally than your addendum has provided for.

This is working perfectly now, thank you very much for having the patience to deal with a very specific request and very fresh Splunk newbie! 🙂

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