Splunk Search

How to use stats range(_time) and pass the results to timechart

mkatta
New Member

I have data where every line has a timestamp and a correlationID. I can find the time elapsed for each correlation ID using the following query.

index=yyy sourcetype=mysource CorrelationID=* | stats range(_time) as timeperCID by CorrelationID, date_hour | stats count avg(timeperCID) as ATC by date_hour | sort num(date_hour)

I want to use timechart and timewrap on this data to be able to eventually get a week over week comparison of the output. I tried adding a timechart at the end but it does not return any results.

1) index=yyy sourcetype=mysource CorrelationID=* | stats range(_time) as timeperCID by CorrelationID, date_hour | stats count avg(timeperCID) as ATC by date_hour | sort num(date_hour) | timechart values(ATC)

2) index=yyy sourcetype=mysource CorrelationID=* | stats range(_time) as timeperCID by CorrelationID, date_hour | timechart count avg(timeperCID) as ATC

I've also tried to add a _time value or recreate it using the strptime before the timechart with no luck.

Please help

0 Karma
1 Solution

niketn
Legend

@mkatta, from code provided in the question seems like you are trying to find the duration of a transaction based on CorrelationID and then plot the average duration of all transactions on the timechart.

index=yyy sourcetype=mysource CorrelationID=* 
| stats count as eventCount earliest(_time) as earliestTime latest(_time) as latestTime by CorrelationID
| eval duration=latestTime-earliestTime
| search eventCount>1
| eval _time=earliestTime
| timechart span=1d avg(duration) as avgDuration 
| fillnull value=0 avgDuration
| eval avgDuration=round(avgDuration,1)
| timewrap 1week
| fillnull value=0
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@mkatta, from code provided in the question seems like you are trying to find the duration of a transaction based on CorrelationID and then plot the average duration of all transactions on the timechart.

index=yyy sourcetype=mysource CorrelationID=* 
| stats count as eventCount earliest(_time) as earliestTime latest(_time) as latestTime by CorrelationID
| eval duration=latestTime-earliestTime
| search eventCount>1
| eval _time=earliestTime
| timechart span=1d avg(duration) as avgDuration 
| fillnull value=0 avgDuration
| eval avgDuration=round(avgDuration,1)
| timewrap 1week
| fillnull value=0
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

mkatta
New Member

This works, I was hoping to avoid keeping these times saved and use the range command. Looks like that is not that straight forward. Thanks for the quick response, I was able to get the results I was looking for.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...