Munin contributed plugins

#!/usr/bin/env ruby

=begin

Munin Plugin for MSSQL - log files 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_logfilesizes.rb /etc/munin/plugins/mssql_logfilesizes.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

=end

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}'"

logfilesize_query = "SELECT cntr_value/1024.0 from sys.dm_os_performance_counters

                     WHERE counter_name = 'Log File(s) Size (KB)'

                     AND object_name = 'SQLServer:Databases'

                     AND instance_name = ?"

all_instance_names = []

sth = dbh.execute(instance_name_query)

sth.fetch do |row|

  all_instance_names.push(row[0].strip)

end

sth.finish

#

# autoconf

#

case ARGV[0]

when '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

#

when 'config'

  puts 'graph_args --base 1024k -r --lower-limit 0'

  puts 'graph_title MSSQL DB Log File Sizes'

  puts 'graph_category db'

  puts 'graph_info This graph shows DB Log File Sizes (MB)'

  puts 'graph_vlabel MB'

  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 GAUGE"

    puts "#{s}.draw LINE1"

  end

  exit 0

end

sth = dbh.prepare(logfilesize_query)

all_instance_names.sort.each do |k|

  sth.execute(k)

  sth.fetch do |row|

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

  end

end

sth.finish

dbh.disconnect