Splunk Search

Capture peak hour and use the same hour in a sub search

Allampally
Path Finder

I am preparing a volume report for my project. My requirement is to capture the peak hour (hour which has highest calls ) with date and time and pass the same date and time in sub search to get statistical data.
My search should be like below
(query to get the peak hour) | (sub search with stats command with duration of peak hour)

I want to print peak hour and with statistical out put in a single query. Any suggestions, how to get this thing ?

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
index=App [| search index=App | bucket _time span=1h | stats count as CallsByHour by _time | sort - CallsByHour | head 1 | eval earliest=_time | eval latest=_time + 3600 | fields earliest, latest ] | stats count by Transaction 

View solution in original post

0 Karma

isoutamo
SplunkTrust
SplunkTrust
You could use command map to launch second search after you have gotten times for it.
0 Karma

Allampally
Path Finder

I will simpify my question, Below is the my basic query which captures peak hour volume and _time. 

index=App  | bucket _time span=1h | stats count as CallsByHour by _time | sort - CallsByHour | head 1

 

Now, I want to write another query based on the captured time in the above query as earliest=_time and latest=_time + 3600.

Eg. index=App earliest=_time latest=_time + 3600 | stats count by Transaction. 

I want to merge above two queries and produce Transactions as final output for the time range which captured from the first query

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Sub-searches run before main search so, rather than piping one search into another, you may need to do this the other way round

--- your search
[| search again with wider time period e.g. earliest=-1d@d latest=@d | bin span=1h _time | stats count (or whatever to evaluate peak) by _time | sort - count | head 1 | eval earliest=_time | eval latest=_time + 3600 | fields earliest, latest ]

 

0 Karma

Allampally
Path Finder

Somehow, its not working. Are you asking me to write same search query between "

search again with wider time period e.g. earliest=-1d@d latest=@d 

" which i had writeen at starting to fetch data ? 

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Essentially, yes. It is whatever query you use to determine what the peak hour is. You haven't said how you determine the peak, or what period you are looking over for the peak. The example I gave is to look for a peak (based on the count of events by hour) in the previous day. The idea is to find the peak by doing some stats in 1hr buckets and sort them so that the peak hour is the first (and only result once head 1 has been applied). You then take the _time of this result and use that as the earliest and the _time + 3600 seconds as the latest time. These are then the only fields returned from the sub-query and are used as parameters to your detailed query which could indeed be the same query or at least similar query.

0 Karma

Allampally
Path Finder

I hope, you had seen my last comment where i have provided a query to find peak hour and written another query which i need to extract Transactions based on time captured

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
index=App [| search index=App | bucket _time span=1h | stats count as CallsByHour by _time | sort - CallsByHour | head 1 | eval earliest=_time | eval latest=_time + 3600 | fields earliest, latest ] | stats count by Transaction 
0 Karma

Allampally
Path Finder

I need one more help from you. I am using the query in dashboard with base search. As you know, while using base search, we should replace base query with "search". But in the query, I should use base search two time. One is at the starting and second is within the square bracket. My question is, how can use base search inbetween sqaure bracket ?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I am not sure what you are asking - the outer search has an implied search command at the beginning so these two searches effectively start the same way in your example. Having said that, if I understand your example, you want the count by Transaction for the busiest hour. It might be better to do that this way

index=App 
| bucket _time span=1h 
| stats count by _time, Transaction 
| eventstats sum(count) as total by _time 
| eventstats max(total) as busiest
| where total=busiest

 

0 Karma

Allampally
Path Finder

Thank you Rock star 🙂 It worked for me 

0 Karma

Nisha18789
Builder

@Allampally , can you provide the separate queries for both the data? If yes, could you provide that as well.

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