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 showing a decoded CQL query message

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.

Wireshark showing a mixture of QUERY, PREPARE and EXECUTE messages

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

Wireshark filter builder showing available protocols

  1. Scroll down and open the CQL protocol
  2. Select cql.consistency
  3. Choose ONE from the list of pre-defined values

Wireshark CQL protocol filter options with cql.consistency selected

  1. Click OK and hit the blue arrow

Wireshark filtered to show only messages with consistency ONE

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"

Wireshark filtered by table name showing the CREATE TABLE and prepared statements

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.