Hi, I have a background with T-SQL and reading the forums I start to realize that "join" is not so good to use with Splunk. I have found similar forum posts addressing my questions, but still don't seem to get it, perhaps it's just a learning thing. But I'll share my case and see if anyone can point me in the right direction, preferably explaining it like you're talking to a three year old 😄 So. I want to output data about an "Order" in a Table in a Dashboard. I have my initial search that grabs an order by Properties.OrderReference. In an order I have transactions. A transaction has a Properties.TransactionReference. Transactions in an order will have status updates as the order is processed in our system. The Properties.OrderStatus contains an enum, like "InProgesss", "Error", "Complete" and so on. My goal is to show in a table, the transactions in an order and the _latest_ OrderStatus. I am not interested in the previous statuses for a transaciton, just the latest one based on _time. I have played around a bit and this is giving me what I want (sorry for any n00b stuff in here): index="my_index"
| spath input=Properties
| where RenderedMessage="Created a new transaction"
AND 'Properties.OrderReference'="289e272f-2677-409b-9576-f28b2763c658"
AND 'Properties.EnvironmentName'="Development"
| join Properties.TransactionRef AND Properties.OrderReference
[search index="my_index"
| where MessageTemplate="Publishing transaction status"]
| eval Time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| rename Properties.TransactionReference as Reference,
Properties.Amount as Amount,
Properties.Currency as Currency,
Properties.TransactionType as Type,
Properties.TransactionStatus as Status
| table Time, Reference, Type, Amount, Currency, Status However this is pretty slow, and it uses join that I am starting to realize is not a good option. I have also played around, for the second "enriching" search, to use something like: | sort - _time | head 1 in order to just grab the latest occurence. But no luck switching to "stats" or similar. Any help would be appreciated, please let me know if more background info is needed. Edit: Here are events from the two different searches. First one, showing transactions in the order: {"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"}} Second one, showing status updates for transactions in the order: {"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"}} KR Daniel
... View more