In a world powered by data, monitoring system performance is key for any application’s smooth operation. Prometheus, an open-source systems monitoring and alerting toolkit, is commonly used for that purpose. It allows us to fetch, store, and operate on multi-dimensional data like system metrics.

There are many systems that hook up to a db and you can query them directly but there are instances where you need to use other options. In this post, we’ll go through the process of creating custom metrics for Prometheus using a Bash script and a MySQL database query, all within a practical example - tracking coffee servings by cafe!

Prerequisites

Before we begin, make sure that you have the following:

  • Prometheus and Node Exporter installed on your server.
  • A MySQL database with relevant data, which you can query.

Create a mysql authentication file

Create a authentication file the bash script will use and put it somewhere safe on the server, eg /root/client_auth.cnf

[client]
user = my_coffee_adventures
password = replace-me
host = my-coffee-shop-01
database = coffee-world

sudo chmod 600 /root/client_auth.cnf

Creating the Bash Script

Here’s our Bash script. It selects data from a MySQL database and transforms it into a format that Prometheus can consume.

#!/bin/bash
set -euo pipefail

OUTPUT_FILE="/var/lib/node_exporter/textfile_collector/coffee_servings_by_cafe.prom"
TEMP_OUTPUT_FILE=$(mktemp "${OUTPUT_FILE}.XXXXXX")
trap 'rm -f "$TEMP_OUTPUT_FILE"' EXIT

# Prepare the database query
CAFE_SERVINGS_QUERY="
SELECT cafeId, cafeName, COUNT(*) AS CoffeeServings
FROM torders o JOIN tcafes c on o.cafeId = c.cafe_id
WHERE ordertime > date_sub(current_timestamp(),interval 1 minute)
GROUP BY cafeId, cafeName;
"
# Convert multi-line query into single line
CAFE_SERVINGS_QUERY=$(echo "$CAFE_SERVINGS_QUERY" | tr '\n' ' ')

# Query the database
CAFE_SERVINGS_COUNT=$(mysql --defaults-extra-file=/root/client_auth.cnf -N -s -e "$CAFE_SERVINGS_QUERY")

# Add HELP and TYPE comments to the temporary output file
echo "# HELP coffee_servings_by_cafe Current coffee servings by cafe." >> "$TEMP_OUTPUT_FILE"
echo "# TYPE coffee_servings_by_cafe gauge" >> "$TEMP_OUTPUT_FILE"

escape_label() {
    local value=$1
    value=${value//\\/\\\\}
    value=${value//\"/\\\"}
    value=${value//$'\n'/\\n}
    printf '%s' "$value"
}

# Loop through the coffee servings count and create the result
while IFS=$'\t' read -r CAFE_ID CAFE_NAME COFFEE_SERVINGS; do
    [[ $COFFEE_SERVINGS =~ ^[0-9]+$ ]] || {
      echo "Unexpected metric value: $COFFEE_SERVINGS" >&2
      exit 1
    }
    # Write the metrics to the temporary file
    CAFE_ID=$(escape_label "$CAFE_ID")
    CAFE_NAME=$(escape_label "$CAFE_NAME")
    printf 'coffee_servings_by_cafe{cafe_id="%s",cafe_name="%s"} %s\n' \
      "$CAFE_ID" "$CAFE_NAME" "$COFFEE_SERVINGS" >> "$TEMP_OUTPUT_FILE"
done <<< "$CAFE_SERVINGS_COUNT"

chmod 644 "$TEMP_OUTPUT_FILE"
mv -f "$TEMP_OUTPUT_FILE" "$OUTPUT_FILE"
trap - EXIT

The Output

  • /var/lib/node_exporter/textfile_collector/coffee_servings_by_cafe.prom should now have this prometheus metrics in it.
# HELP coffee_servings_by_cafe Current coffee servings by cafe.
# TYPE coffee_servings_by_cafe gauge
coffee_servings_by_cafe{cafe_id="101",cafe_name="Latte Lounge"} 345
coffee_servings_by_cafe{cafe_id="3015",cafe_name="Espresso Emporium"} 257

Create a cronjob

* * * * * /opt/scripts/update-my-coffee-metrics.sh

Node Exporter reads textfile metrics on every scrape, so no restart is needed once the collector directory is enabled. The temporary file is in the same directory so the final rename is atomic. Prometheus reserves _total for counters, so this current-value gauge is now coffee_servings_by_cafe. Prometheus naming and Node Exporter textfile collector

If this grows to several queries or label types, I would switch only the exporter to Python and the official prometheus_client. For one query Bash remains simplest.

So here we have gone through a plausble scenario of how to create custom metrics.


Buy Me a Coffee