I wanted to establish an alert that will look at the past hour for the past 6 weeks and make some comparisons. So for 2 PM I will have 6 results for week 1,2,3.... I know I can use date_hour but I need something that I can set when the alert runs, so for the last 60 minutes somehow.
index=text source=text date_hour=14 | timechart span=1h count
Is there a way I can do this more easily?
That is exactly what I wrote, yet relying on the time selector instead of explicitly writing it. My issue I want to solve is that I want 14 to change, I want the alert to check the previous 60 minutes of each week. Currently I have a multisearch; 6 lines for 6 weeks. It's not efficient at all and i wanted to rework it. I need the 14 to be an easily changed number.
Try something like this:
| eval current_hour=strftime(now(), "%I") | eval dh=current_hour-1 | where date_hour=dh | timechart span=1w count
Obviously, you'll need to handle the special case of midnight.
Also, this is still pretty inefficient and can definitely be improved not to read all events only to filter out the hour in question, but it should show how to make the date_hour dynamic. I am not a search expert by any means, so there maybe an easier way to do this.
I would consider a summary index that is populated hourly/nightly and base your by-hour reporting on that.
You can use time modifiers in your search (docs😞
index=text source=text date_hour=14 earliest=-6w | timechart span=1h count
Will give you the last 6 days. If this is something you want to run regularly, you could create a saved search that runs nightly (or weekly), counts what you want to count and store it in a summary index. Your alert search will run much faster that way. It may also be possible to rewrite your search using tstats if you only need to look at metadata fields (like time). date_hour is a search time extraction, so you couldn't use that with tstats.
You can also look at using INGEST_EVAL to create an indexed field that contains the hour component of your timestamp at ingest time. Searches that can use just indexed fields are much more efficient and if this is not a one-off use case, the extra work at ingest time will be worth the benefits.