Grafana Tricks

Dirty deeds, done dirt cheap

A short note upfront. I am using Grafana with PostgreSQL and despite the age difference I am very happy with both. As a consequence most of the tricks below do need an SQL database to work.

Tables with Colors for Ids

The use case, once you see it, is pretty obvious:

We are looking at the apache logs for a single IP and we want to distinguish different browsers by their user agent. The change of color makes it easier to distinuish the different user agents.

How to do it

To achieve this with grafana tables you need to modify the dashboards JSON model. We need 360 hsl colors.

{

"alias": "",

"colorMode": "value",

"colors":[

"hsl(0, 50%, 50%)",

"hsl(1, 50%, 50%)",

"hsl(2, 50%, 50%)",

"hsl(3, 50%, 50%)",

"hsl(4, 50%, 50%)",

...

To seperate these we need 359 threshold values.

"mappingType": 1,

"pattern": "ua_code",

"thresholds":[

1,

2,

3,

4,

...

Both can be easily generated from a unix command line.

$ seq 0 359 | xargs -n 1 -I X echo "hsl(X, 50%, 50%)"

$ seq 0 359 | xargs -n 1 -I X echo "X,"

There are of course other ways, but that is how I do it.

How it works

As you can see we are using hsl colors. This is because hsl colors make programmatic random coloring pretty easy. Hsl means hue, saturation, lightness with the first number being a degree on the color wheel. Luckily CSS understands them.

Variables where Display Name and Value are Different

If you have an SQL database such as PostgreSQL this is easy. You can use a query variable with

SELECT * FROM VALUES (1, 'one'), (2, 'two'), (3, 'three') t(__id, __text);

If it is not PostgreSQL, a UNION ALL will do the trick.

Including SQL Snippets

The Trick above makes most sense if you can expand the variable to SQL snippets. Including text variables in a query does however quote them. This can be avoided with ${variable:csv}.

Generated SQL is Missing

This happens in case of an SQL runtime error. In most cases this is a timeout. So the next step would be to use EXPLAIN. The cure is simple. Introduce a syntax error.

BIGINT is not working

This is because Javascript does not support them and is therefore hard to fix. See the associated Grafaba bug report.

Run Queries on Sample Data

Postgres implements the SQL 2003 syntax to run queries on say 10 percent of the data. With

FROM logs l TABLESAMPLE SYSTEM(10) REPEATABLE (4711) 

the query runs on 10% of the blocks of the logs table.