Home › Topics › Capacity Planning From Monitoring History › Forecasting With a Ruler

Forecasting With a Ruler: Simple Trend Projection From Monitoring Data


Every capacity forecast you will ever need starts with a ruler. You lay it along the last year of a graph, extend the line to the right, and read off where it crosses the limit. That is the method. It is not sophisticated, it is not always right, and it is good enough for nearly every purchasing decision a network administrator makes. Anyone who tells you otherwise is usually selling the sophistication.


This guide teaches the ruler properly. You will learn why a straight line is the right default and how to smooth the noise without hiding the trend. You will learn why you must draw through peaks and not averages, and how one summer of data can lie to you. You will also learn how to do the whole thing in a spreadsheet or a twenty-line script. The guide also covers stating the result without promising precision you do not have. It explains the three situations where the ruler is wrong.


You need only a monitoring system that has been keeping history and the vocabulary from capacity planning basics: use, limit, headroom, and lead time.


In this guide

  • The ruler method, and why it is enough
  • Smoothing the noise with a moving average
  • Peaks versus averages: plan for the busy hour
  • Seasonality: why a line through one summer lies
  • The spreadsheet method, step by step
  • The same thing in twenty lines of script
  • Stating a forecast honestly
  • When the ruler is wrong
  • A forecast statement template
  • The archive that arrived on a Tuesday
  • Checklist
  • Where to go next

The ruler method, and why it is enough


A trend is the direction a number is moving once you ignore the wobble. The simplest honest description of a trend is a straight line. Put a year of monitoring history on a graph, draw the straight line that passes closest to all the points, and extend it. Where it meets the limit is your projected full date. Mathematicians call the line a linear fit; the rest of us call it the ruler.


The ruler is enough because the decisions it feeds are coarse. You are not trying to learn whether the disk fills on the fourth or the eleventh of a month. You are trying to learn whether it fills in four months or fourteen. That tells you whether to start a purchase this quarter or next. A straight line answers that question with room to spare. A curve fitted with more care answers the same question, more slowly, with more ways to be wrong.


Growth on real networks is also, most of the time, roughly linear. People add files at about the rate they added them last month. When growth is not linear, it is usually because of a single identifiable event. In that case, the fix is to know about the event, not to fit a fancier curve. (The fancier curve will fit the event beautifully and then predict it happening again every month.)


Smoothing the noise with a moving average


Raw monitoring data is jagged, and the jaggedness makes the trend hard to see by eye. A moving average is a way to smooth it. For each day, replace the value with the average of that day and the few days around it. A seven-day moving average flattens the weekly rhythm; a thirty-day moving average flattens the monthly one. The line that remains is the shape you are trying to read.


Use the moving average to look, not to calculate. It is the right thing to put on a graph when you want to see whether growth is steady, steepening, or stepping. It is the wrong thing to feed into the crossing-date arithmetic, for two reasons. It lags, because each point includes days from the past, so the smoothed line reaches the limit later than the real one. And it lowers peaks, because averaging a busy day with six quiet ones produces a moderately busy day that never happened.


In a spreadsheet the moving average is one formula. With dates in column A and daily values in column B, the seven-day average for row 8 is =AVERAGE(B2:B8). Drag it down. Put it on the same chart as the raw data and the raw data becomes readable. That is the entire technique.


Peaks versus averages: plan for the busy hour


Draw the ruler through the peaks, never through the averages. A resource fails when it is full at the moment someone needs it. That moment is the busy hour, not the average of the day. A link that averages forty percent utilisation and hits ninety-five every afternoon at three is a link with a problem at three. The average says it has years of headroom. The users at three would like a word.


The practical rule is to export one number per day, and make that number the daily maximum of whatever the monitoring system sampled. For a disk the daily maximum and the daily average are nearly the same, because disks do not empty overnight. For a link, a CPU, or a memory counter they can differ by a factor of two or more. The difference is exactly the part that hurts. Some monitoring systems store the daily maximum as part of their rollup; check yours keeps it, because if it only keeps averages the peaks are gone.


One refinement for links: the busy-hour peak on a single day can be a one-off, such as a backup that ran at noon by mistake. Bandwidth people deal with this using the 95th percentile, which discards the very highest samples before reading the peak. For general capacity work, the daily maximum with a moving average over it is usually enough.


Seasonality: why a line through one summer lies


A straight line only tells the truth when the window it was drawn through contains a fair sample of the year. Seasonality is the regular rise and fall of use with the calendar. Use is quiet in the holidays, busy at the year-end close, with a bump when the new intake arrives. Draw the ruler through a quiet season and it projects quiet forever. Draw it through a busy one and it projects a crisis that never comes. The seasonal patterns guide catalogues the rhythms; here you only need to respect them.


I learned this the way everyone does. I once forecast an imaging archive would last fourteen months, based on a beautifully straight three-month line. The three months were the summer, when half the department was away and the scanners were idle. The archive lasted five months, and the busiest week of the year was the week it filled. I now draw the line through a full year or I write "based on a partial year" in letters large enough to embarrass me later.


The rule that follows is simple. Twelve months of data or more: draw the line through all of it. Six to twelve months: draw the line, and say which seasons it is missing. Under six months: draw the line, call the result a guess, and set a date to redo it.


The spreadsheet method, step by step


Here is the whole procedure in a spreadsheet. The function names below are the common ones; if your spreadsheet spells them differently, its help page will say. Every step uses columns A and B: dates in A, daily maxima in B, one row per day, with a header row in row 1.


  1. Export a year of daily maxima for the resource from the monitoring system as a CSV file with two columns, date and value. Open it in the spreadsheet and confirm the date column is recognised as dates, not text.
  2. Sort by date ascending and delete any rows where the value is blank or zero because the collector was down. A zero is not a measurement; it is an absence.
  3. Put the limit in a cell of its own, for example D1, in the same units as column B.
  4. Compute the slope in D2 with =SLOPE(B2:B366,A2:A366). This is the growth per day, because spreadsheet dates are day numbers underneath.
  5. Compute the intercept in D3 with =INTERCEPT(B2:B366,A2:A366).
  6. Compute the crossing date in D4 with =(D1-D3)/D2 and format the cell as a date. This is the day the straight line reaches the limit.
  7. Compute the monthly growth in D5 with =D2*30.44 for the register. This is the slope per day multiplied by the average length of a month.
  8. Check the result by asking what the line predicts for today: =FORECAST(TODAY(),B2:B366,A2:A366). If that is far from the latest real reading, the trend is not straight; see "when the ruler is wrong" below.
  9. Repeat steps 4 to 6 using only the last six months of rows, and note the second crossing date. The two dates are the ends of your range.
  10. Put the raw data, the moving average, and the fitted line on one chart with the limit as a horizontal line. Look at it before you believe it.

Step nine is the honest part. The whole-year fit tells you where the long trend leads, and the six-month fit tells you whether it has recently changed. When they agree, you have a forecast. When they disagree by more than a couple of months, something changed, and the recent line is usually the one to believe.


Tip: keep the export and the workbook next to the capacity register, with the export date in the file name. A forecast without its data is an opinion, and in six months you will want to know whether the line moved or the data did.


The same thing in twenty lines of script


If you would rather not click, the same arithmetic fits in a short script. A script can run for every row of the register at once. The one below reads a CSV of date,value with a header row, and fits the straight line. It prints the monthly slope and the day the line reaches a limit you give it. It needs nothing beyond a standard Python install.


import csv, sys
from datetime import date

path, limit = sys.argv[1], float(sys.argv[2])
xs, ys = [], []
with open(path, newline="") as f:
    for row in csv.DictReader(f):
        xs.append(date.fromisoformat(row["date"]).toordinal())
        ys.append(float(row["value"]))

n = len(xs)
mx, my = sum(xs) / n, sum(ys) / n
sxx = sum((x - mx) ** 2 for x in xs)
sxy = sum((x - mx) * (y - my) for x, y in zip(xs, ys))
slope = sxy / sxx                 # units per day
intercept = my - slope * mx

print(f"trend: {slope * 30.44:+.2f} per month over {n} days")
if slope <= 0:
    print("flat or falling: no crossing date")
else:
    crossing = (limit - intercept) / slope
    print(f"reaches {limit:g} on {date.fromordinal(round(crossing))}")

Run it as python3 ruler.py fs01_d_daily_max.csv 4096 and it tells you the trend and the date. Run it again with a CSV trimmed to the last six months and you have the other end of the range. The four lines in the middle are the same least-squares fit the spreadsheet's SLOPE function performs. They are written out so you can see there is no magic in it.


Stating a forecast honestly


A forecast is a range with a most-likely point, and that is how you should say it. "The volume is full between March and June, most likely in May" is a forecast. "The volume is full on the fourteenth of May" is a number that will be wrong. When it is wrong, the person who heard it will remember the date and forget the method. Give the range first, the likely point second, and the reason for the width third.


The width of the range comes from the two fits you already did. The whole-year line and the six-month line give two crossing dates. The range runs between them, widened by a month on each side if the data is under a year old. The most likely date is the whole-year fit unless the recent fit disagrees sharply. In that case, say that too: "the long-run trend says August, but growth has been faster since the spring and the recent trend says May."


Use the same three confidence words every time, and define them once so people know what you mean:


  • High: a year or more of data, the whole-year and six-month fits agree within a month, and no known project will change the rate.
  • Medium: six to twelve months of data, or the two fits disagree by a few months, or a known project may add load but its size is estimated.
  • Low: under six months of data, or a step change in the last quarter, or a project of unknown size is coming.

Confidence words are for the reader's benefit, not yours. A low-confidence forecast that says so is still useful: it tells the reader to order early or to check again next month. A low-confidence forecast dressed as a high one is how the next review meeting gets its opening slide.


When the ruler is wrong


The ruler assumes tomorrow grows like yesterday, and three things break that assumption. Each has a signature on the graph and a fix that has nothing to do with mathematics.


Step changes

A step change is a jump: the line runs level, leaps, and runs level again at the new height. A department moved its archive onto the volume, a site was added to the link, a batch of devices was discovered. The ruler through a step gives a slope that never existed, steep through the jump and wrong on both sides. Fit only the data after the step, and say in the forecast that you did. If the step is recent and you have little data after it, the confidence word is "low" and the review date is soon.


New projects

A project that has not started yet leaves no trace in the history, which is the one place the ruler looks. The fix is to ask. Once a quarter, ask the people who start projects what is coming: new sites, new applications, new scanners, a move to keep recordings. Add each answer to the forecast as a named step with an estimated size and date. A rough estimate of a known step beats a precise projection that ignores it.


Migrations and clean-ups

A migration off a resource makes the line fall. And a clean-up makes it drop, and the ruler dutifully projects the resource emptying itself by autumn. Treat a fall like a step: fit after it, not through it. And be careful with clean-ups, because they buy headroom without changing the slope. The line resumes its old rate from the new lower point. The crossing date moves later by exactly the space you freed and not a day more. When the shape of the graph has changed for good, that is a signal to rebaseline. In that case, record the new normal so the next forecast starts from the right place.


A forecast statement template


Write every forecast in the same shape. That way, the reader can compare this month's with last month's and you cannot leave out the uncomfortable line. This is the shape; fill in the angle-bracketed parts and delete nothing.


FORECAST: <resource, named as monitoring names it>
Data:            <n> days of daily maxima, <first date> to <last date>
Method:          straight-line fit (whole window and last six months)
Current use:     <value> of <limit> (<percent>)
Trend:           <+amount per month> whole window; <+amount per month> recent
Projected full:  between <early month> and <late month>, most likely <month>
Confidence:      <high / medium / low> because <one reason>
Known steps:     <project, expected date, estimated size> or "none known"
Seasons missing: <none / list>
Review date:     <date>
Prepared by:     <name>, <date>

The "known steps" and "seasons missing" lines are the ones people are tempted to delete because they are usually empty. Leave them. An empty line that says "none known" is a statement that you asked; a missing line is a statement that you did not.


The archive that arrived on a Tuesday


Northgate Retail's file volume had a year of clean data and a straight line that said twenty months to full, with high confidence. The forecast went in the register and everyone felt well organised. Nobody had asked the projects team. They were three weeks from moving the old document archive onto the same volume because "it had the most space". The migration ran on a Tuesday. The line jumped from fifty-five percent to ninety-one percent by Wednesday. And the twenty-month forecast became a six-week one overnight. The disk order was placed on the Thursday, at the urgent rate, and arrived with a fortnight to spare. The projects team said they would have mentioned it if anyone had asked, which was true.


The ruler did nothing wrong. It described the past faithfully, and the past did not contain the archive. The missing piece was the quarterly question, and it costs one email.


Checklist


  • Export daily maxima, not averages, for at least a year where the history exists.
  • Remove blank and zero rows caused by collector gaps before fitting.
  • Put a moving average on the chart to see the shape; fit the line to the raw daily maxima.
  • Fit the whole window and the last six months; use the two crossing dates as the range.
  • Check for step changes and fit only after the most recent one.
  • Ask the people who start projects what is coming, and add each answer as a named step.
  • State the forecast as a range with a most-likely month and a confidence word.
  • Note which seasons the data is missing.
  • Save the export and the workbook next to the register with the date in the file name.
  • Set a review date and redo the fit then, whatever the confidence was.

Where to go next


You can now turn a year of history into a date, a range, and a confidence word. That is everything the register needs in its trend and projected-full columns. The next question is what to do with the date, and that depends on how long it takes to buy. The guide on lead time takes the projected full date and works backwards to the day you must order. That guide and this one both sit inside Capacity Planning From Monitoring History. Keep the ruler. It is the only forecasting tool that has never once asked for a bigger monitor.


Put this into practice with SysPeep Monitor

The export in step one is the whole trick, and it only exists if the monitoring system kept the history. SysPeep Monitor keeps long-term graphs and reports in one web console. It stores the data in SQLite, MySQL/MariaDB, or SQL Server. That way, a year of daily peaks is there to fit a line through. It lets you put a threshold on utilisation so the crossing you forecast also arrives as an email. The free trial installs on any Windows machine.

Download Free TrialLearn About SysPeep