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!

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