How to fetch results to a table whose columns are sorted by date AND a timeframe of that date?

[lmonahan]

I have a test that I run between 1am and 6am each night. I am able to fetch the results for the last 21 days using this query:

index=myindex host=host1 OR host=host2 event=“*event1*” OR event=“*event2*” earliest=-21d latest=now 
| eval date=strftime(_time, "%Y-%m-%d") 
| eval rounded_mean = round(‘my data’, 2) 
| chart perc50(rounded_mean) over name by date limit=0

This query results in a table with a column for each date and a row for each event.

However, sometimes I run the test during the day for debugging purposes and I want to omit any of this daytime data by fetching only the results from 1am to 6am.

How can I refine the results further with a criteria that says "For each day of the last 21 days, between the hours of 1am and 6am, fetch the results"?

[lmonahan]

Thank you @dmarling ! Yes I think your solution will work. In my data I am not receiving data_hour so I cannot use it.

My workaround for now is similar: use eval to get the hour and perform a subsearch based on it.

Here is my query:

index=myindex host=host1 OR host=host2 event=“event1” OR event=“event2” earliest=-21d latest=now
| eval hour=strftime(_time, "%H")
| eval date=strftime(_time, "%Y-%m-%d")
| search hour<=3
| eval rounded_mean = round(‘my data’, 2)
| chart perc50(rounded_mean) over name by date limit=0

[dmarling]

Glad that works. You can also use a where command to trim out a line:

index=myindex host=host1 OR host=host2 event=“event1” OR event=“event2” earliest=-21d latest=now
| eval date=strftime(_time, "%Y-%m-%d")
| where tonumber(strftime(_time, "%H"))<=3
| eval rounded_mean = round(‘my data’, 2)
| chart perc50(rounded_mean) over name by date limit=0

[dmarling · Accepted solution]

If your goal is to limit the base data to only look from 1-6 am each of the past 21 days this will accomplish that:

index=myindex date_hour>=1 date_hour<7 (host=host1 OR host=host2 event=“event1” OR event=“event2”) earliest=-21d latest=now 
| eval date=strftime(_time, "%Y-%m-%d") 
| eval rounded_mean = round(‘my data’, 2) 
| chart perc50(rounded_mean) over name by date limit=0