Im storing Device Status data into Elastic to generate reports out of it. Following are list of fields and sample data
DeviceId DeviceType Status Updated_At
10001 50001 ON 2017-12-12 17:04:26 10002 50002 ON 2017-12-12 17:04:34 10001 50001 OFF 2017-12-12 17:08:21 10002 50002 ON 2017-12-12 17:09:27 10001 50001 ON 2017-12-12 17:09:29 10002 50002 OFF 2017-12-12 17:09:43 10001 50001 OFF 2017-12-12 17:09:55 10002 50002 ON 2017-12-12 17:09:58
I want to calculate utilization of each device (Group by DeviceId). The way I want to calcualte utilization is calculate number of minutes between ON and OFF of a device. At times I will have consicutive ONs or OFFs as well. Irrespective of that., I want to take ON and next OFF (there coule be multiple ONs before next OFF) then find time difference between them to know how long an utility turned ON. I need to apply Group By on DeviceType to find utilization of group of Devices.
- From Mapping perspective., I kept DeviceId and DeviceType as Long, Status as String - Any other better options? Like Keyword as I'll do exact search
- Possible to write a query in ES to achieve what I had mentioned above or I should write the logic in my application?