Stats into timechart

[wormfishin]

I'm running a query for a 1 hour window. I need to group events by a unique ID and categorize them based on another field. I can do this with the transaction and timechart command although its very slow.
transaction keepevicted=true UniqueID | timechart span="5m" limit=10 avg(duration) by myTypes

I'm trying to reproduce this output using stats but I need the data broken down into 5 minute intervals for each type of transactions. Here is what I have now that contains the final data I need. stats range(_time) as UniqueID_Durations first(_time) by myTypes UniqueID

I want to split this into a timechart using the first(_time) and have the time chart caclulate the average UniqueID_Duration for each myTypes.

I feel like it should be this, but it does not work.

stats range(_time) as UniqueID_Duration first(_time) by myTypes UniqueID | timechart span=5m avg(UniqueID_Duration) by myTypes.

Any suggestions?

[MuS · Accepted solution]

Hi wormfishin,

the timechart command uses _time of your event which is not available anymore after your stats. You could try something like this :

stats range(_time) as UniqueID_Duration first(_time) as myTime by myTypes UniqueID | chart span=5m avg(UniqueID_Duration) over myTime by myTypes

this is un-tested, but should work....

cheers, MuS

[wormfishin]

eventstats actually worked better as it displayed the time in string format instead of epoch. Thanks, that was exactly what I needed.

[gordo32]

agreed. I had a similar issue also resolved by eventstats

[MuS]

or simply use eventstats instead of stats and _time will stay in your results 😉