topic Re: Show raw data in new search on clicking the timestamp or a value using drilldown in Dashboards & Visualizations

Show raw data in new search on clicking the timestamp or a value using drilldown?

yamini_37 — Thu, 01 Dec 2022 16:20:31 GMT

Hi Everyone,

I have one requirement. Below is my search query to show "no.of users logged in" for every 1 hour.

index=ABC sourcetype=xyz "PROFILE_LOGIN" |rex "PROFILE:(?<UserName>\w+)\-" |bin _time span=1h |stats dc(UserName) as No_Of_Users_Logged_In by _time

I am getting like below:

_time No_Of_Users_Logged_In

2022-11-28 10:00 1

2022-11-28 11:00 2

I want when I click in the first row/timestamp/ No_Of_Users_Logged_In, it should show the raw logs of the events where the logged-in usernames are present in that particular time (if the time stamp is 10:00, then it should show raw events from 10:00 to 11:00). These events should open in new search .

Also, can you guide me how to view these in panel below the table using drilldown. It should be only show when we click on the values. (It’s an additional request to know the possibility)

Please guide and help me.

xml code snippet :
<row>
<panel>
<title>Number of Users Logged In</title>
<table>
<search>
<query>index=ABC sourcetype=xyz "PROFILE_LOGIN" |rex "PROFILE:(?<UserName>\w+)\-"
|bin _time span=1h |stats dc(UserName) as No_Of_Users_Logged_In by _time</query>
<earliest>$time_token.earliest$</earliest>
<latest>$time_token.latest$</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">6</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">none</option>
</table>
</panel>
</row>

Re: Show raw data in new search on clicking the timestamp or a value using drilldown

ITWhisperer — Wed, 30 Nov 2022 12:16:58 GMT

The value from the first column of the table is stored in the $value$ token. Use this in the search as earliest and use the value +1 hour as latest.

Re: Show raw data in new search on clicking the timestamp or a value using drilldown

yamini_37 — Wed, 30 Nov 2022 14:50:05 GMT

@ITWhisperer

Thanks for the response, but I didn't understand it clearly. Below is the XML code of that panel. Could you please make changes in this xml snippiet.

<row>
<panel>
<title>Number of Users Logged In</title>
<table>
<search>
<query>index=ABC sourcetype=xyz "PROFILE_LOGIN" |rex "PROFILE:(?<UserName>\w+)\-"
|bin _time span=1h |stats dc(UserName) as No_Of_Users_Logged_In by _time</query>
<earliest>$time_token.earliest$</earliest>
<latest>$time_token.latest$</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">6</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">none</option>
</table>
</panel>
</row>

Re: Show raw data in new search on clicking the timestamp or a value using drilldown

yamini_37 — Wed, 30 Nov 2022 14:51:15 GMT

@somesoni2, @jeffland , please help

Re: Show raw data in new search on clicking the timestamp or a value using drilldown

ITWhisperer — Wed, 30 Nov 2022 15:32:48 GMT

Try something like this

<row> <panel> <title>Number of Users Logged In</title> <table> <search> <query>index=ABC sourcetype=xyz "PROFILE_LOGIN" |rex "PROFILE:(?<UserName>\w+)\-" |bin _time span=1h |stats dc(UserName) as No_Of_Users_Logged_In by _time</query> <earliest>$time_token.earliest$</earliest> <latest>$time_token.latest$</latest> <sampleRatio>1</sampleRatio> </search> <option name="count">6</option> <option name="dataOverlayMode">none</option> <option name="drilldown">cell</option> <drilldown> <eval token="earliest">$value2$</eval> <eval token="latest">relative_time($value2$,"+1h")</eval> <link target="_blank">search?q=search%20index%3DABC%20sourcetype%3Dxyz%20%2CPROFILE_LOGIN%2C&earliest%3D$earliest$&latest%3D$latest$</link> </drilldown> </table> </panel> </row>

Re: Show raw data in new search on clicking the timestamp or a value using drilldown

yamini_37 — Wed, 30 Nov 2022 18:11:02 GMT

@ITWhisperer

earliest and latest tokens are not working. When I clicked on the cell, the new tab got opened with the search query " index=ABC sourcetype=xyz "PROFILE_LOGIN" and time picker set to "last 15 minutes". I tried modifying the time tokens but no luck.

Plz help

Re: Show raw data in new search on clicking the timestamp or a value using drilldown

ITWhisperer — Wed, 30 Nov 2022 18:32:54 GMT

<eval token="earliest">$click.value$</eval> <eval token="latest">relative_time($click.value$,"+1h@h")</eval>

Re: Show raw data in new search on clicking the timestamp or a value using drilldown

yamini_37 — Thu, 01 Dec 2022 05:31:21 GMT

@ITWhispererI tried this too, it's not working (picture attached). Any other suggestions, please?