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

5 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.
DevOps Online Training said...

Good blog post on Nagios, very useful really good information thanks for posting such a good information.

Best Regards,
CourseIng - DevOps Training in Hyderabad