SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmonitor_computer]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tmonitor_computer](
	[computer] [varchar](20) NOT NULL,
 CONSTRAINT [PK_tmonitor_computer] PRIMARY KEY CLUSTERED 
(
	[computer] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmonitor_performanceCounterValue]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tmonitor_performanceCounterValue](
	[monitorDate] [datetime] NOT NULL CONSTRAINT [DF_tmonitor_performanceCounter__monitorDate]  DEFAULT (getutcdate()),
	[counterID] [smallint] NOT NULL,
	[value] [numeric](18, 2) NOT NULL,
 CONSTRAINT [PK_tmonitor_performanceCounter] PRIMARY KEY CLUSTERED 
(
	[monitorDate] ASC,
	[counterID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmonitor_performanceCounter]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tmonitor_performanceCounter](
	[counterID] [smallint] IDENTITY(1,1) NOT NULL,
	[object] [varchar](80) NOT NULL,
	[instance] [varchar](8) NOT NULL,
	[counter] [varchar](32) NOT NULL,
	[computer] [varchar](20) NOT NULL,
 CONSTRAINT [PK_tmonitor_counter] PRIMARY KEY CLUSTERED 
(
	[counterID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UC_tmonitor_counter__object_instance_counter_computer] UNIQUE NONCLUSTERED 
(
	[object] ASC,
	[instance] ASC,
	[counter] ASC,
	[computer] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[monitor_performanceCounterValue_add]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
 
CREATE procedure [dbo].[monitor_performanceCounterValue_add]
(
	@object varchar(80)
	,@instance varchar(8)
	,@counter varchar(32)
	,@computer varchar(20)
	,@value numeric(18,2)
)
as
set nocount on

declare @counterID smallint
set @counterID = (	select	counterID
					from	dbo.tmonitor_performanceCounter
					where	object = @object
							and instance = @instance
							and counter = @counter
							and computer = @computer)

if @counterID is null
begin
	insert	dbo.tmonitor_computer
	(
		computer
	)
	select	@computer
	where	@computer not in(select computer from dbo.tmonitor_computer)
	
	insert	dbo.tmonitor_performanceCounter
	(
		object
		,instance
		,counter
		,computer
	)
	values
	(
		@object
		,@instance
		,@counter
		,@computer
	)
	set @counterID = scope_identity()
end

insert	dbo.tmonitor_performanceCounterValue
(
	monitorDate
	,counterID
	,value
)
values
(
	default
	,@counterID
	,@value
)


' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tmonitor_performanceCounterValue_REF_tmonitor_performanceCounter__counterID__counterID]') AND parent_object_id = OBJECT_ID(N'[dbo].[tmonitor_performanceCounterValue]'))
ALTER TABLE [dbo].[tmonitor_performanceCounterValue]  WITH CHECK ADD  CONSTRAINT [FK_tmonitor_performanceCounterValue_REF_tmonitor_performanceCounter__counterID__counterID] FOREIGN KEY([counterID])
REFERENCES [dbo].[tmonitor_performanceCounter] ([counterID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tmonitor_performanceCounterValue] CHECK CONSTRAINT [FK_tmonitor_performanceCounterValue_REF_tmonitor_performanceCounter__counterID__counterID]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tmonitor_performanceCounter_REF_tmonitor_computer__computer__computer]') AND parent_object_id = OBJECT_ID(N'[dbo].[tmonitor_performanceCounter]'))
ALTER TABLE [dbo].[tmonitor_performanceCounter]  WITH CHECK ADD  CONSTRAINT [FK_tmonitor_performanceCounter_REF_tmonitor_computer__computer__computer] FOREIGN KEY([computer])
REFERENCES [dbo].[tmonitor_computer] ([computer])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tmonitor_performanceCounter] CHECK CONSTRAINT [FK_tmonitor_performanceCounter_REF_tmonitor_computer__computer__computer]

################################################################
# 
# include file for scripts that query the Win32_PerfFormattedData_XXX counters
# and call the monitor_performanceCounterValue_add procedure
#
################################################################

# initialize variables and db connection
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=dbServer;Integrated Security=SSPI;Initial Catalog=dbName");
$cmd = new-object system.Data.Sqlclient.SqlCommand("monitor_performanceCounterValue_add", $cn);

$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"

$cmd.Parameters.Add("@object",[System.Data.SqlDbType]"VarChar",80) | Out-Null
$cmd.Parameters.Add("@instance",[System.Data.SqlDbType]"VarChar",8) | Out-Null
$cmd.Parameters.Add("@counter",[System.Data.SqlDbType]"VarChar",32) | Out-Null
$cmd.Parameters.Add("@computer",[System.Data.SqlDbType]"VarChar",20) | Out-Null
$cmd.Parameters.Add("@value",[System.Data.SqlDbType]"Decimal") | Out-Null
$cmd.Parameters["@value"].Precision = 11
$cmd.Parameters["@value"].Scale = 2

# function for saving to db
function monitor_performanceCounterValue_add([string] $object, [string] $instance, [string] $counter, [string] $computer, [decimal] $value)
{
	$cmd.Parameters["@object"].Value = $object
	$cmd.Parameters["@counter"].Value = $counter
	$cmd.Parameters["@instance"].Value = $instance
	$cmd.Parameters["@computer"].Value = $server
	$cmd.Parameters["@value"].Value = $value
	$cn.Open()
	$cmd.ExecuteNonQuery() | Out-Null
	$cn.Close()
}

################################################################
# 
# monitors processor usage
# uses db function in include_monitor_performanceCounterValue.ps1 to save
#
################################################################

# include files
. .\include_monitor_performanceCounterValue.ps1

# declare an array of the servers to monitor
$servers = @("SERVER1","SERVER2","SERVER3")

# initialize variables
$pollIntervallSeconds = 10

#everloop
while($true)
{
	#loop the servers
	foreach($server in $servers)
	{
		trap [System.Data.SqlClient.SqlException] { break; } #cannot reach the db!
	
		$processor = Get-WmiObject -class Win32_PerfFormattedData_PerfOS_Processor -Property Name,PercentProcessorTime -computerName $server | where{$_.Name -eq "_Total"}
		if($processor -ne $null)
		{
			# if the system only has one processor, we don't get an array of objects but a single object
			if($processor -is [array])
			{
				for($i = 0; $i –lt $processor.Count; $i++)
				{
					monitor_performanceCounterValue_add "Processor" $processor[$i].Name "PercentProcessorTime" $server ([decimal]$processor[$i].PercentProcessorTime)
				}
			}
			else
			{
				monitor_performanceCounterValue_add "Processor" $processor.Name "PercentProcessorTime" $server ([decimal]$processor.PercentProcessorTime)
			}
			$processor = $null
		}
	}
	Start-Sleep -s $pollIntervallSeconds
}

# "cleanup" doubtfully reached ;-)
$cmd = $null
$cn = $null

################################################################
# 
# monitors disk space and usage
# uses db function in include_monitor_performanceCounterValue.ps1 to save
#
################################################################

# include files
. .\include_monitor_performanceCounterValue.ps1

# declare an array of the servers to monitor
$servers = @("SERVER1","SERVER2","SERVER3")

#loop the servers
foreach($server in $servers)
{
	trap [System.Data.SqlClient.SqlException] { break; } #cannot reach the db!
	# we use the -ErrorAction SilentlyContinue for the gwmi call, since it is a non-terminating error, and thus does not get caught in a trap

	$disk = Get-WmiObject -query "SELECT Name,CurrentDiskQueueLength,PercentFreeSpace,FreeMegabytes FROM Win32_PerfFormattedData_PerfDisk_LogicalDisk" -computerName $server -ErrorAction SilentlyContinue | where{$_.Name -ne "_Total"}
	if($disk -ne $null)
	{
		# if the system only has one disk, we don't get an array of objects but a single object
		if($disk -is [array])
		{
			for($i = 0; $i –lt $disk.Count; $i++)
			{
				monitor_performanceCounterValue_add "LogicalDisk" $disk[$i].Name "CurrentDiskQueueLength" $server ([decimal]$disk[$i].CurrentDiskQueueLength)
				monitor_performanceCounterValue_add "LogicalDisk" $disk[$i].Name "FreeMegabytes" $server ([decimal]$disk[$i].FreeMegabytes)
				monitor_performanceCounterValue_add "LogicalDisk" $disk[$i].Name "PercentFreeSpace" $server ([decimal]$disk[$i].PercentFreeSpace)
			}
		}
		else
		{
			monitor_performanceCounterValue_add "LogicalDisk" $disk.Name "CurrentDiskQueueLength" $server ([decimal]$disk.CurrentDiskQueueLength)
			monitor_performanceCounterValue_add "LogicalDisk" $disk.Name "FreeMegabytes" $server ([decimal]$disk.FreeMegabytes)
			monitor_performanceCounterValue_add "LogicalDisk" $disk.Name "PercentFreeSpace" $server ([decimal]$disk.PercentFreeSpace)
		}
		$disk = $null
	}
}

# cleanup
$cmd = $null
$cn = $null