Splunk Search

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

Explorer

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 ?

0 Karma
1 Solution

SplunkTrust
SplunkTrust
  1. Add datehour>=0 AND datehour<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 datemday.

    &lt Your Index and Source Type > datehour>=0 AND datehour<9 | eval durationBins=case(date_hour>=0 AND datehour<6,"00:00 AM - 06:00 AM",datehour>=6 AND datehour<9,"06:00 AM - 09:00 AM") | chart count over datemday 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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

Splunk Employee
Splunk Employee

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

0 Karma

SplunkTrust
SplunkTrust
  1. Add datehour>=0 AND datehour<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 datemday.

    &lt Your Index and Source Type > datehour>=0 AND datehour<9 | eval durationBins=case(date_hour>=0 AND datehour<6,"00:00 AM - 06:00 AM",datehour>=6 AND datehour<9,"06:00 AM - 09:00 AM") | chart count over datemday 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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

Motivator

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

0 Karma