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