How to create a stats search that will show results based on two different time slots?

[splgeek]

hello all
i want to run a search with a stats count that will show results based on two separate time slots

Stats Count - 12 Am to 6 AM
Stats Count - 6 Am to 9 AM

is there anyway to put together a search for this ?

[aaraneta_splunk]

@splgeek - Did one of the answers below help provide a working solution? If yes, please don't forget to click "Accept" below the best answer to resolve your question. If no, please leave a comment to provide more feedback. Thanks.

[niketn · Accepted solution]

1. Add date_hour>=0 AND date_hour<9 condition to your base query to filter unwanted hours.
2. Use case statement to create two bins for 0 to 6 AM and 6 to 9 AM respectively.
3. Finally use charts to count events for the two bins.

4. You can add over date_mday, if you are calculating for wide date range and want to split per day. You can modify _time using eval, strftime and time modifiers to have variety of timestamps to use instead of date_mday.

   &lt Your Index and Source Type > date_hour>=0 AND date_hour<9 | eval durationBins=case(date_hour>=0 AND date_hour<6,"00:00 AM - 06:00 AM",date_hour>=6 AND date_hour<9,"06:00 AM - 09:00 AM") | chart count over date_mday by durationBins

PS:
1. Append, Appendcols, Join can also be used however, they will have performance issues specially if there are several thousand events returned for each of the two bins per day for a long range of data like a month.
2. Using case statements you can add remaining hour bins and default bin as well. You would need to adjust date_hour filter to main search accordingly.

[gokadroid]

If you are doing it for current day try to adjust the earliest and latest to suit your needs with bucket and then use append to get the second time duration search to produce the counts. Try something like this for current day:

index=yourIndex sourcetype=yourSourcetype earliest=@d latest=@d+360m| bucket _time span=6h| stats count 
| append [search  index=yourIndex sourcetype=yourSourcetype earliest=@d+360m latest=@d+540m| bucket _time span=3h | stats count]

If you want to change the search to some date or day back in time, then just adjust the time modifiers earliest and latest with respective +/-d@d modifiers as mentioned in the documentation link here