How to refine the time range within a search?

[svercelli]

I'm trying to select a specific custom time range within a search after selecting a larger time range with the time picker and narrowing down the results using eventstats. What would be the way to do this exactly? My search looks like this

index=bhs sourcetype=BHS_LBT_BAG |addinfo| eventstats earliest(_time) as FirstAppearance  by BAGTAGID | where FirstAppearance=_time 

In this case the second time range I want to narrow it down to is the earliest being 5AM the previous day up to 5AM the current day.

[woodcock]

Like this:

index=bhs sourcetype=BHS_LBT_BAG | eventstats min(_time) as FirstAppearance  by BAGTAGID | where FirstAppearance=_time
| where = FirstAppearance>=relative_time(now(), "-1d@d+5h") AND FirstAppearance<=relative_time(now(), "@d+5h")

[aaraneta_splunk]

@svercelli - Did the answer provided by DalJeanis help provide a working solution to your question? If yes, please don't forget to resolve this post by clicking "Accept". If no, please leave a comment with more feedback. Thanks!

[DalJeanis]

As long as you are only narrowing the range within the current search, all you have to do is calculate your new time bounds and then use search or where, just like you've done. The code you've posted should work to select only the earliest transaction for each BAGTAGID.

I'm assuming that what you want is to select all the events for each BAGTAGID that start before 5 AM the day before the first event and that end before 5 AM the next day. However, since that is the first transaction for each BAGTAGID, there will never be anything before that one, so you really only need to calculate the end date/time.

If this event is at 2AM, then 5AM (3 hours later) is the correct end date. If this event is at 2 PM, then 5AM the next day is the correct end date. So, from our given time, we need to subtract 5 hours, take only the day portion, and then add 29 hours. Alternately, we could add 19 hours, take the day portion, and then add 5 hours. I'll code this latter version.

| eval cutoffdate = relative_time(relative_time(FirstAppearance,"+19h@d"),"+5h") 
| where _time <= cutoffdate

Note that this means an event at 4:30 AM will only have half an hour of trailing events retained