Capacity and Trending reports are one of the shortcomings of the SCOM product in my opinion. I’ve created a report that does forecasting using Linear Regression.
Example of the output of the report

Linear Regression is a mathematical forumula that works out the y value by using the historical values and a constant
More information on the formula can be found here: https://en.wikipedia.org/wiki/Simple_linear_regression
Parameter Block used by the report

For the performance objects, counter and instances I’ve populate only the Processor, Memory and Disk Counters. You obviously can add more if you choose to.
Below is the SQL statement used in the report
declare @startdate datetime
Declare @enddate datetime
declare @forecasting int
set @startdate = @FromDate
set @enddate = @Todate
set @forecasting = @forecastingdays
if object_id(‘tempdb..#alldiskdata’) IS NOT NULL
DROP TABLE #alldiskdata
create table #alldiskdata
(idt int identity(1,1),
servername varchar(max),
sampledate datetime,
instancename varchar(max),
perf_object varchar(max),
perf_counter varchar(max),
samplevalue float)
insert into #alldiskdata
SELECT distinct x.path, dateadd(hour, 2, a.DateTime) as datetime, y.instancename, z.objectname, z.countername,a.averagevalue
FROM Perf.vperfdaily AS a with (nolock) INNER JOIN
vManagedEntity AS x with (nolock) ON a.ManagedEntityRowId = x.ManagedEntityRowId INNER JOIN
vPerformanceRuleInstance AS y with (nolock) ON a.PerformanceRuleInstanceRowId = y.PerformanceRuleInstanceRowId INNER JOIN
vPerformanceRule AS z with (nolock) ON y.RuleRowId = z.RuleRowId
wHERE dateadd(hour, 2, a.DateTime) >= @startdate
and z.ObjectName in (@Object) AND z.CounterName in (@Counter) and x.path in (@Servers)
and y.instancename in (@Instance)
order by x.path, z.objectname, z.countername, y.instancename, dateadd(hour, 2, a.DateTime)
if object_id(‘tempdb..#servernames’) IS NOT NULL
DROP TABLE #servernames
create table #servernames
(idt int identity(1,1),
servername varchar(max),
objectname varchar(max),
countername varchar(max),
instancename varchar(max)
)
insert into #servernames
select distinct servername, perf_object, perf_counter, instancename from #alldiskdata
–select * from #servernames
DECLARE @MaxRownum int
SET @MaxRownum = (SELECT MAX(idt) FROM #servernames)
DECLARE @Iter int
SET @Iter = (SELECT MIN(idt) FROM #servernames)
–select @maxrownum, @iter
if object_id(‘tempdb..#serverdiskdatatrend’) IS NOT NULL
DROP TABLE #serverdiskdatatrend
create table #serverdiskdatatrend
(idt bigint identity(1,1),
servername varchar(max),
sampledate datetime,
instancename varchar(max),
perf_object varchar(max),
perf_counter varchar(max),
samplevalue float,
forecastvalue float)
WHILE @Iter <= @MaxRownum
BEGIN
if object_id(‘tempdb..#serverdiskdata’) IS NOT NULL
DROP TABLE #serverdiskdata
create table #serverdiskdata
(idt int identity(1,1),
servername varchar(max),
sampledate datetime,
instancename varchar(max),
perf_object varchar(max),
perf_counter varchar(max),
samplevalue float)
declare @servername varchar(max)
select @servername = servername from #servernames where idt=@iter
declare @objectname varchar(max)
select @objectname = objectname from #servernames where idt=@iter
declare @countername varchar(max)
select @countername = countername from #servernames where idt=@iter
declare @instancename varchar(max)
select @instancename = instancename from #servernames where idt=@iter
insert into #serverdiskdata
select servername, sampledate, instancename, perf_object, perf_counter, samplevalue from #alldiskdata where servername=@servername and perf_object=@objectname
and perf_counter=@countername and instancename=@instancename
declare @n bigint
select @n=count(*) from #serverdiskdata
declare @m float
declare @b float
declare @y float
declare @xx float
–select @n
–select @n
–@m=slope
select @m =
((@n * sum(idt*samplevalue)) – (sum(idt)*sum(samplevalue)))/
((@n * sum(Power(idt,2)))-Power(Sum(idt),2)),
–@y is intercept
@y =
avg(samplevalue) -Avg(idt) *
((@n * Sum(idt*samplevalue)) – (Sum(idt) * Sum(samplevalue)))/
((@n * Sum(idt*idt)) – (Sum(idt) * Sum(idt))),
@xx=
avg(samplevalue) – ((@n * sum(idt*samplevalue)) – (sum(idt)*sum(samplevalue)))/((@n * sum(idt^2))-sum(idt)^2)* avg(idt)
from #serverdiskdata
declare @x int
declare @forestartdate datetime
declare @forestartvalue float
declare @forestartservername nvarchar(max)
declare @forestartinstancename nvarchar(max)
declare @foreobject nvarchar(max)
declare @forecounter nvarchar(max)
select @x = min(idt) from #serverdiskdata
while @x <= @n
begin
insert into #serverdiskdatatrend select servername, sampledate,instancename,perf_object, perf_counter, samplevalue,@m*@x+@y as yvalue from #serverdiskdata where idt=@x
select @forestartdate = sampledate from #serverdiskdata where idt=@x
select @forestartvalue = samplevalue from #serverdiskdata where idt=@x
select @forestartservername = servername from #serverdiskdata where idt=@x
select @forestartinstancename = instancename from #serverdiskdata where idt=@x
select @foreobject = perf_object from #serverdiskdata where idt=@x
select @forecounter = perf_counter from #serverdiskdata where idt=@x
set @x = @x+1
end
select @x = max(idt) from #serverdiskdata
declare @forecast int
set @forecast = @forecasting+@x
while @x <= @forecast
begin
set @forestartdate = dateadd(“day”, 1, @forestartdate)
insert into #serverdiskdatatrend values (@forestartservername, @forestartdate, @forestartinstancename, @foreobject, @forecounter, 0,@m*@x+@y)
set @x = @x+1
end
set @iter = @iter+1
end
select * from #serverdiskdatatrend