Performing time restrictions in a subsearch

[phoenixdigital]

Hi All,

Having a sticky issue with adding another time restriction after the primary search.

The data we have that comes in is hourly... however the official start day for recording the data is 6am. So we need to shift _time by six hours when performing searches.

What we would like to do is this

index="myIndex" Identifier="53xxxx15"  earliest="-10mon@mon" latest="-1mon@mon" Temperature=*  | dedup _time, Identifier sortby -_indextime | eval _time = relative_time(_time, "-6h") | timechart span=1mon sum(TheDataReportedHourly) as Monthly_Totals

Please note the dedup is required as there are multiple data points for the same hour and identifier. We only want to use the most recent.

Now obviously the above searches earliest and latest is locking to the month before the time shift occurs so data is being lost.

We have attempted to add an earliest and latest later on in the search but it returns no data

index="myIndex" Identifier="53xxxx15"  earliest="-11mon@mon" latest="-1mon@mon" Temperature=*  | dedup _time, Identifier sortby -_indextime | eval _time = relative_time(_time, "-6h") | search * earliest="-10mon@mon" latest="-2mon@mon" | timechart span=1mon sum(TheDataReportedHourly) as Monthly_Totals

I have tried to have the second search do things like _time<"A date" or _time<anEpoch but these fail as well.

ie

index="myIndex" Identifier="53xxxx15"  earliest="-10mon@mon" latest="-1mon@mon" | dedup _time, Identifier sortby -_indextime | eval _time = relative_time(_time, "-6h") | eval afterDate=strptime("1 Jan 2012","%d %b %Y") | eval beforeDate=strptime("1 Dec 2012","%d %b %Y") | eval timeEpoch=strptime(_time,"%d/%b/%Y %H:%M:%S") | search _time>afterDate AND _tim<beforeDate | table _time, timeEpoch, afterDate, beforeDate

Anyone have any ideas how I can shift _time then perform another _time restriction?

[landen99]

I understand that this question is a bit old but it appears to contain good educational value in its current context. Here are a few notes on your last search to help you understand what that search is doing and why it may be returning unexpected results:
Your first earliest and latest specify between 10 months ago and one month ago.
You then change the event times (at search time) to 6 hours earlier
You use strptime(X,Y) and give it a string and tell it to convert it to epoch time for afterdate and beforedate. You give the same function the _time variable which is in epoch time already and not a string even though it displays as a string in tables (by Splunk's standard procedure), etc. I am not sure what that actually does to _time but assuming that Splunk ignores the function, you tell Splunk to search on that time with the current results. No result will match independent search ranges unless the earliest and latest of each set overlap on the time period of the event whose time value was altered in the search (non-permanent/non-indexed). You may be looking to run a separate search and to join it to the current results using either append or join.

[jonuwz · Accepted solution]

You dont have to snap straight to the month, this is valid :

earliest="-11mon@mon+6h" latest="-1mon@mon+6h" 

Then you can do :

... | eval _time=_time-(3600*6)

[phoenixdigital]

Perfect! Thanks for that. Didn't know you could put more information onto earliest and latest like that.

The manipulation of _time is brilliant too. I was certain when I was initially building this search I tried manipulation of _time that way but it didn't work. I was obviously mistaken.

Thanks again