Querying events from unverified contracts
So you want to write a Dune query for some quick and dirty analysis, but the contract isn’t verified on Dune! *starts sobbing uncontrollably 😭😭*
But degens aren’t deterred! With a little understanding of Ethereum events and a little effort, you can be on your way to querying.
In this post we’ll walk through analyzing some data from a contract that is yet to be verified on Dune--Juicebox’s Terminal V1 contract that is being used by Constitution DAO.
I realized partway through writing this tutorial that Richard Chen already used the methodology I’m discussing to create a query for summing the amount given by each contributor to ConstitutionDAO, so to give due credit to the Gandalf of Dune wizards, I’ll be breaking down the steps to get to his result.
Ethereum events
Events (also known as logs) on Ethereum are a way of storing historical information that is significantly cheaper than contract storage. While this data can’t be accessed by contracts, they can be easily queried from an application.
They are typically used as records of pertinent information from a transaction, so they are also great for analysis.
In this example, we’ll look at Pay events from the Terminal V1 contract, which have information on who paid into the DAO and how much. I pasted an example of one of these events below from Etherscan.
Getting the events we want
Dune Analytics has a table of all the events that take place on Ethereum--ethereum.logs. But how do we get the events we want?
Events have up to 4 indexed parameters to make querying from a node easier and faster. Dune stores these in ethereum.logs as topic1, topic2...
The first topic is almost always the “event signature,” an identifier for the event.
There are two ways to get this:
- Grab it from an example event on a block explorer
- Compute it yourself using the event information
For the first method, if you have an example transaction and the contract is verified on Etherscan, you can grab the first topic from the event log. In our on-chain example, you can see that the signature is `0x3deb3bb81c337489381f7685daa204d30e6a265dac443a718949c258e3e17317`.
Say you don’t have an example transaction handy or the contract isn’t verified on Etherscan, doesn’t matter if you’re a degen! If you know the event name and parameter types, you can compute the signature yourself!
The signature is the keccak256 hash of the event name and argument types:
keccak256('eventName(arg1 type,arg2 type…)')
For the Pay event, it’s:
keccak256('Pay(uint256,uint256,address,uint256,string,address)')
Using node and `ethers`, we can run:
const ethers = require('ethers');
ethers.utils.keccak256(ethers.utils.toUtf8Bytes('Pay(uint256,uint256,address,uint256,string,address)'));
We get the same result:
Finally, we can use the event signature and contract address to fetch these events in Dune:
Using the event data
Now that we have the event data, we can start to look at some numbers.
Looking back at our sample event, let’s look at what information we have:
We want to see the contributions to ConstitutionDAO by contributor. You can see there is a projectId field on which we’ll want to filter, a beneficiary field showing who contributed (and therefore should be the beneficiary of DAO tokens), and an amount that we’ll want to sum.
ethereum.logs has 4 columns (topic1-topic4) for each of the (up to) 4 indexed fields. projectId will be in topic3 and beneficiary will be in topic4. amount is concatenated with the other non-indexed parameters in the data field.
You’ll notice that the data fields (topic* and data) are not nice decoded values. Rather, everything is a bytes data type.
For addresses, we want to the hex string anyway, so all we want to do is effectively cut out the leading zeros in the bytes with the substring function like so:
SUBSTRING(topic4 FROM 13 FOR 20) AS address_from_topic4
For projectId, we want to get a numeric value. We can use the bytea2numeric function to convert the raw bytes to a decimal number like so:
BYTEA2NUMERIC(topic3) AS project_id_from_topic3
Last but not least, we need to combine the previous two functions to get the amount parameter. Since amount is one of the non-indexed parameters, it is concatenated with the other non-indexed parameters (note and called). Since amount is the first parameter and it’s 32 bytes long, we can clean it up like so:
BYTEA2NUMERIC(SUBSTRING(data FROM 1 FOR 32)) AS amount_wei_from_data
You’ll notice that this is a large number, because it is the amount of ether specified in its smallest denomination--wei. We need to divide it by 10^18 to get the amount in ether:
BYTEA2NUMERIC(SUBSTRING(data FROM 1 FOR 32)) / 1e18 AS amount_ether_from_data
We can check all these parameters are correct by comparing our query with our sample event.
Looks great!
If we apply everything discussed herein, we end up with Richard Chen’s query for the largest contributors:
Go forth and query, my fellow degen wizards.
Appendix
One thing to watch out for when doing this is are parameters that aren’t a fixed width in bytes. For example, in the Pay event, there is a note field of type string. The string type can be an arbitrary length, so if you’re querying in the data field for a parameter after it (like caller), it takes some extra tricks. I’ll leave that to you to figure out :)