Performance Counter Trending and Forecasting report

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s