Calculate total time for a specific event over a range of time period?

[akidua]

I have 2 queries:
One is an OFF event, and one is an ON event for a cluster of machines for customers. I want to calculate approximate total hours of OFF event within a time range. Also, the assumption is if the ON event is seen for the first time within the time picker range that implies the machine was already OFF before this.
So, for example:
For 20-day period over month of November

OFF events:

CustomerID	Time/Date
ABC	11/2/2022
GHI	11/3/2022
GHI	11/9/2022
MNO	11/10/2022
PQR	11/14/2022
JKL	11/16/2022

ON events:

CustomerID	Time/Date
DEF	11/5/2022
GHI	11/7/2022
PQR	11/7/2022
JKL	11/12/2022
MNO	11/15/2022
JKL	11/18/2022

So, if today date is November 20 and time picker range is set for last 20 days (making time range from 11/1/2022 to 11/20/2022):
OFF time for ABC is 11/20 – 11/2 = 18 days

OFF time for DEF is 11/5- 11/1 = 4 days since the machines is assumed to be turned off before the ON event

OFF time for GHI is (11/7-11/3) + (11/20-11/9) = 4 + 11 + 15 days

OFF time for JKL is (11/12-11/1) + (11/18-11/16) = 11+2 = 13 days

OFF time for MNO is (15-10) = 5 days

OFF time for PQR is (11/7-11/1) +(11/20-11/14) = 6+6 =12 days

So total off time(approximate) for 6 customers over a range of 20 days is 18+4+15+13+5+12=67 days

The query that I came up with is just customers data sorted over customer and decreasing time:

index=xaci sourcetype="xaxd" "*Powered off operation for*" OR "Powered On operation for" | rex "[cC]ustomer:(?<customerID>\w"|  sort customerID, - _time

CustomerID	_time
ABC	11/2/2022
DEF	11/5/2022
GHI	11/9/2022
GHI	11/7/2022
GHI	11/3/2022
JKL	11/18/2022
JKL	11/16/2022
JKL	11/12/2022
MNO	11/15/2022
MNO	11/10/2022
PQR	11/14/2022
PQR	11/7/2022

Any help is highly appreciated since I'm new to Splunk 🙂

[yuanliu]

Here is the most literal interpretation of your requirement:

| transaction CustomerID startswith=State="ON" endswith=State="OFF" keepevicted=1 keeporphans=1
| addinfo
| eval offDays = round(if(closed_txn == 1, duration, if(State == "OFF", info_max_time - _time, _time - info_min_time)) / 86400)
| stats sum(offDays) as offDays by CustomerID

This assumes that "Time/Date" in your data illustration is _time field.  The sample data gives

CustomerID	info_min_time	info_max_time	offDays
ABC	2022-11-01	2022-11-20	18
DEF	2022-11-01	2022-11-20	4
GHI	2022-11-01	2022-11-20	15
JKL	2022-11-01	2022-11-20	13
MNO	2022-11-01	2022-11-20	5
PQR	2022-11-01	2022-11-20	12
			67

Note the above code will not list info_min_time and info_max_time as shown.  Those columns are added to help visualization.  The expanded code is

| transaction CustomerID startswith=State="ON" endswith=State="OFF" keepevicted=1 keeporphans=1
| addinfo
| fieldformat info_min_time = strftime(info_min_time, "%F")
| fieldformat info_max_time = strftime(info_max_time, "%F")
| eval offDays = round(if(closed_txn == 1, duration, if(State == "OFF", info_max_time - _time, _time - info_min_time)) / 86400)
| stats sum(offDays) as offDays by CustomerID info_min_time info_max_time

[akidua]

Thank you so much @yuanliu 
I have a question though:
For the customer JKL, how is "duration" going to give the correct result. Isn't duration going to give time difference between latest and first event? So with that logic it will give 11/12/2022 -11/16/2022
instead of 13 days?

[akidua]

Apologies..i understood the logic..duration will be computed for ON event followed by an OFF event.
Thanks @yuanliu  for your help 🙂

[yuanliu · Accepted solution]

I think I made a mistake.  It should be from OFF to ON as one transaction.  The rest should be picked up from boundaries

| transaction CustomerID startswith=State="OFF" endswith=State="ON" keepevicted=1 keeporphans=1
| addinfo
| fieldformat info_min_time = strftime(info_min_time, "%F")
| fieldformat info_max_time = strftime(info_max_time, "%F")
| eval offDays = round(if(closed_txn == 1, duration, if(State == "OFF", info_max_time - _time, _time - info_min_time)) / 86400)
| stats sum(offDays) as offDays by CustomerID

[akidua]

Yeah I already modified it to startswith=state="OFF" to get the correct results
Thanks for your help @yuanliu 🙂