1#!/usr/local/bin/perl -w
2# -*- perl -*-
3
4use strict;
5
6=head1 NAME
7
8freeradius_sqlippools_ - Plugin to monitor IP pool utilisation
9
10=head1 CONFIGURATION
11
12This is a wildcard plugin to support fetching the status of multiple sqlippool
13instances.
14
15It can also be linked directly (as with a non-wildcard plugin) to present a
16combined graph showing the percentage utilisation of all pools.
17
18It is likely that a common configuration will apply to all plugins but this
19doesn't have to be so:
20
21    [freeradius_sqlippools_*]
22        env.fr_driver mysql
23        env.fr_host 192.0.2.1
24        env.fr_port 3306
25        env.fr_db radius
26        env.fr_user radius
27        env.fr_pass radpass
28
29fr_driver is the name of the Perl DBI driver used in the DSN connection string.
30The corresponding DBD module for the driver must be installed.
31
32You should omit fr_pass and specify fr_passfile to avoid placing the password
33in a plugin configuration file that is world accessible, e.g.:
34
35    [freeradius_sqlippools_mypool]
36        user radmonitor
37        group radmonitor
38        env.fr_driver Pg
39        env.fr_host 192.0.2.2
40        env.fr_port 5432
41        env.fr_db radius
42        env.fr_user radmonitor
43        env.fr_passfile /home/radmonitor/db_pass.txt
44
45=head1 AUTHORS
46
47Original Author: Network RADIUS
48
49=head1 LICENSE
50
51GPLv2
52
53=head1 MAGIC MARKERS
54
55 #%# family=auto
56 #%# capabilities=autoconf suggest
57
58=cut
59
60
61use File::Basename;
62use Storable qw(lock_store lock_retrieve);
63use DBI;
64
65use constant STATEFILE => "$ENV{MUNIN_PLUGSTATE}/freeradius_sqlippools.state";
66
67use constant SQL => <<'EOF';
68SELECT
69	DISTINCT pool_name AS pool_name,
70	COUNT(id) OVER (PARTITION BY pool_name) AS total,
71	SUM(CASE WHEN expiry_time > NOW() THEN 1 ELSE 0 END) OVER (PARTITION BY pool_name) AS used
72FROM radippool
73EOF
74
75my $script = basename($0);
76(my $instance) = $script =~ /freeradius_sqlippools_(.+)/;
77my $command = $ARGV[0] || 'show';
78
79autoconf() if $command eq 'autoconf';
80suggest() if $command eq 'suggest';
81config_instance($instance) if $command eq 'config' && defined $instance;
82config_combined() if $command eq 'config' && !defined $instance;
83show_instance($instance) if $command eq 'show' && defined $instance;
84show_combined() if $command eq 'show' && !defined $instance;
85
86exit;
87
88
89sub autoconf {
90	my $results;
91	eval {
92		$results = get_pools(1);
93	};
94	if ($results) {
95		print "yes\n";
96	} else {
97		print "no (Failed to read pool status from database)\n";
98	}
99}
100
101sub suggest {
102	my $pools = get_pools(1);
103	return unless defined $pools;
104	print "$_\n" foreach keys %{$pools};
105}
106
107sub config_instance {
108	my $instance = shift;
109
110	print <<EOF;
111graph_title FreeRADIUS SQL IP pool ($instance)
112graph_category Other
113graph_args -l 0
114total.label Total IPs
115total.draw AREA
116used.label Allocated IPs
117used.draw AREA
118EOF
119}
120
121sub config_combined {
122
123	print <<EOF;
124graph_title FreeRADIUS SQL IP pools
125graph_category Other
126graph_args -l 0 -u 100
127graph_vlabel Utilisation (%)
128EOF
129
130	my $pools = get_pools();
131	foreach (keys %{$pools}) {
132		print "$_.label Pool: $_\n";
133		print "$_.warning 90\n";
134		print "$_.critical 95\n";
135	}
136}
137
138sub show_instance {
139	my $instance = shift;
140	my $pool = get_pools()->{$instance};
141	print "total.value $pool->{total}\n";
142	print "used.value $pool->{used}\n";
143}
144
145sub show_combined {
146	my $pools = get_pools();
147	foreach (keys %{$pools}) {
148		my $util = $pools->{$_}->{used} * 100 / $pools->{$_}->{total};
149		print "$_.value $util\n";
150	}
151}
152
153sub get_pools {
154
155	my $no_cache = shift;  # Ensure that caching doesn't interfere with reconfiguration
156
157	# Read results from the cache unless stale or told not to
158	if (!$no_cache && -e STATEFILE && -M STATEFILE < 60/86400) {
159		return lock_retrieve(STATEFILE);
160	}
161
162	my $driver   = $ENV{'fr_driver'} || 'mysql';
163	my $host     = $ENV{'fr_host'}   || '127.0.0.1';
164	my $port     = $ENV{'fr_port'}   || '3306';
165	my $db       = $ENV{'fr_db'}     || 'radius';
166	my $user     = $ENV{'fr_user'}   || 'radius';
167	my $pass     = $ENV{'fr_pass'}   || 'radpass';
168	my $passfile = $ENV{'fr_passfile'};
169
170	# Read password from a file
171	if (!defined $pass && defined $passfile) {
172		open (my $FH, '<', $passfile) || die "Failed to open fr_passfile: $passfile";
173		$pass = <$FH>;
174		chomp $pass;
175		close $FH;
176	}
177
178	my $dsn;
179	if ($driver eq 'Oracle') {
180		$dsn = "DBI:$driver:$db";
181	} else {
182		$dsn = "DBI:$driver:database=$db;host=$host";
183	}
184	$dsn .= ";port=$port" if $port;
185
186	# Read the results by running our query against the database
187	my $dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1, PrintError => 0, AutoCommit => 1 });
188	my $sth = $dbh->prepare(SQL);
189	$sth->execute();
190	my $results=$sth->fetchall_hashref('pool_name');
191	$sth->finish();
192	$dbh->disconnect();
193
194	# Cache the results
195	if (!$no_cache && $results) {
196		lock_store($results,STATEFILE);
197	}
198
199	return $results;
200
201}
202
203# vim:syntax=perl
204