Splunk Search

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

splgeek
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

niketn
Legend
  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.

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

View solution in original post

aaraneta_splunk
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

niketn
Legend
  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.

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

gokadroid
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...