Would something like this work for you? | table _time, OrderReference, TransactionReference, Type, Amount, Currency, OrderStatus
| stats latest(*) as * by OrderReference TransactionReference Full r...
See more...
Would something like this work for you? | table _time, OrderReference, TransactionReference, Type, Amount, Currency, OrderStatus
| stats latest(*) as * by OrderReference TransactionReference Full runanywhere example based on your events | makeresults format=json data="[{\"Level\":\"Information\",\"MessageTemplate\":\"Created a new transaction\",\"RenderedMessage\":\"Created a new transaction\",\"Properties\":{\"SourceContext\":\"ApiGateway.Controllers.OrdersController\",\"TransactionReference\":\"e4dfbba0-90cf-4e1d-9ca3-e661ace5fe1d\",\"TransactionType\":\"Transfer\",\"Amount\":901,\"Currency\":\"SEK\",\"ExecutionDate\":\"2023-11-15T14:32:00.0000000+02:00\",\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"ActionId\":\"9a240462-d4c7-485e-a974-8229f2520c6c\",\"ActionName\":\"ApiGateway.Controllers.OrdersController.PostOrder (ApiGateway)\",\"RequestId\":\"0HN34CGT9KPCS:00000004\",\"RequestPath\":\"/orders\",\"ConnectionId\":\"0HN34CGT9KPCS\",\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Created a new transaction\",\"RenderedMessage\":\"Created a new transaction\",\"Properties\":{\"SourceContext\":\"ApiGateway.Controllers.OrdersController\",\"TransactionReference\":\"7ced831c-f8fd-41a2-88b1-6b564259539b\",\"TransactionType\":\"Transfer\",\"Amount\":567,\"Currency\":\"SEK\",\"ExecutionDate\":\"2023-11-15T14:32:00.0000000+02:00\",\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"ActionId\":\"9a240462-d4c7-485e-a974-8229f2520c6c\",\"ActionName\":\"ApiGateway.Controllers.OrdersController.PostOrder (ApiGateway)\",\"RequestId\":\"0HN34CGT9KPCS:00000004\",\"RequestPath\":\"/orders\",\"ConnectionId\":\"0HN34CGT9KPCS\",\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Created a new transaction\",\"RenderedMessage\":\"Created a new transaction\",\"Properties\":{\"SourceContext\":\"ApiGateway.Controllers.OrdersController\",\"TransactionReference\":\"9f7742e7-0350-420a-9f6b-79d7bd024bc5\",\"TransactionType\":\"Transfer\",\"Amount\":234,\"Currency\":\"SEK\",\"ExecutionDate\":\"2023-11-15T14:32:00.0000000+02:00\",\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"ActionId\":\"9a240462-d4c7-485e-a974-8229f2520c6c\",\"ActionName\":\"ApiGateway.Controllers.OrdersController.PostOrder (ApiGateway)\",\"RequestId\":\"0HN34CGT9KPCS:00000004\",\"RequestPath\":\"/orders\",\"ConnectionId\":\"0HN34CGT9KPCS\",\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Publishing transaction status\",\"RenderedMessage\":\"Publishing transaction status\",\"Properties\":{\"SourceContext\":\"ApiGateway.Services.StatusUpdateService\",\"Debtor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Creditor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Prefunding\":null,\"Type\":\"Transfer\",\"PaymentProcessType\":\"Internal\",\"TransactionReference\":\"9f7742e7-0350-420a-9f6b-79d7bd024bc5\",\"Suti\":\"CommonTypeLibrary.DomainModel.Suti\",\"ExecutionDate\":\"CommonTypeLibrary.DomainModel.ExecutionDate\",\"Amount\":\"SEK234.00\",\"ResponsibleLedger\":\"CommonTypeLibrary.DomainModel.Ledger\",\"RemittanceInformation\":\"None\",\"OriginalTransactionReference\":\"None\",\"SuppressedStatuses\":[],\"TransactionStatus\":\"Complete\",\"Messages\":null,\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"TransactionIdentifier\":\"9f7742e7-0350-420a-9f6b-79d7bd024bc5\",\"JobType\":\"TransactionStatusUpdateTask\",\"JobRetries\":0,\"ProcessInstanceId\":2251799813733043,\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Publishing transaction status\",\"RenderedMessage\":\"Publishing transaction status\",\"Properties\":{\"SourceContext\":\"ApiGateway.Services.StatusUpdateService\",\"Debtor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Creditor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Prefunding\":null,\"Type\":\"Transfer\",\"PaymentProcessType\":\"Internal\",\"TransactionReference\":\"e4dfbba0-90cf-4e1d-9ca3-e661ace5fe1d\",\"Suti\":\"CommonTypeLibrary.DomainModel.Suti\",\"ExecutionDate\":\"CommonTypeLibrary.DomainModel.ExecutionDate\",\"Amount\":\"SEK901.00\",\"ResponsibleLedger\":\"CommonTypeLibrary.DomainModel.Ledger\",\"RemittanceInformation\":\"None\",\"OriginalTransactionReference\":\"None\",\"SuppressedStatuses\":[],\"TransactionStatus\":\"Complete\",\"Messages\":null,\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"TransactionIdentifier\":\"e4dfbba0-90cf-4e1d-9ca3-e661ace5fe1d\",\"JobType\":\"TransactionStatusUpdateTask\",\"JobRetries\":0,\"ProcessInstanceId\":2251799813733043,\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Publishing transaction status\",\"RenderedMessage\":\"Publishing transaction status\",\"Properties\":{\"SourceContext\":\"ApiGateway.Services.StatusUpdateService\",\"Debtor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Creditor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Prefunding\":null,\"Type\":\"Transfer\",\"PaymentProcessType\":\"Internal\",\"TransactionReference\":\"7ced831c-f8fd-41a2-88b1-6b564259539b\",\"Suti\":\"CommonTypeLibrary.DomainModel.Suti\",\"ExecutionDate\":\"CommonTypeLibrary.DomainModel.ExecutionDate\",\"Amount\":\"SEK567.00\",\"ResponsibleLedger\":\"CommonTypeLibrary.DomainModel.Ledger\",\"RemittanceInformation\":\"None\",\"OriginalTransactionReference\":\"None\",\"SuppressedStatuses\":[],\"TransactionStatus\":\"Complete\",\"Messages\":null,\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"TransactionIdentifier\":\"7ced831c-f8fd-41a2-88b1-6b564259539b\",\"JobType\":\"TransactionStatusUpdateTask\",\"JobRetries\":0,\"ProcessInstanceId\":2251799813733043,\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Publishing transaction status\",\"RenderedMessage\":\"Publishing transaction status\",\"Properties\":{\"SourceContext\":\"ApiGateway.Services.StatusUpdateService\",\"Debtor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Creditor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Prefunding\":null,\"Type\":\"Transfer\",\"PaymentProcessType\":\"Internal\",\"TransactionReference\":\"9f7742e7-0350-420a-9f6b-79d7bd024bc5\",\"Suti\":\"CommonTypeLibrary.DomainModel.Suti\",\"ExecutionDate\":\"CommonTypeLibrary.DomainModel.ExecutionDate\",\"Amount\":\"SEK234.00\",\"ResponsibleLedger\":\"CommonTypeLibrary.DomainModel.Ledger\",\"RemittanceInformation\":\"None\",\"OriginalTransactionReference\":\"None\",\"SuppressedStatuses\":[],\"TransactionStatus\":\"InProgress\",\"Messages\":[],\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"TransactionIdentifier\":\"9f7742e7-0350-420a-9f6b-79d7bd024bc5\",\"JobType\":\"TransactionStatusUpdateTask\",\"JobRetries\":0,\"ProcessInstanceId\":2251799813733043,\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Publishing transaction status\",\"RenderedMessage\":\"Publishing transaction status\",\"Properties\":{\"SourceContext\":\"ApiGateway.Services.StatusUpdateService\",\"Debtor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Creditor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Prefunding\":null,\"Type\":\"Transfer\",\"PaymentProcessType\":\"Internal\",\"TransactionReference\":\"e4dfbba0-90cf-4e1d-9ca3-e661ace5fe1d\",\"Suti\":\"CommonTypeLibrary.DomainModel.Suti\",\"ExecutionDate\":\"CommonTypeLibrary.DomainModel.ExecutionDate\",\"Amount\":\"SEK901.00\",\"ResponsibleLedger\":\"CommonTypeLibrary.DomainModel.Ledger\",\"RemittanceInformation\":\"None\",\"OriginalTransactionReference\":\"None\",\"SuppressedStatuses\":[],\"TransactionStatus\":\"InProgress\",\"Messages\":[],\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"TransactionIdentifier\":\"e4dfbba0-90cf-4e1d-9ca3-e661ace5fe1d\",\"JobType\":\"TransactionStatusUpdateTask\",\"JobRetries\":0,\"ProcessInstanceId\":2251799813733043,\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Publishing transaction status\",\"RenderedMessage\":\"Publishing transaction status\",\"Properties\":{\"SourceContext\":\"ApiGateway.Services.StatusUpdateService\",\"Debtor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Creditor\":\"CommonTypeLibrary.DomainModel.AccountHolder\",\"Prefunding\":null,\"Type\":\"Transfer\",\"PaymentProcessType\":\"Internal\",\"TransactionReference\":\"7ced831c-f8fd-41a2-88b1-6b564259539b\",\"Suti\":\"CommonTypeLibrary.DomainModel.Suti\",\"ExecutionDate\":\"CommonTypeLibrary.DomainModel.ExecutionDate\",\"Amount\":\"SEK567.00\",\"ResponsibleLedger\":\"CommonTypeLibrary.DomainModel.Ledger\",\"RemittanceInformation\":\"None\",\"OriginalTransactionReference\":\"None\",\"SuppressedStatuses\":[],\"TransactionStatus\":\"InProgress\",\"Messages\":[],\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"TransactionIdentifier\":\"7ced831c-f8fd-41a2-88b1-6b564259539b\",\"JobType\":\"TransactionStatusUpdateTask\",\"JobRetries\":0,\"ProcessInstanceId\":2251799813733043,\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Publishing transaction status\",\"RenderedMessage\":\"Publishing transaction status\",\"Properties\":{\"SourceContext\":\"ApiGateway.Services.StatusUpdateService\",\"TransactionReference\":\"e4dfbba0-90cf-4e1d-9ca3-e661ace5fe1d\",\"TransactionStatus\":\"Registered\",\"OrderStatus\":\"Registered\",\"Messages\":null,\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"JobType\":\"OrderStatusUpdateTask\",\"JobRetries\":0,\"ProcessInstanceId\":2251799813733043,\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Publishing transaction status\",\"RenderedMessage\":\"Publishing transaction status\",\"Properties\":{\"SourceContext\":\"ApiGateway.Services.StatusUpdateService\",\"TransactionReference\":\"7ced831c-f8fd-41a2-88b1-6b564259539b\",\"TransactionStatus\":\"Registered\",\"OrderStatus\":\"Registered\",\"Messages\":null,\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"JobType\":\"OrderStatusUpdateTask\",\"JobRetries\":0,\"ProcessInstanceId\":2251799813733043,\"EnvironmentName\":\"Development\"}},
{\"Level\":\"Information\",\"MessageTemplate\":\"Publishing transaction status\",\"RenderedMessage\":\"Publishing transaction status\",\"Properties\":{\"SourceContext\":\"ApiGateway.Services.StatusUpdateService\",\"TransactionReference\":\"9f7742e7-0350-420a-9f6b-79d7bd024bc5\",\"TransactionStatus\":\"Registered\",\"OrderStatus\":\"Registered\",\"Messages\":null,\"OrderReference\":\"289e272f-2677-409b-9576-f28b2763c658\",\"JobType\":\"OrderStatusUpdateTask\",\"JobRetries\":0,\"ProcessInstanceId\":2251799813733043,\"EnvironmentName\":\"Development\"}}]"
| fields _raw
| spath Properties
| spath input=Properties
| table _time, OrderReference, TransactionReference, Type, Amount, Currency, OrderStatus
| stats latest(*) as * by OrderReference TransactionReference