Drill down - unable to get the result

[dkgs]

Hi,

there is a requirement to create drill down from  a chart. When you click the chart the values should populate in a panel below. But currently its not working. In the below chart user roles are not getting populated. Attaching the code for reference. Any help would be appreciated

[niketn]

@dkgs For the community to assist you better, can you add Simple XML dashboard code (specially the one that is populating your chart)? Adding a screenshot means Community experts who try to assist you will have to type it themselves to prototype or give you an example as you requested (unless you are fine with any run anywhere example portraying your scenario). Please add more details to your actual issue. If you want the role in the dropdown you will first have to create a drilldown token for role from your chart panel. Which you do not have currently.

Based on your current chart screenshot, you need one of the following:

Option 1: If you want Generic drilldown token to pull the role name which has been clicked (if you have many dynamic roles):

<set token="role">$click.name2$</set>

Option 2: If you need specific drilldown in case of fixed and handful of roles, but based on click want to pull all roles at a particular time:

<set token="role_NO_RISK">$row.NO_RISK$</set>
<set token="role_YOUR_2ND_ROLE">$row.YOUR_2ND_ROLE$</set>
...
...

On a different note, seems like your SPL needs several changes for performance tuning. For example you can have either KV_MODE=json or INDEXED_EXTRACTION=json and avoid spath (depending on use case). You need to filter results upfront rather than later in the query! Refer to documentation to understand these better: https://docs.splunk.com/Documentation/Splunk/latest/Search/Quicktipsforoptimization

[dkgs]

any@niketn  Thank you for the reply. Please find the below XML code . The values need to be populated based on the date.  Any suggestions would be appreciated . Thanks in advance

<form>
<label>Security Roles</label>
<fieldset submitButton="false"></fieldset>
<row>
<panel>
<chart>
<search>
<query>index="indexPro" sourcetype=RSUSR100N source=RSUSR100N-DH1 | spath | rename message.JOBNAME as JOBNAME, message.JOBSPOOL{}.* as * | mvexpand LINE | search LINE="*Role*" | search LINE="*|*" | eval USERNAME=trim(mvindex(split(LINE,"|"),1)), DATE=mvindex(split(LINE,"|"),2), TIME=mvindex(split(LINE,"|"),3), PERFORMEDBY=mvindex(split(LINE,"|"),4), ACTION=mvindex(split(LINE,"|"),5), ROLE=trim(mvindex(split(LINE,"|"),6)), ROLENAME=trim(mvindex(split(LINE,"|"),7)) | dedup LINE | fields USERNAME DATE TIME PERFORMEDBY ACTION ROLE ROLENAME | table DATE USERNAME ROLE ACTION PERFORMEDBY ROLENAME | join USERNAME [search index="index_heineken-uk" sourcetype=*USR02* message.SID = "DH1" | rename message.SID as SID, message.VALUE as USR02_DATA | eval mvf1 = split(USR02_DATA, ";") | eval USERNAME=trim(mvindex(mvf1,0)) | eval GROUP=mvindex(mvf1,1) | eval VALIDTO=mvindex(mvf1,2) | eval VALIDFROM=mvindex(mvf1,3) | eval LASTLOGONTIME=mvindex(mvf1,4) | eval CLIENT=mvindex(mvf1,5) | eval LASTLOGONDATE=mvindex(mvf1,6) | eval LOCKSTATUS=mvindex(mvf1,7) | eval LDATE=strftime(strptime(LASTLOGONDATE, "%Y%m%d"),"%d.%m.%Y")| eval LDATE2=strptime(LASTLOGONDATE, "%Y%m%d") | eval today=strftime(now(),"%d.%m.%Y") | eval lastMo = relative_time(now(),"-90d@d") | fields DATE USERNAME SID CLIENT GROUP VALIDTO LASTLOGONTIME LASTLOGONDATE LOCKSTATUS LDATE today 90days lastMo LDATE2 | dedup USERNAME | fields USERNAME GROUP ] | join type=outer [search index="index_heineken-uk" sourcetype=AGR_USERS source=AGR_USERS-DH1 | rename message.SID as SID, message.VALUE as ROLE_DATA | eval mvf1 = split(ROLE_DATA, ";") | eval ROLE=trim(mvindex(mvf1,0)), MODIFIED=trim(mvindex(mvf1,1)) | fields ROLE MODIFIED ] |eval AlertStatus=if( MODIFIED = 'X', "RISK", "NO_RISK") | eval _time=strftime(strptime(DATE,"%d.%m.%Y"),"%Y-%m-%d %H:%M:%S") |chart count over _time by AlertStatus</query>
<earliest>0</earliest>
<latest></latest>
<sampleRatio>1</sampleRatio>
<refresh>5m</refresh>
<refreshType>delay</refreshType>
</search>

<option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
<option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
<option name="charting.axisTitleX.text">ROLE</option>
<option name="charting.axisTitleX.visibility">visible</option>
<option name="charting.axisTitleY.text">DATE</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">column</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">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.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="refresh.display">progressbar</option>
<option name="trellis.enabled">0</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
<drilldown>
<set token="tokComponent">$row._time$</set>
</drilldown>
</chart>
</panel>
</row>
<row depends="$tokComponent$">
<panel>
<title>User Roles</title>
<table>
<search>
<query>index="indexPro" sourcetype=RSUSR100N source=RSUSR100N-DH1 | spath | rename message.JOBNAME as JOBNAME, message.JOBSPOOL{}.* as * | mvexpand LINE | search LINE="*Role*" | search LINE="*|*" | eval USERNAME=trim(mvindex(split(LINE,"|"),1)), DATE=mvindex(split(LINE,"|"),2), TIME=mvindex(split(LINE,"|"),3), PERFORMEDBY=mvindex(split(LINE,"|"),4), ACTION=mvindex(split(LINE,"|"),5), ROLE=trim(mvindex(split(LINE,"|"),6)), ROLENAME=trim(mvindex(split(LINE,"|"),7)) | dedup LINE | fields USERNAME DATE TIME PERFORMEDBY ACTION ROLE ROLENAME | table DATE USERNAME ROLE ACTION PERFORMEDBY ROLENAME | where DATE="$tokComponent$" </query>
<earliest>0</earliest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">20</option>
<option name="dataOverlayMode">none</option>
<option name="percentagesRow">false</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
</table>
</panel>
</row>
</form>

[niketn · Accepted solution]

@dkgs seems like your series is split by AlertStatus as a field similar to | timechart count by AlertStatus.

You can try click.name2 to pull the AlertStatus name and click.value2 to pull that particular AlertStatus value. Please add the code as suggested earlier to set the required token in the <drilldown> section of the chart and use the token in the drilldown search.

[dkgs]

@niketn  Now I am getting the alertstatus. But I need to get the _time in the x axis also. I tried adding the below code, but not working. Could you please suggest the modifications required. Thanks in advance

<dashboard>
<label>Test_Security_PARC_Roles_Drilldown</label>
<row>
<panel>
<chart>
<search>
<query>index="index1" sourcetype=table1 source=table1-DH1 | spath | rename message.JOBNAME as JOBNAME, message.JOBSPOOL{}.* as * | mvexpand LINE | search LINE="*Role*" | search LINE="*|*" | eval USERNAME=trim(mvindex(split(LINE,"|"),1)), DATE=mvindex(split(LINE,"|"),2), TIME=mvindex(split(LINE,"|"),3), PERFORMEDBY=mvindex(split(LINE,"|"),4), ACTION=mvindex(split(LINE,"|"),5), ROLE=trim(mvindex(split(LINE,"|"),6)), ROLENAME=trim(mvindex(split(LINE,"|"),7)) | dedup LINE | fields USERNAME DATE TIME PERFORMEDBY ACTION ROLE ROLENAME | table DATE USERNAME ROLE ACTION PERFORMEDBY ROLENAME | join USERNAME [search index="index1" sourcetype=*table2* message.SID = "DH1" | rename message.SID as SID, message.VALUE as table2_DATA | eval mvf1 = split(table2_DATA, ";") | eval USERNAME=trim(mvindex(mvf1,0)) | eval GROUP=mvindex(mvf1,1) | eval VALIDTO=mvindex(mvf1,2) | eval VALIDFROM=mvindex(mvf1,3) | eval LASTLOGONTIME=mvindex(mvf1,4) | eval CLIENT=mvindex(mvf1,5) | eval LASTLOGONDATE=mvindex(mvf1,6) | eval LOCKSTATUS=mvindex(mvf1,7) | eval LDATE=strftime(strptime(LASTLOGONDATE, "%Y%m%d"),"%d.%m.%Y")| eval LDATE2=strptime(LASTLOGONDATE, "%Y%m%d") | eval today=strftime(now(),"%d.%m.%Y") | eval lastMo = relative_time(now(),"-90d@d") | fields DATE USERNAME SID CLIENT GROUP VALIDTO LASTLOGONTIME LASTLOGONDATE LOCKSTATUS LDATE today 90days lastMo LDATE2 | dedup USERNAME | fields USERNAME GROUP ] | join type=outer [search index="index1" sourcetype=table3 source=table3-DH1 | rename message.SID as SID, message.VALUE as ROLE_DATA | eval mvf1 = split(ROLE_DATA, ";") | eval ROLE=trim(mvindex(mvf1,0)), MODIFIED=trim(mvindex(mvf1,1)) | fields ROLE MODIFIED ] |eval AlertStatus=if( MODIFIED = 'X', "RISK", "NO_RISK") | eval _time=strftime(strptime(DATE,"%d.%m.%Y"),"%Y-%m-%d %H:%M:%S")|chart count over _time by AlertStatus</query>
<earliest>0</earliest>
<sampleRatio>1</sampleRatio>
</search>
<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">column</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>
<drilldown>
<set token="token1">$click.name2$</set>
<set token="token2">$click.value$</set>
</drilldown>
</chart>
</panel>
</row>
<row depends="$token1$,$token2$" >
<panel>
<table>
<title>User Roles</title>
<search>
<query>index="index1" sourcetype=table1 source=table1-DH1 | spath | rename message.JOBNAME as JOBNAME, message.JOBSPOOL{}.* as * | mvexpand LINE | search LINE="*Role*" | search LINE="*|*" | eval USERNAME=trim(mvindex(split(LINE,"|"),1)), DATE=mvindex(split(LINE,"|"),2), TIME=mvindex(split(LINE,"|"),3), PERFORMEDBY=mvindex(split(LINE,"|"),4), ACTION=mvindex(split(LINE,"|"),5), ROLE=trim(mvindex(split(LINE,"|"),6)), ROLENAME=trim(mvindex(split(LINE,"|"),7)) | dedup LINE | fields USERNAME DATE TIME PERFORMEDBY ACTION ROLE ROLENAME | table DATE USERNAME ROLE ACTION PERFORMEDBY ROLENAME | join type=outer [search index="index1" sourcetype=table3 source=table3-DH1 | rename message.SID as SID, message.VALUE as ROLE_DATA | eval mvf1 = split(ROLE_DATA, ";") | eval ROLE=trim(mvindex(mvf1,0)), MODIFIED=trim(mvindex(mvf1,1)) | fields ROLE MODIFIED ] |eval AlertStatus=if( MODIFIED = 'X', "RISK", "NO_RISK") | eval date2=strftime(strptime(DATE,"%d.%m.%Y"),"%Y-%m-%d %H:%M:%S")| where AlertStatus="$token1$" AND date2="$token2$" </query>
<earliest>0</earliest>
<latest></latest>
</search>
</table>
</panel>
</row>
</dashboard>

[niketn]

@dkgs refer to one of my older answers to get the earliest and latest time from a time chart drilldown: https://community.splunk.com/t5/Dashboards-Visualizations/Need-help-doing-drilldown-from-linechart/t...

Do up-vote the original thread as well if it helps! 🙂

[dkgs]

@niketn  Thank you. The below works

<drilldown>
<set token="token1">$click.name2$</set>
<set token="token2">$click.value$</set>
<eval token="tokEarliest">$row._time$</eval>
<eval token="tokEarliestString">strftime($tokEarliest$,"%Y-%m-%d %H:%M:%S")</eval>
</drilldown>
</chart>
</panel>
</row>
<row depends="$token1$,$tokEarliestString$" >
<panel>
<table>

[thambisetty]

copy your complete query under User Roles panel and paste it in below URL and click on encode, take the output and replace the output with query in your dashborad. 

NOTE: don't copy your query in below URL if there is sensitive information

https://www.urlencoder.org/

[dkgs]

@thambisetty Apologies, I am new to Splunk drill down. Could you please help how will the encoded code helps in the query

[thambisetty]

did you try with sample _time value taken from parent report and use in child report manually? 

if that works, URL encoding is required for your dependent search query. use any urlencoder. 

[dkgs]

@thambisetty  Could you please help how to do that .

Please do share some working example

Thanks in advance