IDO tool to cleanup the database of Icinga IDOutils

idotool.pl

#!/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:

Just curious: Does this still work with current releases and what does it do exactly?

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.

Alright, so there won't be any accidental deletions unless I enter something wrong, right?