Doing SQL like COUNT and GROUP BY on spans to get "stats"?

[ckcr4lyf]

Hi all,

For my BitTorrent tracker, I have a trace for every HTTP request, which contains a top level span with attributes such as:

• User-Agent
• Infohash
• IP

I was wondering if via TraceQL it is possible to do some query like SELECT infohash, COUNT(*) as cnt FROM table_name GROUP BY infohash ORDER BY cnt DESC; (sql pseudocode). Basically print a count of how many times a particular infohash has been "announced" (an HTTP request).

I'm able to use TraceQL get something like the following:

{span.infohash != ""} | select(span.infohash, span.ip, span.user-agent)

If I had this data now in SQL, I could trivially do some interesting queries to get stats I want. What is the recommended way to do this kinda stuff from TraceQL? I am using Grafana + Tempo.

[mapno]

Hi @ckcr4lyf. You should be able to get this information with TraceQL metrics.

Example: {span.infohash!=""} | rate() by (span.infohash) would give you a timeseries of the rate of spans that have span.infohash, aggregated by that attribute.

For the SQL query you've shared, something like {span.infohash!=""} | count_over_time() by (span.infohash) should do it. You'll need to set the step equal to the time range of the query to get a unique value for the entire time range though.

[ckcr4lyf]

Thanks for the suggestion! The rate() is pretty cool for looking at the histogram 👍

For the count though, I am kind of able to get it to work, but within Grafana there only seems to be an option to render as a graph, which definitely causes some infohashes to overlap:

as opposed to just a table format, e.g.

infohash	count
ABCD	5
GGGG	5
XDXD	4

But I guess that's a Grafana limitation rather than Tempo / TraceQL related, so I'll ask there!

[mapno]

It's a limitation of the explore view for Tempo, yes.

With a panel you'll be able to get a table view. I was able to get this with a few transformations:

and the query {} | count_over_time() by (resource.k8s.container.name).

TraceQL metrics is not purposely designed to be used like this, so you may encounter a few rough edges. It's great to see Tempo being used in more ways we initially thought of when building it :)

[ckcr4lyf]

Awesome, thanks for the idea to try and use it via a panel! I'm able to get it in table form and sort as well. Cheers, thanks a lot!

[ckcr4lyf]