topic Re: Alternative to join, correlating TOP 1 matches from second search? in Splunk Search

Alternative to join, correlating TOP 1 matches from second search?

dannepannesthlm — Mon, 29 Apr 2024 12:07:57 GMT

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

Re: Alternative to join, correlating TOP 1 matches from second search?

ITWhisperer — Mon, 29 Apr 2024 10:38:25 GMT

From other forum post, you have probably seem that volunteers usually work better with sample anonymised representative events. Please can you share some events, preferable in a code block </>, so that we have something to work with (to test our solutions before posting them)?

Re: Alternative to join, correlating TOP 1 matches from second search?

dannepannesthlm — Mon, 29 Apr 2024 10:48:19 GMT

Thanks for the feedback, should I export the results of my searches as csv or some other way? Thanks

Re: Alternative to join, correlating TOP 1 matches from second search?

ITWhisperer — Mon, 29 Apr 2024 11:01:47 GMT

Paste the raw events into a codeblock e.g.

{"timestamp":"2024-04-29 11:59:59","user":"ITWhisperer","Account":1234}

Re: Alternative to join, correlating TOP 1 matches from second search?

dannepannesthlm — Mon, 29 Apr 2024 12:05:13 GMT

I've added events for the two searches I would like to use, thanks

Re: Alternative to join, correlating TOP 1 matches from second search?

PickleRick — Mon, 29 Apr 2024 12:25:38 GMT

OK. Regardless of join or not, your search is pretty bad performance-wise due to how it's initial part in the first place.

You're doing

<initial_search>
| spath [...]
| where <some_condition>

Unfortunately, it's gonna have to read and parse every single event from the given time range which is not what you want.

What is a bit tricky when approaching Splunk for the first time is that due to the fact that you're dealing with so called "schema on read" approach, Splunk - first and foremost - indexes values.

So if you have a search saying field=value Splunk first searches for all events containing the value and only those events are then checked if they do contain that value in places corresponding with the definition of field. The more conditions you have in your initial search, the more events Splunk can discard from the initial result set (due to them containing, for example, just one of two sought for terms) so that ideally the "hit ratio" is quite high and Splunk doesn't have to work too much at parsing those intermediate search results.

Your search on the other hand invokes the spath command on every single event that falls within the time range and only then it checks the results for some condition using the where command. If your events were well-formed json events, you could have the sourcetype defined with KV_MODE=json and use field=value matching based on json fields. But even if you don't have the fields parsed automatically at the point of your initial search, you can greatly improve your search performance by adding the conditions as a "full-text search".

So your

index="my_index"
| spath input=Properties
| where RenderedMessage="Created a new transaction"
        AND 'Properties.OrderReference'="289e272f-2677-409b-9576-f28b2763c658"
        AND 'Properties.EnvironmentName'="Development"

can be rewritten (yes, it looks a bit ugly but should be a lot faster) as

index="my_index" "Created a new transaction" "289e272f-2677-409b-9576-f28b2763c658"  "Development"
| spath input=Properties
| where RenderedMessage="Created a new transaction" AND 'Properties.OrderReference'="289e272f-2677-409b-9576-f28b2763c658" AND 'Properties.EnvironmentName'="Development"

Re: Alternative to join, correlating TOP 1 matches from second search?

ITWhisperer — Mon, 29 Apr 2024 12:38:07 GMT

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

Re: Alternative to join, correlating TOP 1 matches from second search?

dannepannesthlm — Mon, 29 Apr 2024 13:42:49 GMT

Thanks, this works but only gives me one transaction in the result

Re: Alternative to join, correlating TOP 1 matches from second search?

dannepannesthlm — Mon, 29 Apr 2024 13:44:24 GMT

I'm marking this as the solution since it makes my search nearly instant.
Though join might not be optimal, this change is sufficient for my needs at the moment, thanks a lot @PickleRick and also @ITWhisperer for the time and effort spent.
Much appreciated!