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

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...