Created
October 8, 2013 09:04
-
-
Save lazyfrosch/6881834 to your computer and use it in GitHub Desktop.
IDO tool to cleanup the database of Icinga IDOutils
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env perl | |
#################################################################### | |
# Icinga IDO Cleanup Tool | |
# | |
# (c) 2012 NETWAYS GmbH | |
# by Markus Frosch <markus.frosch@netways.de> | |
# | |
# LICENSE: | |
# | |
# This work is made available to you under the terms of Version 2 of | |
# the GNU General Public License. A copy of that license should have | |
# been provided with this software, but in any event can be snarfed | |
# from http://www.fsf.org. | |
# | |
# This work 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 or visit their web page on the internet at | |
# http://www.fsf.org. | |
#################################################################### | |
# HELP {{{1 | |
=head1 NAME | |
idotool.pl - tool for maintaining the Icinga IDO database | |
=head1 SYNOPSIS | |
idotool.pl -l | -i <instance> | |
[-m <mode>] | |
[-w <what>] | |
[-v] | |
[-h] | |
[-V] | |
maintaining the Icinga IDO database | |
=head1 OPTIONS | |
=over | |
=item -l|--list | |
List known instances | |
=item -i|--instance <instance> | |
Instance to work on | |
=item -m|--mode <mode> | |
mode for the run: | |
* info (default) | |
* clean | |
=item -w|--what <what> | |
clean which part of the database: | |
* status | |
* config | |
* history | |
* all (default on info) | |
Warning: "all" cleans up everything related | |
to "instance" - including every reference to | |
the object ids. | |
=cut | |
# HELP }}} | |
# defaults {{{1 | |
# requirements | |
use strict; | |
use DBI; | |
use DBD::mysql; | |
use Data::Dumper; | |
use Getopt::Long qw(:config no_ignore_case bundling); | |
use Pod::Usage; | |
# global vars | |
our $opt; | |
our $DB; | |
our $instance_id; | |
my $version = "0.1"; | |
# }}} | |
# configuration | |
our $db_dsn = "dbi:mysql:icinga_idoutils:localhost:3306"; # database name and host | |
our $db_user = "icinga_idoutils"; # username | |
our $db_pass = "test123"; # password | |
our $prefix = "icinga_"; # table prefix | |
our $db_limit = 1000; # limit for delete queries | |
# to avoid blocking | |
# tables {{{1 | |
our @tables_config = ( | |
'acknowledgements', | |
'comments', | |
'commands', | |
'configfiles', | |
'configfilevariables', | |
'contact_addresses', | |
'contact_notificationcommands', | |
'contactgroup_members', | |
'contactgroups', | |
'contactnotificationmethods', | |
'contactnotifications', | |
'contacts', | |
'contactstatus', | |
'customvariables', | |
'customvariablestatus', | |
'host_contactgroups', | |
'host_contacts', | |
'host_parenthosts', | |
'hostdependencies', | |
'hostescalation_contactgroups', | |
'hostescalation_contacts', | |
'hostescalations', | |
'hostgroup_members', | |
'hostgroups', | |
'hosts', | |
'scheduleddowntime', | |
'service_contactgroups', | |
'service_contacts', | |
'servicedependencies', | |
'serviceescalation_contactgroups', | |
'serviceescalation_contacts', | |
'serviceescalations', | |
'servicegroup_members', | |
'servicegroups', | |
'services', | |
'timeperiod_timeranges', | |
'timeperiods', | |
); | |
our @tables_status = ( | |
'hoststatus', | |
'programstatus', | |
'runtimevariables', | |
'servicestatus', | |
); | |
our @tables_history = ( | |
'commenthistory', | |
'conninfo', | |
'downtimehistory', | |
'eventhandlers', | |
'externalcommands', | |
'flappinghistory', | |
'logentries', | |
'notifications', | |
'processevents', | |
'slahistory', | |
'statehistory', | |
'systemcommands', | |
'hostchecks', | |
'servicechecks', | |
'timedevents', | |
'timedeventqueue', | |
); | |
# }}} | |
# main program {{{1 | |
# read commandline config | |
GetOptions( | |
"l|list" => \$opt->{list}, | |
"m|mode=s" => \$opt->{mode}, | |
"w|what=s" => \$opt->{what}, | |
"i|instance=s" => \$opt->{instance}, | |
"i|instance=i" => \$opt->{instance}, | |
"v|verbose:s" => \$opt->{verbose}, | |
"h|help" => \$opt->{help}, | |
"V|version" => \$opt->{version} | |
); | |
# should print version? | |
if (defined $opt->{version}) { print "idotool.pl ".$version."\n"; exit 0; } | |
# should print help? | |
if ($opt->{help}) { pod2usage(1); } | |
# connect database | |
connect_db(); | |
# requested listing? | |
if($opt->{list}) { | |
list_instances(); | |
exit; | |
} | |
# check instance | |
if(!defined $opt->{instance}) { | |
handleerror("instance required!"); | |
} | |
# check mode | |
if(!$opt->{mode}) { | |
$opt->{mode} = "info"; | |
} | |
# check what | |
if(!$opt->{what} && $opt->{mode} eq "info") { | |
$opt->{what} = "all"; | |
} | |
elsif(!$opt->{what}) { | |
handleerror("what required!"); | |
} | |
if($opt->{mode} eq "info") { | |
info(); | |
info_objects(); | |
if($opt->{what} eq "all") { | |
info_status(); | |
info_config(); | |
info_history(); | |
} | |
elsif($opt->{what} eq "status") { | |
info_status(); | |
} | |
elsif($opt->{what} eq "config") { | |
info_config(); | |
} | |
elsif($opt->{what} eq "history") { | |
info_history(); | |
} | |
else { | |
handleerror("what: '".$opt->{what}."' not implemented!"); | |
} | |
} | |
elsif($opt->{mode} eq "clean") { | |
info(); | |
info_objects(); | |
#$DB->{'AutoCommit'} = 0; # TODO: remove! | |
if($opt->{what} eq "all") { | |
clean_status(); | |
clean_config(); | |
clean_history(); | |
clean_instance(); | |
} | |
elsif($opt->{what} eq "status") { | |
clean_status(); | |
} | |
elsif($opt->{what} eq "config") { | |
clean_config(); | |
} | |
elsif($opt->{what} eq "history") { | |
clean_history(); | |
} | |
else { | |
handleerror("what: '".$opt->{what}."' not implemented!"); | |
} | |
#$DB->rollback(); #TODO: remove | |
} | |
else { | |
handleerror("mode '".$opt->{mode}."' is not implemented!"); | |
} | |
# }}} | |
# SUBS {{{1 | |
# handle a error {{{2 | |
sub handleerror { | |
my $error = shift; | |
print "$error\n\n"; | |
pod2usage(1); | |
exit(1); | |
} | |
# }}} | |
# connect to database {{{2 | |
sub connect_db { | |
if(!$DB) { | |
$DB = DBI->connect($db_dsn, $db_user, $db_pass) or die "Unable to connect: $DBI::errstr\n"; | |
} | |
} | |
# }}} | |
# list instances {{{2 | |
sub list_instances { | |
my ($query, $result, $handle); | |
$query = "SELECT instance_id, instance_name, instance_description FROM ${prefix}instances"; | |
$handle = $DB->prepare($query) or handleerror($DBI::errstr); | |
my ($id, $name, $description); | |
if($handle->execute()) { | |
$handle->bind_columns(\$id, \$name, \$description); | |
print "Instances in the database:\n\n"; | |
printf(" | %5s | %-15s | %-25s |\n", "id", "name", "description"); | |
print (" |-------|-----------------|---------------------------|\n"); | |
while($handle->fetch()) { | |
printf(" | %5s | %-15s | %-25s |\n", $id, $name, $description); | |
} | |
print "\n"; | |
} | |
else { | |
handleerror("error querying instance data!"); | |
} | |
} | |
# }}} | |
# basic instance infos {{{2 | |
sub info { | |
my ($query, $result, $handle, $byid); | |
# get instance_id | |
if ($opt->{instance} =~ m/^\d+$/) { | |
print "Instance: id=0 - running kind of blind\n\n"; | |
$instance_id = $opt->{instance}; | |
return; | |
} | |
$query = "SELECT instance_id, instance_description FROM ${prefix}instances WHERE instance_name = ".$DB->quote($opt->{instance}); | |
$handle = $DB->prepare($query) or handleerror($DBI::errstr); | |
my $description; | |
my $instance_name; | |
if($handle->execute()) { | |
$handle->bind_columns(\$instance_id, \$description); | |
$handle->fetch(); | |
if(!$instance_id) { | |
handleerror("instance '$opt->{instance}' not found!"); | |
} | |
else { | |
print "Instance: $opt->{instance}"; | |
print " ($description)" if $description; | |
print " (id=$instance_id)\n\n"; | |
} | |
} | |
else { | |
handleerror("error querying instance data!"); | |
} | |
} | |
# }}} | |
# get object information {{{2 | |
sub info_objects { | |
print "Objects:\n\n"; | |
my ($query, $table, $handle, $count); | |
# hosts - all, active | |
my ($hosts, $hosts_active); | |
$query = "SELECT count(object_id), sum(is_active) FROM ${prefix}objects WHERE instance_id = ".$DB->quote($instance_id)." and objecttype_id = 1"; | |
$handle = $DB->prepare($query) or handleerror($DBI::errstr); | |
if($handle->execute()) { | |
$handle->bind_columns(\$hosts, \$hosts_active); | |
$handle->fetch(); | |
printf(" %10s: %s (+%s inactive)\n", "hosts", $hosts_active, $hosts - $hosts_active); | |
} | |
else { | |
handleerror("db error: $DBI::errstr"); | |
} | |
# services - all, active | |
my ($services, $services_active); | |
$query = "SELECT count(object_id), sum(is_active) FROM ${prefix}objects WHERE instance_id = ".$DB->quote($instance_id)." and objecttype_id = 2"; | |
$handle = $DB->prepare($query) or handleerror($DBI::errstr); | |
if($handle->execute()) { | |
$handle->bind_columns(\$services, \$services_active); | |
$handle->fetch(); | |
printf(" %10s: %s (+%s inactive)\n", "services", $services_active, $services - $services_active); | |
} | |
else { | |
handleerror("db error: $DBI::errstr"); | |
} | |
# other - all, active | |
my ($others, $others_active); | |
$query = "SELECT count(object_id), sum(is_active) FROM ${prefix}objects WHERE instance_id = ".$DB->quote($instance_id)." and objecttype_id != 1 and objecttype_id != 2"; | |
$handle = $DB->prepare($query) or handleerror($DBI::errstr); | |
if($handle->execute()) { | |
$handle->bind_columns(\$others, \$others_active); | |
$handle->fetch(); | |
printf(" %10s: %s (+%s inactive)\n", "others", $others_active, $others - $others_active); | |
} | |
else { | |
handleerror("db error: $DBI::errstr"); | |
} | |
print "\n"; | |
} | |
# }}} | |
# get config information {{{2 | |
sub info_config { | |
print "Config tables:\n\n"; | |
my ($query, $table, $handle, $count); | |
foreach(@tables_config) { | |
$table = $prefix.$_; | |
$query = "SELECT count(*) FROM $table WHERE instance_id = ".$DB->quote($instance_id); | |
$handle = $DB->prepare($query) or handleerror($DBI::errstr); | |
if($handle->execute()) { | |
$handle->bind_columns(\$count); | |
$handle->fetch(); | |
printf(" %32s: %s\n",$_, $count); | |
} | |
else { | |
handleerror("db error: $DBI::errstr"); | |
} | |
} | |
print "\n"; | |
} | |
# }}} | |
# get status information {{{2 | |
sub info_status { | |
print "Status tables:\n\n"; | |
my ($query, $table, $handle, $count); | |
foreach(@tables_status) { | |
$table = $prefix.$_; | |
$query = "SELECT count(*) FROM $table WHERE instance_id = ".$DB->quote($instance_id); | |
$handle = $DB->prepare($query) or handleerror($DBI::errstr); | |
if($handle->execute()) { | |
$handle->bind_columns(\$count); | |
$handle->fetch(); | |
printf(" %32s: %s\n",$_, $count); | |
} | |
else { | |
handleerror("db error: $DBI::errstr"); | |
} | |
} | |
print "\n"; | |
} | |
# }}} | |
# get history information {{{2 | |
sub info_history { | |
print "Historic tables:\n\n"; | |
my ($query, $table, $handle, $count); | |
foreach(@tables_history) { | |
$table = $prefix.$_; | |
$query = "SELECT count(*) FROM $table WHERE instance_id = ".$DB->quote($instance_id); | |
$handle = $DB->prepare($query) or handleerror($DBI::errstr); | |
if($handle->execute()) { | |
$handle->bind_columns(\$count); | |
$handle->fetch(); | |
printf(" %32s: %s\n",$_, $count); | |
} | |
else { | |
handleerror("db error: $DBI::errstr"); | |
} | |
} | |
print "\n"; | |
} | |
# }}} | |
## clean status information {{{2 | |
sub clean_status { | |
print "Cleaning status tables:\n"; | |
my ($query, $table, $handle); | |
foreach(@tables_status) { | |
my $count = 0; | |
my $affected = 0; | |
my $iterations = 0; | |
$table = $prefix.$_; | |
$query = "DELETE FROM $table WHERE instance_id = ".$DB->quote($instance_id)." LIMIT $db_limit"; | |
do { | |
$count = $DB->do($query) or handleerror($DBI::errstr); | |
if($count > 0) { | |
$affected += $count; | |
$iterations++; | |
} | |
} until ($count == 0); | |
printf(" %32s: %8d (%s iterations)\n",$_, $affected, $iterations); | |
} | |
print "\n"; | |
} | |
# }}} | |
## clean config information {{{2 | |
sub clean_config { | |
print "Cleaning config tables:\n"; | |
my ($query, $table, $handle); | |
foreach(@tables_config) { | |
my $count = 0; | |
my $affected = 0; | |
my $iterations = 0; | |
$table = $prefix.$_; | |
$query = "DELETE FROM $table WHERE instance_id = ".$DB->quote($instance_id)." LIMIT $db_limit"; | |
do { | |
$count = $DB->do($query) or handleerror($DBI::errstr); | |
if($count > 0) { | |
$affected += $count; | |
$iterations++; | |
} | |
} until ($count == 0); | |
printf(" %32s: %8d (%s iterations)\n",$_, $affected, $iterations); | |
} | |
print "\n"; | |
} | |
# }}} | |
## clean history information {{{2 | |
sub clean_history { | |
print "Cleaning history tables:\n"; | |
my ($query, $table, $handle); | |
foreach(@tables_history) { | |
my $count = 0; | |
my $affected = 0; | |
my $iterations = 0; | |
$table = $prefix.$_; | |
$query = "DELETE FROM $table WHERE instance_id = ".$DB->quote($instance_id)." LIMIT $db_limit"; | |
do { | |
$count = $DB->do($query) or handleerror($DBI::errstr); | |
if($count > 0) { | |
$affected += $count; | |
$iterations++; | |
} | |
} until ($count == 0); | |
printf(" %32s: %8d (%s iterations)\n",$_, $affected, $iterations); | |
} | |
print "\n"; | |
} | |
# }}} | |
## clean instance basic information {{{2 | |
sub clean_instance { | |
print "Cleaning instance tables:\n"; | |
my ($query, $table, $handle); | |
# removing data from objects | |
my $count = 0; | |
my $affected = 0; | |
my $iterations = 0; | |
$table = $prefix."objects"; | |
$query = "DELETE FROM $table WHERE instance_id = ".$DB->quote($instance_id)." LIMIT $db_limit"; | |
do { | |
$count = $DB->do($query) or handleerror($DBI::errstr); | |
if($count > 0) { | |
$affected += $count; | |
$iterations++; | |
} | |
} until ($count == 0); | |
printf(" %32s: %8d (%s iterations)\n", "objects", $affected, $iterations); | |
# removing the instance itself | |
$table = $prefix."instances"; | |
$query = "DELETE FROM $table WHERE instance_id = ".$DB->quote($instance_id); | |
$count = $DB->do($query) or handleerror($DBI::errstr); | |
print "\n"; | |
if($count > 0) { | |
print " instance '".$opt->{instance}."' has been wiped from the database!\n"; | |
} | |
else { | |
handleerror("instance not found in table!"); | |
} | |
print "\n"; | |
} | |
# }}} | |
# END SUBS }}} | |
# vi: expandtab ts=4 sw=4 fdm=marker: |
Alright, so there won't be any accidental deletions unless I enter something wrong, right?
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
It lists a summary of data inside the Icinga IDO, and can remove all data of an instance, or a certain part of the IDO.