Hello,
I am attempting to run a search that will only include data occurring before 6 AM or after 6 PM, then group the data by 12 hour time range and country, and sum the total amount for each time period. For example, I want the sum of the data for each country in time periods such as Monday 6 PM - Tuesday 6 AM, Tuesday 6 PM - Wednesday 6 AM, etc.
This is the code I have:
host=10 AND (date_hour<6 OR date_hour>18) | bucket _time span=12h | stats sum(AMOUNT) as total by COUNTRY _time
This query is limiting my data to only those that occured before 6 AM or after 6 PM, but it reporting is as Monday 8 PM - Tueday 8 AM (with the data between Tuesday 6 AM-8AM excluded), Tuesday 8 AM - Tuesday 8 PM (with the data between T 8 AM and 6 PM excluded), etc. Is there any way so that the reported time frame is from 6 to 6, instead of from 8 to 8?
Thank you.
If you search is really correct (highly questionable, IMHO, because the date* fields are pre-TZ-normalized fields), then you can shift the time like this:
host=10 | eval _time=_time - 2*60*60 | eval date_hour=strftime(_time, "%H") | where (date_hour<6 OR date_hour>18) | bucket _time span=12h | stats sum(AMOUNT) as total by COUNTRY _time
This recalculates date_hour
to be sure it is correct (and so you may not even need the time shift, because this was probably your original problem).