Munin contributed plugins

#!/usr/bin/env ruby

#

# Munin Plugin for MSSQL - transaction monitoring

#

# Author: Wilfred Chau <openapp.developer@gmail.com>

# Date: 2011-05-18

# Version: 1.0

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License version 2

# as published by the Free Software Foundation.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

# GNU General Public License for more details.

#

# You should have received a copy of the GNU General Public License along

# with this program; if not, write to the Free Software Foundation, Inc.,

# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

#

#

# Prerequistes:

#	1) /etc/odbc.ini and /etc/freetds.conf

#	2) rubygems

#	3) ruby-dbi

#

# Usage:

#	1) copy this script to the munin install plugins directory (e.g. /usr/share/munin/plugins)

#	2) chmod to allow executable to others

#	3) create symbolic link in /etc/munin/plugins

#          ln -s /usr/share/munin/plugins/mssql_transaction.rb /etc/munin/plugins/mssql_transaction.rb

#

# Parameters:

#	autoconf

#	config (required)

#

# Config variables:

#	sqluser : mssql user who has view server state privilege

#	sqlpass : password for the mssql user

#	dsn     : datasource name as defined in /etc/odbc.ini

#	instance: instance to monitor

#

#%# family=auto

#%# capabilities=autoconf

require 'rubygems'

require 'dbi'

sqluser  = 'rubyuser'

sqlpass  = 'rubyuser'

dsn      = 'TESTSQL'

instance = 'AdventureWorks'

#

# Queries

#

#

dbh = DBI.connect("DBI:ODBC:#{dsn}",sqluser,sqlpass)

instance_name_query = "SELECT distinct instance_name

                       FROM sys.dm_os_performance_counters

                       WHERE instance_name = '#{instance}'

		       and object_name = 'SQLServer:Databases'

                       order by instance_name"

transaction_query = "select cntr_value from sys.dm_os_performance_counters

                     where counter_name = 'transactions/sec'

                     and object_name = 'SQLServer:Databases'

                     and instance_name = ?"

all_instance_names  = Array.new

sth = dbh.execute(instance_name_query)

sth.fetch do |row|

	all_instance_names.push(row[0].strip)

end

sth.finish

#

# autoconf

#

if ARGV[0] == "autoconf"

	if all_instance_names.length > 1 && sqluser.length > 1 && sqlpass.length > 1

		puts "yes"

	else

		puts "no"

		puts "Usage: #{__FILE__} autoconf|conf"

	end

	exit 0

#

# config definition

#

elsif ARGV[0] == "config"

	puts "graph_args --base 1000 -r --lower-limit 0"

	puts "graph_title MSSQL Transactions/s"

	puts "graph_category db"

	puts "graph_info This graph shows transactions/s"

	puts "graph_vlabel transactions/s"

	puts "graph_scale no"

	puts "graph_period second"

	all_instance_names.sort.each do |s|

		puts "#{s}.label #{s}"

		puts "#{s}.info INSTANCE: #{s}"

		puts "#{s}.type DERIVE"

		puts "#{s}.draw LINE1"

	end

	exit 0

end

#

# Getting data

#

sth = dbh.prepare(transaction_query)

all_instance_names.sort.each do |k|

	sth.execute(k)

	sth.fetch do |row|

		# since type is DERIVE, need to convert value to integer then to string

		puts "#{k.to_s}.value #{row[0].to_i.to_s}"

	end

end

sth.finish

dbh.disconnect