How To Table The Average Of A Field Created Using Eval?

[vtsguerrero]

I have in my index field StartTime and EndTime
I used this command to create the duration:

index=Main Channel=* StartTime=* EndTime=*
| eval Duration = EndTime-StartTime
| eval Duration = strftime(Duration/1000 , "00:%M/:%S:%3Q")
| stats count by Channel
| stats avg(Duration) by Channel

Considering that values are in Epoch format, how can I table Duration Per Groupped Channel?

Thanks In Advance!

[sideview · Accepted solution]

If you want just straight average durations by channel, I think you want something like this:

index=Main Channel=* StartTime=* EndTime=*
 | eval Duration = EndTime-StartTime
 | stats avg(Duration) as AvgDuration by Channel
 | eval AvgDuration = strftime(AvgDuration/1000 , "%H:%M:%S:%3Q")

If instead you want to keep the individual rows the same, but add in an extra column for each, saying what the average duration for al the rows that have that value for "Channel", then you want this:

index=Main Channel=* StartTime=* EndTime=*
 | eval Duration = EndTime-StartTime
 | eventstats avg(Duration) as AvgDuration by Channel
 | eval AvgDuration = strftime(AvgDuration/1000 , "%H:%M:%S:%3Q")
 | eval Duration = strftime(Duration/1000 , "%H:%M:%S:%3Q")

UPDATE:

This is summing up the comment thread below as an update - If you want to roll up the results to where each channel is a row, and each row has the global average duration across all channels, and each row has the average for the given channel, and also the deviation of that channel's average duration from the global population average, with all these durations formatted as "hh:mm:ss", then you want:

index=Main Channel=* StartTime=* EndTime=*
| eval Duration = EndTime-StartTime
| eventstats avg(Duration) as globalAverageDuration
| stats avg(Duration) as AvgDuration last(globalAverageDuration) as globalAverageDuration by Channel
| eval Difference = (AvgDuration - globalAverageDuration)/1000
| eval AvgDuration = strftime(AvgDuration/1000 , "%H:%M:%S:%3Q")
| eval globalAverageDuration = strftime(globalAverageDuration/1000 , "%H:%M:%S:%3Q")
| eval differenceParity=if(Difference>=0,"+","-") 
| eval Difference=abs(Difference) 
| eval Difference=differenceParity + strftime(Difference/1000, "%H:%M:%S:%3Q")

[vtsguerrero]

What if I need the table to show also the difference? How can I use epoch times this way?

Example:

 | Channel | Time Elapsed | Average Time | Difference Time

 Ch 1 00:00:135 | 00:00:200 | -00:00:065

[sideview]

index=Main Channel=* StartTime=* EndTime=*
  | eval Duration = EndTime-StartTime
  | streamstats avg(Duration) as AvgDuration by Channel
  | eval Difference = (Duration - AvgDuration)/1000
  | eval AvgDuration = strftime(AvgDuration/1000 , "00:%M/:%S:%3Q")
  | eval Duration = strftime(Duration/1000 , "00:%M/:%S:%3Q")

Will give you the difference as an integer number of seconds. You can't strftime it quite as easily as these others, since strftime wont accept negative integers as input. So we have to be more clever.

index=Main Channel=* StartTime=* EndTime=*
  | eval Duration = EndTime-StartTime
  | streamstats avg(Duration) as AvgDuration by Channel
  | eval Difference = (Duration - AvgDuration)/1000
  | eval AvgDuration = strftime(AvgDuration/1000 , "00:%M/:%S:%3Q")
  | eval Duration = strftime(Duration/1000 , "00:%M/:%S:%3Q")
  | eval differenceParity=if(Difference>=0,"+","-") 
  | eval Difference=abs(Difference) 
  | eval Difference=differenceParity + strftime(Difference/1000, "00:%M:%S:%3Q")

[vtsguerrero]

Sorry to bother again, but what about if I also want to group this table one channel per line?
For example line one for comparisson only with Ch1, line two Ch2 and so on....
Thanks a lot @sideview it helped a lot!

[sideview]

Well, unless I'm misunderstanding, if you want to end up with one channel per line, it's the first search I suggested in my answer, and it's MUCH simpler.

index=Main Channel=* StartTime=* EndTime=*
| eval Duration = EndTime-StartTime
| stats avg(Duration) as AvgDuration by Channel
| eval AvgDuration = strftime(AvgDuration/1000 , "00:%M/:%S:%3Q")

but of course then there's no "difference from average" cause each line just has the average.

[vtsguerrero]

Can I show the same average for all with the sum stats and then just show the difference per channel?
I mean one table with a columm for same average for all...

[sideview]

oh goodness. That's quite different. I'll update my actual answer.

[vtsguerrero]

Whoooooa @sideview , that's exactly what I needed!
Sorry if I didn't know how to express it from the beginning...
Now I just need to show the deviation in a timechart graphic but I think it won't be hard...
Thanks again buddy!

[vtsguerrero]

Hello Again!
Still Miss One Thing Here, It's Currently Showing A EventStats table, but let's suppose I need to save these deviation and average into a new eval field, and use these with other fields in a table command?
How can I use both pre-saved fields for a table command?

[sideview]

There's some ambiguity in your last question, but I think the best thing is for you to play around with eventstats vs stats. Basically eventstats keeps the incoming rows the same (ie doesn't transform them), and just paints extra fields onto those rows.

The answer to your last question may involves changing | stats avg(Duration) as AvgDuration last(globalAverageDuration) as globalAverageDuration by Channel to | eventstats avg(Duration) as AvgDuration last(globalAverageDuration) as globalAverageDuration by Channel. eventstats with a by clause is quite subtle in that it will keep the rows the same, but do the calculations for the extra fields such that all rows with Channel="A" are treated together and will get the same extra field values.

[vtsguerrero]

Now it worked perfectly!
Very good explanation!
I just didn't know I could add more columms or use two average, one general and one per channel.
Thanks a lot @sideview! 😄

[somesoni2]

I see two duration related field in your expected output. Avg Duration is fine, what does "Duration" field contains.
Also, get rid of your first stats (count by channel) and move your eval-strftime after last stats.

[vtsguerrero]

The Duration field doesn't really exist in the database, it should show the real time taken for each action, so I took the finish time and start time to calculate the "between time" and show in a table the average time it should use and the effective time it did use.

[icyfeverr]

Can you provide an example of how you expect the output to look?

[vtsguerrero]

It should look like this:

| Channel | Duration | Average
| Ch A | 00:00:132 | 00:00:100
| Ch B | 00:00:225 | 00:00:320
| Ch C | 00:00:168 | 00:00:090

In a table format with more fields that are still not ready, but these are the most important.