Recently I needed to track down what queries were being run against a cluster. We needed queries to be run with a consistency of LOCAL_QUORUM but it appeared that some were being run with QUORUM instead. I needed to prove this to the development team.

I recently wrote about using ngrep to discover connections to the cluster, so this post will build on that to show how to use ngrep and Wireshark to capture queries hitting a specific node.

Note: This will not work if you have client-to-node encryption turned on. However, once you see how easy it is to sniff the data entering the node, you may wish to turn this on.

Capturing Traffic with ngrep

To demonstrate, I will use The Last Pickle’s stress tool to create some traffic:

bin/tlp-stress run KeyValue -n 200 --rate 20 -r 0.2

Before running this, set up ngrep to capture the queries with the -O parameter to create a packet capture file:

sudo ngrep '' -d any -x dst port 9042 and dst host xxx.xxx.xxx.xxx -O file.pcap

Once ngrep is running, start the tlp-stress command. The file.pcap file will be generated during this time.

Warning: This file can contain sensitive data — it could include the username and password used to log into the cluster, and any data inserted during the packet capture.

Analysing with Wireshark

Now you have the packet capture file, download it to a local machine to view with Wireshark. Wireshark has hundreds of protocols built in, including CQL — it’s just a case of loading the capture file and Wireshark will decode the queries.

Wireshark is split into three screens. The top shows each network message in summary form; selecting one gives details in the next two screens. The bottom screen gives a hex dump, but the middle screen is the important one, where the message is decoded using the CQL protocol.

You can see whether a message is a QUERY, PREPARE, or EXECUTE, and crucially what consistency level was used.

Filtering by Consistency

The real power comes with filtering. To search for all queries with a consistency of ONE:

  1. Click the Expression text to the right of the “Apply a display filter” box
  2. Scroll down and open the CQL protocol
  3. Select cql.consistency
  4. Choose ONE from the list of pre-defined values
  5. Click OK and hit the blue arrow

You’ll be presented with all the network messages using ONE consistency — making it straightforward to prove to your development team exactly which queries are using the wrong consistency level.

Filtering by Table Name

You can also filter by table name using:

cql.string contains "keyvalue"

This will show all query strings containing the table name, including the CREATE TABLE statement and any prepared statements.

There are numerous different ways to filter the messages to understand how the cluster is being used. This tool is a useful way to understand what might be happening to a cluster if you don’t have full access or traceability within the applications.

Hopefully this post will also give you pause to think about encryption if you have not yet got this enabled on your cluster.