Splunk Search

Run search only on data for certain time of day

heilman
New Member

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.

0 Karma

woodcock
Esteemed Legend

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).

.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!