Getting Data In

Average on time only, without considering date

andreafebbo
Communicator

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.

0 Karma
1 Solution

somesoni2
Revered Legend

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") 

View solution in original post

somesoni2
Revered Legend

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") 

woodcock
Esteemed Legend

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.

0 Karma
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...