topic Re: Average on time only, without considering date in Getting Data In

Average on time only, without considering date

andreafebbo — Fri, 23 Jun 2017 13:46:45 GMT

I have this query:

 Base quey
| eval EndTime = if(Result="OK", _time, null)
| eval StartTime = if(LogType ="START", _time, null)
| stats Latest(StartTime) as StartTime Latest(EndTime) as EndTime by PackageName ExecutionInstanceGUID
| eval Duration = (EndTime-StartTime)
| where (Duration != "" OR Duration >= 0)
| stats avg(EndTime) as AVGEndTime avg(Duration) as AVGDuration avg(StartTime) as AVGStartTime by PackageName
| fieldformat AVGStartTime = strftime(AVGStartTime,"%H:%M:%S") 
| fieldformat AVGEndTime = strftime(AVGEndTime,"%H:%M:%S")

My problems is that in calculating the average on AVGStartTime and AVGEndTime he considers the day also.
I'd like the average to be made just on the hour, minutes, seconds, not on the day.

Thanks.

Re: Average on time only, without considering date

woodcock — Fri, 23 Jun 2017 13:58:54 GMT

Show the output as it is now and then show the desired output. I do not understand "the problem" and how "he considers the day" because that part is not in the search. Show us the entire search.

Re: Average on time only, without considering date

somesoni2 — Fri, 23 Jun 2017 14:37:42 GMT

Try this

Base quey
 | eval EndTime = if(Result="OK", _time-relative_time(_time,"@d"), null)
 | eval StartTime = if(LogType ="START", _time-relative_time(_time,"@d"), null)
 | stats Latest(StartTime) as StartTime Latest(EndTime) as EndTime by PackageName ExecutionInstanceGUID
 | eval Duration = (EndTime-StartTime)
 | where (Duration != "" OR Duration >= 0)
 | stats avg(EndTime) as AVGEndTime avg(Duration) as AVGDuration avg(StartTime) as AVGStartTime by PackageName
 | fieldformat AVGStartTime = strftime(AVGStartTime,"%H:%M:%S") 
 | fieldformat AVGEndTime = strftime(AVGEndTime,"%H:%M:%S")