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 = 123-my-super-secret-pwd-123!
host = my-coffee-shop-01
database = coffee-world

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 -e # exit immediately if a command fails

# Create a temporary output file
TEMP_OUTPUT_FILE=$(mktemp)

# 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_total The current number of coffee servings by a specific cafe." >> "$TEMP_OUTPUT_FILE"
echo "# TYPE coffee_servings_by_cafe_total gauge" >> "$TEMP_OUTPUT_FILE"

# Loop through the coffee servings count and create the result
while IFS=$'\t' read -r CAFE_ID CAFE_NAME COFFEE_SERVINGS; do
    # Write the metrics to the temporary file
    echo "coffee_servings_by_cafe_total{cafeId=\"$CAFE_ID\", cafeName=\"$CAFE_NAME\"} $COFFEE_SERVINGS" >> "$TEMP_OUTPUT_FILE"
done <<< "$CAFE_SERVINGS_COUNT"

# Replace the OUTPUT_FILE with the temporary output file
OUTPUT_FILE="/var/lib/node_exporter/textfile_collector/coffee_servings_by_cafe.prom"
mv "$TEMP_OUTPUT_FILE" "$OUTPUT_FILE"
chmod 644 "$OUTPUT_FILE"

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_total The current number of coffee servings by a specific cafe.
# TYPE coffee_servings_by_cafe_total gauge
coffee_servings_by_cafe_total{cafeId="101", cafeName="Latte Lounge"} 345
coffee_servings_by_cafe_total{cafeId="3015", cafeName="Espresso Emporium"} 257
coffee_servings_by_cafe_total{cafeId="2148", cafeName="Cappuccino Corner"} 198

Create a cronjob

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

After this step, just restart your node_exporter and it should pick it up, if you have textfile_collector enabled

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


Buy Me a Coffee