Splunk Search

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

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

SplunkTrust
SplunkTrust

@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

SplunkTrust
SplunkTrust

@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

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