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 🙂 ... View more

I have 2 different search queries and I want to calculate sum of differences between time of event 1 and event 2 (in hours) for a common field (customID) Query 1: index=xacin sourcetype="xaxd" "*Completed setting deactivation timer for*" OR "grace period" | rex "[cC]ustom:(?<customID>\w+)"| dedup customID| eval ltime=_time customID ltime wj 1678118565.572 bi8m 1678089668.915 nri 1678060951.505 Query 2: index=xacin sourcetype="xaxd" "*StatusHandler - Completed moving *" | rex "custom:(?<customID>\w+)"| dedup customID |eval rtime=_time customID rtime bi8m 1678118477.707 a2su 1678118456.775 ceo 1678118425.484 nri 1678089748.844 Since bi8m and nri are common customID, I need to output : (1678118477.707-1678089668.915) + (1678089748.844 -1678060951.505) = 57606.131  I tried to come up with the following query but clearly it's not working: index=xacin sourcetype="xaxd" "*Completed setting deactivation timer for*" OR "grace period" | rex "[cC]ustom:(?<customID>\w+) "| dedup customID| eval ltime=_time | append [search index=xacin sourcetype="xaxd" "*StatusHandler - Completed moving *" | rex "custom:(?<customID>\w+)"| dedup customID| eval rtime=_time | stats count by customID | where count > 1 | eval time_diff=(rtime-ltime)| stats sum(time_diff) ... View more