/[nagios-plugins-perl]/trunk/plugins/check_mssql_monitor.pl
ViewVC logotype

Contents of /trunk/plugins/check_mssql_monitor.pl

Parent Directory Parent Directory | Revision Log Revision Log


Revision 184 - (show annotations) (download)
Tue Mar 21 07:38:07 2017 UTC (3 years, 8 months ago) by racvision
File MIME type: text/plain
File size: 13845 byte(s)
check_mssql_monitor: integration nouvelle fonctionnalite, increment version
1 #! /usr/bin/perl -w
2 #
3 # check_mssql_monitor - Return cpu workload from sp_monitor procedure of a SQL Server
4 #
5 # This script requires the FreeTDS library and DBD::Sybase Perl
6 # module. The SYBASE environment variable also needs to be defined.
7 # Make sure FreeTDS is compiled with --with-tdsver=8.0 !!!
8 #
9 # It also requires File:::Basename, Nagios::Plugins and Time::HiRes.
10 #
11 # Copyright (c) 2008 Jean-Marc Amiaud <jm@amiaud.org>
12 # Copyright (c) 2010-2017 St├ęphane Urbanovski <stephane.urbanovski@ac-nancy-metz.fr>
13 #
14 # This program is free software; you can redistribute it and/or
15 # modify it under the terms of the GNU General Public License
16 # as published by the Free Software Foundation; either version 2
17 # of the License, or (at your option) any later version.
18 #
19 # This program is distributed in the hope that it will be useful,
20 # but WITHOUT ANY WARRANTY; without even the implied warranty of
21 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22 # GNU General Public License for more details.
23 #
24 # You should have received a copy of the GNU General Public License
25 # along with this program; if not, write to the Free Software
26 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
27 #
28
29 use strict;
30 use warnings;
31
32 use vars qw($PROGNAME $VERSION);
33 use File::Basename qw(basename);
34 use Nagios::Plugin;
35 #use Nagios::Plugin::Functions qw(max_state);
36 use Nagios::Plugin::Threshold;
37 use Time::HiRes qw(gettimeofday tv_interval);
38 use DBI;
39
40 use Data::Dumper;
41
42 $PROGNAME = basename($0);
43 $VERSION = '0.10.0';
44
45 my $driver = 'Sybase';
46 # my $database = 'master';
47 my $query = 'sp_monitor';
48 my $tempPath = '/tmp';
49
50 my $np = Nagios::Plugin->new(
51 usage => "Usage: %s -H <hostname> [ -p <port> ] [ -t <timeout> ]\n"
52 . " -U <user> -P <pass> [ -w <response_time_warn_range> ] [ -c <response_time_crit_range> ]\n"
53 . " [ --cpuWarning <cpu_warn_range> ] [ --cpuCritical <cpu_crit_range> ]\n"
54 . " [ --ioWarning <io_warn_range> ] [ --ioCritical <io_crit_range> ]\n"
55 . " [ --mirror ]\n"
56 . ' [ -s ]',
57 version => $VERSION,
58 plugin => $PROGNAME,
59 shortname => uc($PROGNAME),
60 blurb => 'Retreive server workload values with query the sp_monitor procedure',
61 extra => "\n\nCopyright (c) 2008 Hotplug SARL\nCopyright (c) 2010-2017 St├ęphane Urbanovski",
62 timeout => 30,
63 );
64
65 $np->add_arg(
66 spec => 'hostname|H=s',
67 help => "-H, --hostname=<hostname>\n SQL Database hostname",
68 required => 1,
69 );
70
71 $np->add_arg(
72 spec => 'port|p=i',
73 help => "-p, --port=<port>\n"
74 . ' SQL TCP port (default: driver-dependent).',
75 required => 0,
76 default => 1433,
77 );
78
79 $np->add_arg(
80 spec => 'username|U=s',
81 help => "-U, --username=<username>\n"
82 . ' Username to connect with.',
83 required => 1,
84 );
85
86 $np->add_arg(
87 spec => 'password|P=s',
88 help => "-P, --password=<password>\n"
89 . ' Password to use with the username.',
90 required => 1,
91 );
92
93 $np->add_arg(
94 spec => 'database|b=s',
95 help => "-b, --database=<database>\n",
96 default => '',
97 );
98
99 $np->add_arg(
100 spec => 'warning|w=s',
101 help => "-w, --warning=THRESHOLD\n"
102 . " Warning threshold for the responce time (ms). See\n"
103 . " http://nagiosplug.sourceforge.net/developer-guidelines.html#THRESHOLDFORMAT\n"
104 . ' for the threshold format.',
105 default => ':500',
106 );
107
108 $np->add_arg(
109 spec => 'critical|c=s',
110 help => "-c, --critical=THRESHOLD\n"
111 . " Critical threshold for the responce time (ms). See\n"
112 . " http://nagiosplug.sourceforge.net/developer-guidelines.html#THRESHOLDFORMAT\n"
113 . ' for the threshold format.',
114 default => ':2000',
115 );
116
117 $np->add_arg(
118 spec => 'ioWarning=s',
119 help => "--ioWarning=THRESHOLD\n"
120 . " Warning threshold for the io access value. Value must be numeric. See\n"
121 . " http://nagiosplug.sourceforge.net/developer-guidelines.html#THRESHOLDFORMAT\n"
122 . ' for the threshold format.',
123 required => 0,
124 );
125
126 $np->add_arg(
127 spec => 'ioCritical=s',
128 help => "--ioCritical=THRESHOLD\n"
129 . " Critical threshold for the io access value. Value must be numeric. See\n"
130 . " http://nagiosplug.sourceforge.net/developer-guidelines.html#THRESHOLDFORMAT\n"
131 . ' for the threshold format.',
132 required => 0,
133 );
134
135 $np->add_arg(
136 spec => 'cpuWarning=s',
137 help => "--cpuWarning=THRESHOLD\n"
138 . " Warning threshold for the cpu load value. Value must be numeric. See\n"
139 . " http://nagiosplug.sourceforge.net/developer-guidelines.html#THRESHOLDFORMAT\n"
140 . ' for the threshold format.',
141 required => 0,
142 );
143
144 $np->add_arg(
145 spec => 'cpuCritical=s',
146 help => "--cpuCritical=THRESHOLD\n"
147 . " Critical threshold for the cpu load value. Value must be numeric. See\n"
148 . " http://nagiosplug.sourceforge.net/developer-guidelines.html#THRESHOLDFORMAT\n"
149 . ' for the threshold format.',
150 required => 0,
151
152 );
153 $np->add_arg(
154 spec => 'mirror|m',
155 help => "-m, --mirror\n Check mirroring status.",
156 required => 0,
157 );
158 $np->add_arg(
159 spec => 'show|s+',
160 help => "-s, --show\n Show the result values in the status text.",
161 required => 0,
162 );
163
164 $np->getopts;
165
166 # Assign, then check args
167
168 my $hostname = $np->opts->hostname;
169 my $port = $np->opts->port;
170 my $username = $np->opts->username;
171 my $password = $np->opts->password;
172 my $database = $np->opts->database;
173 my $timeWarning = $np->opts->warning;
174 my $timeCritical = $np->opts->critical;
175 my $show = $np->opts->show;
176 my $verbose = $np->opts->verbose;
177 my $checkMirror = $np->opts->mirror;
178
179 my $cpuWarning = $np->opts->cpuWarning;
180 my $cpuCritical = $np->opts->cpuCritical;
181 my $ioWarning = $np->opts->ioWarning;
182 my $ioCritical = $np->opts->ioCritical;
183
184
185 # TODO: Should check if the DBI driver exists
186
187 $np->nagios_exit(UNKNOWN, 'Hostname contains invalid characters.')
188 if ($hostname =~ /\`|\~|\!|\$|\%|\^|\&|\*|\||\'|\"|\<|\>|\?|\,|\(|\)|\=/);
189
190 $np->nagios_exit(UNKNOWN, 'Port must be an integer between 1 and 65535.')
191 if ($port && ($port < 1 || $port > 65535));
192
193 $np->nagios_exit(UNKNOWN, 'Username is required.')
194 if ($username eq '');
195
196 $np->nagios_exit(UNKNOWN, 'Password is required.')
197 if ($password eq '');
198
199
200
201 # Note: There's no automated way to check if ranges makes sense, so you can
202 # have a WARNING range within a CRITICAL range with no warning. I'm not going
203 # to do N::P's job here so such thresholds are allowed for now.
204
205 my $cs = "DBI:$driver:host=$hostname" . ($port ? ";port=$port" : '');
206
207 warn("Trying to connect. Connect string: '$cs'\n") if ($verbose);
208 warn("Using the following credentials: $username,$password\n") if ($verbose > 2);
209
210 # Just in case of problems, let's not hang Nagios
211 alarm $np->opts->timeout;
212
213 my $timestart = [gettimeofday];
214
215 my $dbh = DBI->connect($cs,$username,$password,{PrintWarn=>($verbose ? 1 : 0),PrintError=>($verbose ? 1 : 0)})
216 or $np->nagios_exit(CRITICAL, $DBI::errstr);
217
218
219 warn("Connected. Querying server with '$query'\n") if ($verbose > 1);
220
221 my $result = '';
222
223 # selectrow_array behavior in scalar context is undefined (driver-dependent)
224 # if multiple collumns are returned. Just get the first or only collumn:
225 my $sth = $dbh->prepare($query) or $np->nagios_exit(CRITICAL, $DBI::errstr);
226
227 $sth->execute() or die "Unable to execute '$query' :" . $dbh->errstr . "\n";
228
229 my $href;
230
231 while ($href = $sth->fetchrow_hashref or $sth->{syb_more_results}) {
232 print Dumper($href) if $verbose;
233 last if (defined $href->{cpu_busy});
234 }
235 $sth->finish;
236
237 for (keys %{$href}) {
238 $href->{$_} =~ s/\d*\((\d+)\)-\d+%/$1/;
239 }
240
241 my $totalCpu = $href->{'cpu_busy'} + $href->{'io_busy'} + $href->{'idle'};
242
243 $totalCpu++ if ( $totalCpu == 0 );
244
245 my $cpu = 100*$href->{'cpu_busy'}/$totalCpu;
246 my $io = 100*$href->{'io_busy'}/$totalCpu;
247 my $idle = 100*$href->{'idle'}/$totalCpu;
248
249
250 $query = 'SELECT
251 SERVERPROPERTY(\'InstanceName\') AS \'instancename\',
252 SERVERPROPERTY(\'ServerName\') AS \'servername\',
253 SERVERPROPERTY(\'ProductVersion\') AS \'serverversion\',
254 SERVERPROPERTY(\'ComputerNamePhysicalNetBIOS\') AS \'hostname\',
255 SERVERPROPERTY(\'IsClustered\') AS \'iscluster\'
256 ';
257
258
259 $sth = $dbh->prepare($query) or $np->nagios_exit(CRITICAL,"SQL prepare error :" . $DBI::errstr);
260
261 my $serverName = 'unknown';
262 my $serverVersion = 'unknown';
263 my $serverHostName = '';
264 my $instanceName = 'default';
265 my $isCluster = 0;
266 my $mirrorName = 'none';
267 my $mirrorRole = undef;
268 my $mirrorStatus = 'unknown';
269
270 #$sth->execute() or die "Unable to execute '$query' :" . $dbh->errstr . "\n";
271
272 $sth->execute() or $np->nagios_exit(CRITICAL,'SQL execute error :' . $dbh->errstr);
273
274 while($href = $sth->fetchrow_hashref or $sth->{syb_more_results}) {
275 # print Dumper($href);
276 if ( defined($href->{'servername'}) ) {
277 $serverName = $href->{'servername'};
278 $instanceName = $href->{'instancename'} if ($href->{'instancename'} );
279 $serverVersion = $href->{'serverversion'};
280 $serverHostName = $href->{'hostname'};
281 $isCluster = $href->{'iscluster'};
282 last;
283 }
284 }
285
286 if ( $checkMirror ) {
287 warn("Checking mirror state for '$database'\n") if ($verbose);
288
289 $query = 'SELECT d.name, m.mirroring_role_desc, m.mirroring_state_desc
290 FROM sys.database_mirroring m
291 JOIN sys.databases d ON m.database_id = d.database_id
292 WHERE mirroring_state_desc IS NOT NULL';
293
294 if ( $database ne '' ) {
295 $query .= ' AND name = \''.$database.'\'';
296 }
297
298 $sth = $dbh->prepare($query) or $np->nagios_exit(CRITICAL,"SQL prepare error :" . $DBI::errstr);
299 $sth->execute() or $np->nagios_exit(CRITICAL,'SQL execute error :' . $dbh->errstr);
300
301 while($href = $sth->fetchrow_hashref or $sth->{syb_more_results}) {
302 #print Dumper($href) if ($verbose);
303 if ( defined($href->{'name'}) ) {
304 $mirrorName = $href->{'name'};
305 $mirrorRole = $href->{'mirroring_role_desc'};
306 $mirrorStatus = $href->{'mirroring_state_desc'};
307 last;
308 }
309 }
310 }
311
312 $sth->finish;
313
314 $dbh->disconnect;
315
316 my $timeend = [gettimeofday];
317
318 #Turn off alarm
319 alarm(0);
320
321 my $elapsed = tv_interval($timestart, $timeend)*1000; # ms
322 warn("Request complete. Time elapsed: $elapsed ms\n") if ($verbose);
323
324 # First set the cpu and io thresholds to validate them and get the threshold object.
325 # $np->set_thresholds(
326 # warning => $cpuWarning,
327 # critical => $cpuCritical,
328 # );
329 # my $cpuThreshold = $np->threshold;
330
331 my $cpuThreshold = Nagios::Plugin::Threshold->set_thresholds(
332 warning => $cpuWarning,
333 critical => $cpuCritical,
334 );
335
336 # $np->set_thresholds(
337 # warning => $ioWarning,
338 # critical => $ioCritical,
339 # );
340 # my $ioThreshold = $np->threshold;
341
342 my $ioThreshold = Nagios::Plugin::Threshold->set_thresholds(
343 warning => $ioWarning,
344 critical => $ioCritical,
345 );
346
347
348 my $timeThreshold = Nagios::Plugin::Threshold->set_thresholds(
349 warning => $timeWarning,
350 critical => $timeCritical,
351 );
352
353 # Then we can set the normal thresholds for validation and future use.
354 # $np->set_thresholds(
355 # warning => $warning,
356 # critical => $critical,
357 # );
358
359
360 # Add all performance data
361
362 $np->add_perfdata(
363 label => "time",
364 value => $elapsed,
365 uom => 'ms',
366 threshold => $timeThreshold,
367 );
368
369 $np->add_perfdata(
370 label => "cpu",
371 value => sprintf("%.3f",$cpu),
372 uom => '%',
373 threshold => $cpuThreshold,
374 );
375
376 $np->add_perfdata(
377 label => "io",
378 value => sprintf("%.3f",$io),
379 uom => '%',
380 threshold => $ioThreshold,
381 );
382
383 $np->add_perfdata(
384 label => "idle",
385 value => sprintf("%.3f",$idle),
386 uom => '%',
387 );
388
389
390 my $lastState = 0;
391 my $lastServerHostName = '';
392 my $serverSwitch = '';
393
394 # my @results;
395
396 # push (@results, $np->check_threshold($elapsed));
397
398 my $checkStatusTime = $np->check_threshold($elapsed);
399
400 if ($isCluster && $serverHostName) {
401 my $tempFile = $tempPath.'/'.$hostname.'_check_mssql_'.$instanceName;
402
403 if ((-e $tempFile) && (-r $tempFile) && (-w $tempFile)) {
404 open (FH, '<',$tempFile) or exit CRITICAL;
405 $lastState = <FH>;
406 $lastServerHostName = <FH>;
407 close (FH);
408
409 chomp($lastState);
410 chomp($lastServerHostName);
411 }
412
413
414 open (FH, '>'.$tempFile) or exit CRITICAL;
415 print FH $timeend->[0]."\n";
416 print FH "$serverHostName\n";
417 close (FH);
418
419 warn ('server: prev=' . $lastServerHostName . ', now=' . $serverHostName . ' time='.($timeend->[0] - $lastState )) if ($verbose);
420
421 if ( $lastState && ($timeend->[0] - $lastState < 30*60) && $lastServerHostName ne $serverHostName ) {
422 $serverSwitch = sprintf("Cluster switch from '%s' to '%s'",$lastServerHostName,$serverHostName );
423 }
424
425 }
426
427
428 # push (@results, $np->check_threshold(check => $cpu, warning => $cpuWarning, critical => $cpuCritical));
429 # push (@results, $np->check_threshold(check => $io, warning => $ioWarning, critical => $ioCritical));
430 # push (@results, $idle);
431
432
433 # my $checkStatusCpu = $np->check_threshold(check => $cpu, warning => $cpuWarning, critical => $cpuCritical) ;
434 my $checkStatusCpu = $cpuThreshold->get_status($cpu);
435 if ( $checkStatusCpu != OK ) {
436 $np->add_message($checkStatusCpu, sprintf("CPU alerte (%d%%)",$cpu) );
437 }
438
439 my $checkStatusIo = $np->check_threshold(check => $io, warning => $ioWarning, critical => $ioCritical) ;
440
441 if ( $checkStatusIo != OK ) {
442 $np->add_message($checkStatusIo, sprintf("IO alerte (%d%)",$io) );
443 }
444
445 # print Dumper(\@results);
446
447 warn ('Thresholds results: time=' . $elapsed . ', cpu=' . $cpu . ', io=' . $io . ', idle=' . $idle) if ($verbose);
448
449
450 my $msg = '';
451 my $runOnMsg = '';
452 my $mirrorMsg = '';
453
454 if ($isCluster) {
455 $runOnMsg = sprintf(" runing on '%s'", $serverHostName);
456 }
457
458
459 $np->add_message(OK, sprintf("SQL Server V%s '%s' %s responded in %.2f ms (load : cpu=%.2f%% io=%.2f%% idle=%.2f%% )" ,$serverVersion,$instanceName,$runOnMsg,$elapsed,$cpu,$io,$idle) );
460
461 # $mirrorStatus = 'GRUMPF';
462 if ( $checkMirror ) {
463 if ( defined($mirrorRole) ) {
464 my $mirrorMsg .= "Mirror $mirrorStatus on $mirrorName ($mirrorRole)";
465 if ( $mirrorStatus ne 'SYNCHRONIZED' ) {
466 $np->add_message(CRITICAL, $mirrorMsg );
467 } else {
468 $np->add_message(OK, $mirrorMsg );
469 }
470 } else {
471 $np->add_message(UNKNOWN, 'Mirrored database not found: '.$database );
472 }
473 }
474 if ($serverSwitch) {
475 $np->add_message(WARNING, $serverSwitch );
476 }
477
478 my ($status, $message) = $np->check_messages('join' => ' ');
479
480 $np->nagios_exit($status, $message);

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.8