Tuesday, August 5, 2008

A perl-wrapped java-based Nagios MS SQL plugin: check_mssql

I didn't find a nagios plugin that I like for monitoring our MS SQL databases. So I hacked up one in java using the jtds jdbc driver. Yes, the 2 minor things I do to improve "security" are horrible. I'll handle those things better later....

First, the java code... MSSQL.java


import java.sql.*;

// constr (jdbc:jtds:sqlserver://hostname:1433/DBNAME), user, pw, query
public class MSSQL {
public static String MSG[] = {"OK", "WARNING", "CRITICAL", "UNKNOWN"};
public static String msg = "";
public static int s = 3; // default is UNKNOWN
public static long n = 0;
public static long t0ms = System.currentTimeMillis();

public static String rot13(String in) {
StringBuilder sb = new StringBuilder(); int c;
for (int b : in.getBytes()) {
c = b & 32; b &= ~c;
b = ((b >= 'A') && (b <= 'Z') ? ((b - 'A' + 13) % 26 + 'A') : b) | c;
sb.append((char) b);
}
return sb.toString();
}
public static void main(String[] args) {
Runtime.getRuntime().addShutdownHook(new Thread() {
public void run() {
String m = (new Exception().getStackTrace())[0].getClassName();
System.out.println(String.format("%s %s %s|time=%dms;;; rows=%d;;;",
m.substring(0, m.length() - 2), MSG[s], msg,
System.currentTimeMillis() - t0ms, n));
Runtime.getRuntime().halt(s);
}
});
try {
if (args.length != 4) {throw new Exception("4 args expected");}
String c=args[0]; String u=args[1]; String p=args[2]; String q=args[3];
Class.forName("net.sourceforge.jtds.jdbc.Driver");
Connection conn = DriverManager.getConnection(c, u, rot13(p));
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(String.format("select * from %s", q));
n = 0; while (rset.next()) { n++; } stmt.close(); conn.close();
msg = String.format("%d rows",n); s = 0;
} catch (Exception e) {
s = 2; String m = e.getMessage(); if (m != null) { msg = m; }
}
}
}

Now, how to build the perl wrapper... buildmyself.sh

#!/bin/sh
wget http://surfnet.dl.sourceforge.net/sourceforge/jtds/jtds-1.2.2-dist.zip && \
unzip jtds-1.2.2-dist.zip jtds-1.2.2.jar && \
jar xvf jtds-1.2.2.jar > /dev/null && \
rm -rf META-INF && \
javac MSSQL.java && \
printf "Main-Class: MSSQL\n" > .X && \
jar cfm MSSQL.jar .X MS*.class net $0 MSSQL.java && \
rm -rf .X MS*.class jtds-1.2.2-dist.zip jtds-1.2.2.jar check_mssql net
cat > check_mssql << EOF
#!/usr/bin/env perl
use strict;
use File::Temp qw/tempfile/;
my @jar = <DATA>; my \$r; foreach (@jar) { \$r .= unpack('u',\$_); }
my(\$fh, \$fn) = &tempfile('/tmp/MSSQL-XXXXXX', SUFFIX => '.jar');
binmode(\$fh); syswrite(\$fh, \$r) || die('write failed');
close(\$fh) || die('close failed');
my @args = @ARGV; unshift @args, ("java", "-jar", \$fn);
system @args; \$r = \$? >> 8; END { unlink \$fn; \$? = \$r; }
__END__
EOF
uuencode MSSQL.jar MSSQL.jar > .X
N=`wc -l .X | awk '{print $1}'`
tail -`expr $N - 1` .X | head -`expr $N - 2` >> check_mssql && rm .X MSSQL.jar
chmod a+rx check_mssql


Note to self: On fedora uuencode is in the sharutils package...

Now when you run buildmyself.sh, it produces a perl-wrapped check_mssql that looks like this:

#!/usr/bin/env perl
use strict;
use File::Temp qw/tempfile/;
my @jar = <DATA>; my $r; foreach (@jar) { $r .= unpack('u',$_); }
my($fh, $fn) = &tempfile('/tmp/MSSQL-XXXXXX', SUFFIX => '.jar');
binmode($fh); syswrite($fh, $r) || die('write failed');
close($fh) || die('close failed');
my @args = @ARGV; unshift @args, ("java", "-jar", $fn);
system @args; $r = $? >> 8; END { unlink $fn; $? = $r; }
__END__
M4$L#!`H``````$))!3D````````````````)````345402U)3D8O4$L#!`H`
M```(`%Q)!3D$N9B<%````!(````4````345402U)3D8O34%.249%4U0N34;S
...


Usage like this:
vocal(jason): check_mssql jdbc:jtds:sqlserver://hostname:1433/DBNAME user passrot13 "FOO where BAR='baz'"
MSSQL OK 397 rows|time=1534ms;;;
vocal(jason): echo $?
0

4 comments:

Chetan said...
This comment has been removed by the author.
Chetan said...

Successfully build the check_mssql plugin.

But getting erro while authenticating against SQL Server 2005 and SQL Server 2000

Login is correct.

---
MSSQL CRITICAL Login failed for user 'sa'.|time=155ms;;; rows=0;;;
---

Jason Brazile said...

Chetan, the password should be "encrypted" in rot13 - that is one of the horrible "security" hacks I referred to in my post.

Actzipild said...
This comment has been removed by the author.