Calculating CPU Headroom

Introduction: From Status Indicators to Transformed Time-Series

In the previous lesson, you learned how to use aggregate functions like AVG() to display a single summary value in a Stat Panel. That was all about creating status indicators — panels that answer "What's the overall situation right now?" at a glance. Now we're going to explore a different visualization need: showing transformed metrics over time . Instead of aggregating many rows into one number, you'll transform each data point into something more meaningful before Grafana plots it. Think of it this way: aggregate functions help you create status panels (one big number), while calculations help you create insightful time-series graphs (lines that tell a story).

The Problem: Raw Metrics vs. Operational Insights

Here's the visualization problem we're solving. Imagine you have a time-series panel showing "CPU Usage" hovering around 75%. That's useful information, but when stakeholders look at your graphs during a planning meeting, they're asking: "Can we handle more load?" or "Do we have room to deploy this new feature?" A graph showing "CPU Headroom" at 25% answers that question directly. Same underlying data, but visualized in a way that matches how people think about operational capacity. This concept of CPU headroom — the percentage of CPU capacity still available — is what we call a derived metric. It doesn't exist in your database tables, but you can calculate it in your query so Grafana displays it as if it were a real column. By the end of this lesson, you'll know how to create time-series panels that show derived metrics and how to name them properly so your panel legends are clear and professional.

Creating Derived Metrics for Time-Series Panels

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Designing Clear Panel Legends

One of the most important aspects of visualization design is making sure anyone can understand what they're looking at without hunting through documentation or asking questions. This is where custom metric naming becomes critical. When someone opens your panel at 3 a.m. to investigate an alert, your legend needs to be instantly clear.

Here's the problem: when you write a calculated column like 100 - usage without specifying a name, PostgreSQL will name it something like ?column? or just show the expression 100 - usage. Imagine opening a time-series panel and seeing a legend that says:

?column?

That's not helpful. Now imagine instead the legend shows:

CPU Headroom %

That's immediately understandable. The metric name tells you exactly what the graph is showing. This is what professional Grafana panels look like — every visualization has clear, descriptive labels that make it self-documenting.

In your query, you control the legend text using string literals in your SELECT statement. The pattern is 'Your Custom Name' AS metric. The single quotes indicate you're providing literal text, not referencing a database column. When you write 'CPU Headroom %' AS metric, Grafana uses that exact text in your panel's legend, tooltips, and anywhere else it needs to identify this series.

This becomes even more important when you're building multiple panels to monitor different aspects of your infrastructure. If one panel shows "CPU Headroom %" and another shows "Memory Headroom %", anyone viewing your graphs immediately understands that both panels are showing capacity metrics, just for different resources. The consistent naming pattern creates cohesive visualizations.

Think about usability from your users' perspective. Operations teams looking at your panels don't want to decipher technical column names or remember what various calculated expressions mean. They want to see "Network Throughput Mbps" or "Disk Space Available GB" — metric names that directly communicate what's being measured. That's what custom naming accomplishes.

Filtering Data for Focused Visualizations

Grafana panels often need to show data for specific resources or segments. Maybe you want one panel showing CPU metrics for production servers and another panel showing the same metrics for development servers. Or perhaps you're creating a detailed view focused on a single high-priority host. This is where query filtering becomes essential for visualization design.

You're already familiar with Grafana's time range picker. When you select "Last 6 hours" or "Last 24 hours" in Explore, that selection determines what time range you're querying. That's what WHERE $__timeFilter(ts) does — it ensures your query respects the selected time range without you having to hardcode specific dates and times.

But now you need to add another dimension of filtering. Let's say you want a panel specifically showing CPU headroom for 'host-a'. You add this filtering condition to your query using AND host = 'host-a'. The complete filter becomes:

SQL

WHERE $__timeFilter(ts)
  AND host = 'host-a'

This compound filtering gives you precise control over what data Grafana visualizes in each panel. The time filter ensures your panel shows data from your selected time range, while the host filter ensures your panel only shows the specific resource you're monitoring. Both conditions must be satisfied for data to appear in your visualization.

Here's the complete query that creates a focused time-series panel showing CPU headroom for a specific host:

SQL

SELECT
  $__time(ts),
  'CPU Headroom %' AS metric,
  100 - usage AS value
FROM public.metrics_cpu
WHERE $__timeFilter(ts)
  AND host = 'host-a'
ORDER BY 1;

When you create a panel with this query, Grafana will draw a single line on the graph representing CPU headroom for host-a over your selected time range. If host-a had 30% headroom at 10:00 AM and 55% headroom at 11:00 AM, you'll see that exact trend in your visualization. The filtering ensures you're seeing precisely the data you need, while the calculation ensures you're seeing it in the most meaningful form.

This filtering approach is fundamental to building multiple related panels. In a dashboard, you can create panels side-by-side showing the same metric for different hosts. To add a new panel in dashboard mode, click the "Add" button in the top toolbar and select "Visualization". Each panel gets its own query, so you create the first panel with host = 'host-a', add a second panel with host = 'host-b', and a third panel with host = 'host-c'. Grafana lets you resize and position these panels side-by-side, creating a focused view that makes it easy to compare CPU headroom across your infrastructure at a glance.

Alternatively, you might create a single panel showing all three hosts by removing the host filter entirely and changing to host AS metric to differentiate the lines. The query structure gives you complete flexibility in how you organize your visualizations — whether you want separate focused panels or combined multi-series panels depends on your monitoring needs and how your team prefers to consume the information.