running a search for 30 mins before and after the clicked time as drilldown

[ManishVilla7]

Hi All,

I am trying to create a drilldown for my timechart, the idea is to drill down to the events that happened 30 mins before and after the clicked time (click.value in my case). I referred the answer
https://answers.splunk.com/answers/215176/subtracting-30-minutes-from-passed-drilldown-param.html?ut...

Now this is helping me pass the earliest time, but when i use the same concept with the latest time i am not getting the desired result. Below is my current query:

index="tutorial" categoryId=strategy earliest=[|gentimes start=-1|eval new = relative_time($click.value$,"-1800")| return $$new] latest=[|gentimes start=-1|eval latest1 =($click.value$ + 1800)| return $$latest1] | top clientip

after passing through the drilldown pipeline the query changes to:

index="tutorial" categoryId=strategy earliest=[|gentimes start=-1|eval new = relative_time(1572728400.000,"-1800")| return $new] latest=[|gentimes start=-1|eval latest1 =(1572728400.000 1800)| return $latest1] | top clientip

If you look closely the + sign just vanished. I tried many ways to find a work around for that but wasn't able to. Please take a look and try to run the query once by yourself.

@Raghav2384 @RVDowning @somesoni2 @woodcock @gcusello @mayurr98 please help guys!!

[rmmiller · Accepted solution]

(UPDATED)
Got it, thanks for the additional detail. The "+" is a special character when it comes to URL's, and it was being translated/stripped when the drilldown tab is opened as a new window.

I used eval arithmetic on $click.value$, but I converted the + symbol to hex with the help of http://www.asciitable.com/
The hex value for a + sign is 2B, but you must put % in front of it when used in URL's.
This is my drilldown search.

index="tutorial" categoryId=strategy earliest=[|gentimes start=-1|eval new = ($click.value$ - 1800)| return $$new] latest=[|gentimes start=-1|eval latest1 = ($click.value$ %2B 1800)| return $$latest1] | top clientip

Note the + is replaced with %2B in this updated query.

Hope that helps!
rmmiller

[ManishVilla7]

@rmmiller , your help is very much appreciated.
Can you please try to pass the search and open that in another search window. Because i am still facing the issue in getting the drilldown to open another search window with the following search:
index="tutorial" categoryId=strategy earliest=[|gentimes start=-1|eval new = relative_time($mycentertime$,"-1800")| return $$new] latest=[|gentimes start=-1|eval latest1 = relative_time($mycentertime$,"+1800")| return $$latest1] | top clientip

I tried all 3 though, but the "+" sign was still vanishing.

[rmmiller]

@ManishVilla7 did this updated solution solve your problem?

rmmiller

[ManishVilla7]

@rmmiller just checked, works fine. good work!!

cheers

[rmmiller]

@ManishVilla7 Great to hear! Please accept the answer so everyone will know this is closed.
Happy you're on your way!
rmmiller

[rmmiller]

Thanks @ManishVilla7 for the additional detail, that helped figure out what was happening.
rmmiller

[aberkow]

Have you tried setting the token drilldown in the XML, and then using that variable? That works for me. However, I'm not using the gentimes command, instead just using the earliest and latest in a where clause downstream.

Example:


<set token="time">$click.value$</set>


K, I can't get the drilldown tags to format but that should be within drilldown tags in the XML

[rmmiller]

Just curious... I see you used relative_time in your calculation of earliest, but straight up arithmetic for calculation of latest. Do you get the same results if you use relative_time for latest, too?

[ManishVilla7]

yes @rmmiller, the problem remains the same. The main issue is that when the search goes through the drilldown hammer, the "+" sign somehow vanishes.