microsoft-sql

#!/usr/bin/env ruby

=begin

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

=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}'
		       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 = []
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 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}.value #{row[0].to_i}"
  end
end
sth.finish
dbh.disconnect