Munin contributed plugins

#!/usr/bin/env ruby

=begin

Munin Plugin for MSSQL - Buffer cache hit ratio monitoring

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

Date: 2011-05-19

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_buffercachehitratio.rb /etc/munin/plugins/mssql_buffercachehitratio.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

#%# family=auto

#%# capabilities=autoconf

=end

require 'rubygems'

require 'dbi'

sqluser  = 'rubyuser'

sqlpass  = 'rubyuser'

dsn      = 'TESTSQL'

#

# Queries

#

#

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

buffercachehitratio_query = "select (a.cntr_value * 1.0 / b.cntr_value) * 100.0

                            from sys.dm_os_performance_counters a

                            join (select cntr_value, object_name

                                  from sys.dm_os_performance_counters

                                  where counter_name = 'Buffer cache hit ratio base'

                                  and object_name = 'SQLServer:Buffer Manager') b

                            on a.object_name = b.object_name

                            where a.counter_name = 'Buffer cache hit ratio'

                            and a.object_name = 'SQLServer:Buffer Manager'"

#

# 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 Buffer Cache Hit Ratio "

  puts "graph_category db"

  puts "graph_info This graph shows Buffer Cache Hit Ratio"

  puts "graph_vlabel %"

  puts "graph_scale no"

  puts "graph_period second"

  puts "bc_hitratio.label BufferCacheHitRatio"

  puts "bc_hitratio.info BufferCacheHitRatio"

  puts "bc_hitratio.type GAUGE"

  puts "bc_hitratio.draw LINE1"

  exit 0

end

sth = dbh.execute(buffercachehitratio_query)

sth.fetch do |row|

  puts "bc_hitratio.value #{row[0].strip.to_s}"

end

sth.finish

dbh.disconnect