How can I search for a combination of relative and absolute times?

[Glenn]

I have a requirement to create a dashboard for a team's morning checks. It needs to search for logs between 18:00 the day before and 08:00 the morning of the day that the search is run (ie. the most recent 18:00-08:00 period. I was surprised to find out that this was harder than I expected due to difficulties specifying this range correctly. It needs a combination of absolute (18:00-08:00) and relative times (the last time this period occurs).

The best I could do was to bring back everything from the last 18 hours, and then search for only the events matching a certain date_hour (I didn't choose 24 hours because this would probably result in overlap, and I now have to hope that the morning checks are run in the morning). This means that I am using a combination of the time range and the search terms to do it, which is odd. Also it is inefficient (I think) because it searches over a larger range than necessary, plus there are some other drawbacks.

Is there a better way to do this? Am I missing something?

<table>
  <searchString>sourcetype="syslog_forward" tag::host="netapp" host=bnprdfls011.igi.ig.local (date_hour=18 OR date_hour=19 OR date_hour=20 OR date_hour=21 OR date_hour=22 OR date_hour=23 OR date_hour=0 OR date_hour=1 OR date_hour=2 OR date_hour=3 OR date_hour=4 OR date_hour=5 OR date_hour=6 OR date_hour=7) NOT eventtype=netapp_ignore AND NOT msgsev=info</searchString>
  <title>bnprdfls011.igi.ig.local</title>
  <fields>_time, msgsev, msgtype, msgtext</fields>
  <earliestTime>-18h</earliestTime>
  <option name="drilldown">row</option>
  <option name="count">20</option>
  <option name="showPager">true</option>
  <option name="displayRowNumbers">false</option>
</table>

Before you answer that I should use a scheduled saved search to run at 08:00 over the last 14 hours, I am looking into this as well, but am facing a problem in that cached results are NEVER used (searches are always run when someone accesses the dashboard, meaning the timer period is wrong), which I will ask another question about next.

Cheers,

Glenn

[melting]

Two ideas:

1) booleans work with earlieast and latest:
ie: ((earliest="11/09/2010:00:00:00" latest="11/12/2010:23:59:59") OR (earliest="12/15/2010:00:00:00" latest="12/16/2010:23:59:59"))

2) you can append another searches results to the first:
ie: * earliest="11/09/2010:00:00:00" latest="11/12/2010:23:59:59" | append [search * earliest="12/15/2010:00:00:00" latest="12/16/2010:23:59:59"]

[dwaddle]

Did you try the approach I showed above? In my tests, it works every time for getting the timerange of "18:00 yesterday to 08:00 this morning" - which is what I thought you were trying to do.

[Glenn]

The problem is, I don't access to those dates to modify in the search string. It needs to be able to run any day, and bring back results from that morning and the night before.

[dwaddle · Accepted solution]

This isn't mixing absolute and relative, but it should hit what you're looking for.

earliest=-1d@d+18h latest=@d+8h

Basically, this takes advantage of how relative time can be rounded to the top of the (hour/day/month) and then adding/subtracting from there.

[Glenn]

I thought I had tried this! Obviously not quite correctly. This works well thanks.