How to find the time difference between events to calculate the average duration?

[dbcase]

Hi,

I have a data set that looks like this:

I need to calculate the avg duration of the power loss (event where EVENT_SUB_TYPE=ACpwrLoss is the beginning and event where EVENT_SUB_TYPE=ACpwrLossRes is the end). The time that the transaction begins/ends is in the CPE_CONVERTED field and this needs to be by PREMISE.

I've tried several things, but none have worked out yet. This one is the most promising but still no luck

| transaction PREMISE EVENT_SUB_TYPE startswith="ACPwrLoss" endswith="ACPwrLossRes" | timechart avg(duration) by PREMISE

Any thoughts?

[sjohnson_splunk]

Why not use stats instead of timechart? I'm not sure what default span is on timechart but it might be messing up your results.

[jkat54]

This works for the very last outage of each premise, but doesnt show all outages if there were multiples:

 index=top10 | eval powerOff=if(EVENT_SUB_TYPE="ACPwrLoss",_time,null()) | eval powerRestored=if(EVENT_SUB_TYPE="ACPwrLossRes",_time,null()) | stats last(powerRestored) AS lastPowerRestored last(powerOff) AS lastPowerOff by PREMISE | eval outageDuration=lastPowerRestored-lastPowerOff | table PREMISE lastPowerOff lastPowerRestored outageDuration

This works for all outages of each premise, but can get wonky if you have multiple ACPwrLoss'es for one premise before having a ACPwrLossRes... can also get weird if you have the loss but no restoration.

 index=top10 | sort PREMISE | eval powerLost=if(EVENT_SUB_TYPE="ACPwrLoss",_time,null()) | eval powerRestored=if(EVENT_SUB_TYPE="ACPwrLossRes",_time,null()) | filldown powerRestored | table PREMISE powerLost powerRestored | where isnotnull(powerLost) | eval duration=powerRestored-powerLost | stats avg(duration) as duration by PREMISE

duration is in seconds in above.

Here's the same as above but evals for minutes, max, mins, avg, and counts:

 index=top10 | sort PREMISE | eval powerLost=if(EVENT_SUB_TYPE="ACPwrLoss",_time,null()) | eval powerRestored=if(EVENT_SUB_TYPE="ACPwrLossRes",_time,null()) | filldown powerRestored| table PREMISE powerLost powerRestored | where isnotnull(powerLost) | eval duration=powerRestored-powerLost | stats count(duration) as total_outage_count max(eval(duration/60)) as maximum_duration_minutes min(eval(duration/60)) as minumum_duration_minute avg(eval(duration/60)) as average_duration_minutes by PREMISE

Sorry for so many answers, but I hope one of them fits the bill.

Cheers,
JKat54

[sk314]

Did you try | transaction PREMISE startswith="ACPwrLoss" endswith="ACPwrLossRes" | timechart avg(duration) by PREMISE

[michael_bates_1]

Hi,

Try removing the field "EVENT_SUB_TYPE" from the field list.

The transaction command uses the field list to group events together and in your example, the contents of this field are actually different so you are getting 2 transactions with only a single event in them.