SPL With No Joins

This blog post was written at an auspicious time, as the Ethereum ecosystem was preparing for the auction of virtual land plots. According to what I had read, some of those plots had little characters stylized on them, making them more valuable. The whole thing looked adorable.

Obviously, we had to Splunk it. We didn’t stop there. We splunked everything on Ethereum. We ingested blocks, transactions, and started decoding events on the chain.

With an insatiable thirst for more knowledge, we set out to map the asset price of the elements on chain. Using the data posted by Chainlink oracles, we could pinpoint the value of Ethereum for each block.

We were paying attention more particularly to NFT sales, and we found we had a problem mapping the identity of the asset to how much it was sold for. That is because an NFT sale would trigger as part of the same transaction two different events: the OrderMatched event, which sealed the sale and mentioned currency amounts, and the Transfer event, which transferred the electronic token from one account to another.  These are two separate events as the exchange happens first. To avoid reentrancy attacks, the order is first created and matched before the transfer happens.

If we want to match both events, our first move was to conjure two searches, one for each event, and join them on a transaction hash:

index=mainnet address=0x960b7a6bcd451c9968473f7bbfd9be826efd549a sourcetype="ethereum:transaction:event"  "event.name"=Transfer
|  join left=e1 right=e2 where e1.transactionHash=e2.transactionHash
 [search index="mainnet" sourcetype="ethereum:transaction:event" "event.name"=OrdersMatched]

This search turned out to be pretty expensive. Note how we can use the contract address of the token in the first search (0x960b7a6bcd451c9968473f7bbfd9be826efd549a) but not in the second search, as OrdersMatched does not mention the contract address of the item being exchanged.

We have an old hand on our team. He told us to never use joins, or risk being eaten by the tiger we keep on the 4th floor of the Santana Row office. We had no choice!

First, we merged the two searches into one, returning all entries matching both queries:

index=mainnet sourcetype="ethereum:transaction:event" ((address=0x960b7a6bcd451c9968473f7bbfd9be826efd549a Transfer) OR OrdersMatched)

Now, we go into a bit more depth on each of the records. Some of the events that were returned are transfers, others are ordersMatched. We read and create new fields for the data we want to search. First, we analyze the contents of the events:

index=mainnet sourcetype="ethereum:transaction:event" ((address=0x960b7a6bcd451c9968473f7bbfd9be826efd549a Transfer) OR OrdersMatched)
| spath "event.params{}.value"
| spath "event.name"
| rename "event.name" AS eventName
| rename "event.params{}.value" AS eventValues

Then we moved out the transfer values to new fields:

| eval transfer_from=if(eventName="Transfer",mvindex(eventValues,0),null())
| eval transfer_to=if(eventName="Transfer",mvindex(eventValues,1),null())
| eval transfer_index=if(eventName="Transfer",mvindex(eventValues,2),null())

We move out the orders matched values to new fields:

| eval ordersMatched_buyhash=if(eventName="OrdersMatched",mvindex(eventValues,0),null())
| eval ordersMatched_sellHash=if(eventName="OrdersMatched",mvindex(eventValues,1),null())
| eval ordersMatched_maker=if(eventName="OrdersMatched",mvindex(eventValues,2),null())
| eval ordersMatched_taker=if(eventName="OrdersMatched",mvindex(eventValues,3),null())
| eval ordersMatched_price=if(eventName="OrdersMatched",mvindex(eventValues,4),null())
| eval ordersMatched_metadata=if(eventName="OrdersMatched",mvindex(eventValues,5),null())

Now, rather than join, we use the stats command to return the first values of all the new fields by unique transaction hash:

| stats first(ordersMatched*) first(transfer_*) by transactionHash

With those changes, we are able to produce blazing-fast results:

We are making an important assumption - there is only one transfer and order matched event per transaction. That said, right now, this assumption is valid, from checking the existing code emitting those events.

Thanks for reading! Now you know you can avoid plush tiger bites by avoiding joins. There is a presentation with even more tips available here you should read. If you would like to start playing with blockchain data, get in touch with the blockchain team at blockchain@splunk.com.