≡ Menu

Finding Rogue Cassandra Queries

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

Recently I 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.

To demonstrate how this works I will use The Last Pickle’s stress tool to create some traffic to the cluster. We only want a small amount of data, so I will use the following command

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

Before we run this we want to setup ngrep to capture the queries, see my previous post, for details on ngrep, now in this instance we want to add -O parameter to create a packet capture file, containing the incoming data into the node.

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

Once you have ngrep running, you can run the top-stress command. The file.pcap will be generated during this time.

WARNING: This file can now include sensitive data, it could contain the user and password used to log into the cluster, and it will also include any data inserted into the cluster during the packet capture.

Now we have the packet capture file, you can download it to a local machine to view it with Wireshark. If you don’t Wireshark installed, download and install it.

Wireshark has hundreds of protocols built in, including CQL, it is now just a case of loading the capture file and Wireshark will decode the queries.

Once the file is loaded, Wireshark is split into 3 screens, the top shows each of the network messages in summary form, selecting on one of those give details in the next 2 screens. The bottom screen gives a hex dump of the message, but the middle screen if the important one, where the message is decoded using the CQL protocol.

In the screenshot above we can see, this is a query message, selecting from the system.peers_v2 table, using a consistency of ONE.

We can see from the top window the messages are a mixture of QUERY, PREPARE and EXECUTE messages.

So far so good, but the real power comes with the filtering. For example we can search for all queries with a consistency of ONE. To do this we can use the filter builder, to access this, click the ‘Expression’ text, to the right of the ‘Apply a display filter’ box.

This lists all the protocols available, scroll down and open up the CQL protocol, this will list all the available filter options, select the cql.consistency. This will provide the list of pre defined values that can be used, so we can select ONE

Now, select OK, and hit the blue arrow in the filter box, and we will just be presented with all the network messages with a consistency of ONE:

You can also use a filter to search for queries against specific tables, by creating the filter cql.string contains “keyvalue” we can search for all instances of query strings containing the table name keyvalue.

By running filter we can see the statement to create the table, and also 4 prepare statements for the stress tool to run the queries against the table.

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.

{ 0 comments… add one }

Leave a Comment