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 🙂
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
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
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?
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
Yeah I already modified it to startswith=state="OFF" to get the correct results
Thanks for your help @yuanliu 🙂