I have the following business use case: a workflow (WorkflowId column) has multiple running instances (InstanceId column), every running instance has multiple Steps (Step column) and every Step has multiple Events (Event column). I’m using Loki (Grafana) to store the following time series:
Created (Date), WorkflowId (uuid), InstanceId (uuid), Step (text), Event (text)
2021-11-04 16:40 fafcad98-f245-4658-ba8b-526c60e3eba6 fe8a9704-eeb7-4b75-b29f-ff638e991964 UploadDocuments DocumentsUploaded
2021-11-04 19:40 fafcad98-f245-4658-ba8b-526c60e3eba6 4fcbfcdb-1c22-4940-886b-c236a624ac2c VerifyDocumentsBackOffice Approve
2021-11-04 23:40 fafcad98-f245-4658-ba8b-526c60e3eba6 a7b245f7-98b1-4166-97e0-f3cba9cba87e Offer OfferRejected
2021-11-05 01:40 fafcad98-f245-4658-ba8b-526c60e3eba6 cb024966-ddab-4031-867f-772e221c0059 Offer OfferRejected
2021-11-05 01:40 fafcad98-f245-4658-ba8b-526c60e3eba6 a56b82fa-3ddb-431e-bd7d-3a5df101f269 VerifyDocumentsBackOffice Rejected
2021-11-04 21:40 fafcad98-f245-4658-ba8b-526c60e3eba6 05b2798d-a6f2-4b82-b34b-ba469637b026 VerifyDocumentsBackOffice Rejected
2021-11-04 19:40 fafcad98-f245-4658-ba8b-526c60e3eba6 fe8a9704-eeb7-4b75-b29f-ff638e991964 VerifyDocumentsBackOffice Rejected
I want to compute the average duration of Step(UploadDocuments) for every WorkflowId which means to compute first the step duration (UploadDocuments) for every InstanceId
{ First_Occurrence_Of_Step (VerifyDocumentsBackOffice) - First_Occurrence_Of_Step(UploadDocuments) }
and then to make an average of this duration for every WorkflowId.
I tried to start by computing the First_Occurrence_Of_Step(UploadDocuments) by InstanceId
first_over_time({JourneyId="fafcad98-f245-4658-ba8b-526c60e3eba6", Step="UploadDocuments"} | unwrap Created [1d]) by (InstanceId)
but no result back…
Which is the right LogQL query to compute the average of Step(UploadDocuments) for every WorkflowId?
Thanks,