"Fossies" - the Fresh Open Source Software Archive

Member "pandora_server/util/pandora_db.pl" (5 Nov 2021, 51071 Bytes) of package /linux/misc/pandorafms_server-7.0NG.758.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) Perl source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file. For more information about "pandora_db.pl" see the Fossies "Dox" file reference documentation and the latest Fossies "Diffs" side-by-side code changes report: 7.0NG.757_vs_7.0NG.758.

    1 #!/usr/bin/perl
    2 
    3 ###############################################################################
    4 # Pandora FMS DB Management
    5 ###############################################################################
    6 # Copyright (c) 2005-2021 Artica Soluciones Tecnologicas S.L
    7 #
    8 # This program is free software; you can redistribute it and/or
    9 # modify it under the terms of the GNU General Public License
   10 # as published by the Free Software Foundation;  version 2
   11 # This program is distributed in the hope that it will be useful,
   12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
   13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   14 # GNU General Public License for more details.
   15 # You should have received a copy of the GNU General Public License
   16 # along with this program; if not, write to the Free Software
   17 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301,USA
   18 ###############################################################################
   19 
   20 # Includes list
   21 use strict;
   22 use warnings;
   23 use Time::Local;        # DateTime basic manipulation
   24 use DBI;                # DB interface with MySQL
   25 use POSIX qw(strftime);
   26 use File::Path qw(rmtree);
   27 use Time::HiRes qw(usleep);
   28 
   29 # Default lib dir for RPM and DEB packages
   30 use lib '/usr/lib/perl5';
   31 
   32 use PandoraFMS::Core;
   33 use PandoraFMS::Tools;
   34 use PandoraFMS::Config;
   35 use PandoraFMS::DB;
   36 
   37 # version: define current version
   38 my $version = "7.0NG.758 Build 211105";
   39 
   40 # Pandora server configuration
   41 my %conf;
   42 
   43 # Long operations are divided in XX steps for performance
   44 my $BIG_OPERATION_STEP = 100;   # 100 is default
   45 
   46 # Each long operations has a LIMIT of SMALL_OPERATION_STEP to avoid locks. 
   47 #Increate to 3000~5000 in fast systems decrease to 500 or 250 on systems with locks
   48 my $SMALL_OPERATION_STEP = 1000;    # 1000 is default
   49 
   50 # FLUSH in each IO 
   51 $| = 1;
   52 
   53 ########################################################################
   54 # Print the given message with a preceding timestamp.
   55 ########################################################################
   56 sub log_message ($$;$) {
   57     my ($source, $message, $eol) = @_;
   58     
   59     # Set a default end of line
   60     $eol = "\n" unless defined ($eol);
   61     
   62     if ($source eq '') {
   63         print $message;
   64     }
   65     else {
   66         print strftime("%H:%M:%S", localtime()) . ' [' . $source . '] ' . $message . $eol;
   67     }
   68 }
   69 
   70 ########################################################################
   71 # Delete old data from the database.
   72 ########################################################################
   73 sub pandora_purgedb ($$) {
   74     my ($conf, $dbh) = @_;
   75     
   76     # 1) Obtain last value for date limit
   77     # 2) Delete all elements below date limit
   78     # 3) Insert last value in date_limit position
   79     
   80     # Calculate limit for deletion, today - $conf->{'_days_purge'}
   81     
   82     my $timestamp = strftime ("%Y-%m-%d %H:%M:%S", localtime());
   83     my $ulimit_access_timestamp = time() - 86400;
   84     my $ulimit_timestamp = time() - (86400 * $conf->{'_days_purge'});
   85     my $first_mark;
   86     my $total_time;
   87     my $purge_steps;
   88     my $purge_count;
   89     
   90     # Delete extended session data
   91     if (enterprise_load (\%conf) != 0) {
   92         db_do ($dbh, "DELETE FROM tsesion_extended
   93             WHERE id_sesion NOT IN ( SELECT id_sesion FROM tsesion )");
   94         log_message ('PURGE', 'Deleting old extended session data.');
   95     }
   96 
   97     # Delete old inventory data
   98     if (defined ($conf->{'_inventory_purge'}) && $conf->{'_inventory_purge'} > 0) {
   99         if (enterprise_load (\%conf) != 0) {
  100             my $ulimit_timestamp_inventory = time() - (86400 * $conf->{'_inventory_purge'});
  101 
  102             log_message ('PURGE', 'Deleting old inventory data.');
  103 
  104             # This could be very timing consuming, so make 
  105             # this operation in $BIG_OPERATION_STEP 
  106             # steps (100 fixed by default)
  107             # Starting from the oldest record on the table
  108 
  109             $first_mark =  get_db_value_limit ($dbh, 'SELECT utimestamp FROM tagente_datos_inventory ORDER BY utimestamp ASC', 1);
  110             if (defined ($first_mark)) {
  111                 $total_time = $ulimit_timestamp_inventory - $first_mark;
  112                 $purge_steps = int($total_time / $BIG_OPERATION_STEP);
  113                 if ($purge_steps > 0) {
  114                     for (my $ax = 1; $ax <= $BIG_OPERATION_STEP; $ax++) {
  115                         db_do ($dbh, "DELETE FROM tagente_datos_inventory WHERE utimestamp < ". ($first_mark + ($purge_steps * $ax)) . " AND utimestamp >= ". $first_mark );
  116                         log_message ('PURGE', "Inventory data deletion Progress %$ax\r");
  117                         # Do a nanosleep here for 0,01 sec
  118                         usleep (10000);
  119                     }
  120                     log_message ('', "\n");
  121                 } else {
  122                     log_message ('PURGE', 'No data to purge in tagente_datos_inventory.');
  123                 }
  124             } else {
  125                 log_message ('PURGE', 'No data in tagente_datos_inventory.');
  126             }
  127         }
  128     }
  129         
  130     # Delete old data
  131     if ($conf->{'_days_purge'} > 0) {
  132 
  133         # Delete old numeric data
  134         pandora_delete_old_module_data ($dbh, 'tagente_datos', $ulimit_access_timestamp, $ulimit_timestamp);
  135 
  136         # Delete old export data
  137         pandora_delete_old_export_data ($dbh, $ulimit_timestamp);
  138         
  139         # Delete sessions data
  140         pandora_delete_old_session_data (\%conf, $dbh, $ulimit_timestamp);
  141     
  142         # Delete old inventory data
  143     }
  144     else {
  145         log_message ('PURGE', 'days_purge is set to 0. Old data will not be deleted.');
  146     }
  147 
  148     # String data deletion
  149     if (!defined($conf->{'_string_purge'})){
  150         $conf->{'_string_purge'} = 7;
  151     }
  152 
  153     if ($conf->{'_string_purge'} > 0) {
  154         $ulimit_access_timestamp = time() - 86400;
  155         $ulimit_timestamp = time() - (86400 * $conf->{'_string_purge'});
  156         pandora_delete_old_module_data ($dbh, 'tagente_datos_string', $ulimit_access_timestamp, $ulimit_timestamp);
  157     }
  158     else {
  159         log_message ('PURGE', 'string_purge is set to 0. Old string data will not be deleted.');
  160     }
  161 
  162     # Delete event data
  163     if (!defined($conf->{'_event_purge'})){
  164         $conf->{'_event_purge'}= 10;
  165     }
  166     if ($conf->{'_event_purge'} > 0) {
  167         my $event_limit = time() - 86400 * $conf->{'_event_purge'};
  168         my $events_table = 'tevento';
  169         
  170         # If is installed enterprise version and enabled metaconsole, 
  171         # check the events history copy and set the name of the metaconsole events table
  172         if (defined($conf->{'_enterprise_installed'}) && $conf->{'_enterprise_installed'} eq '1' &&
  173             defined($conf->{'_metaconsole'}) && $conf->{'_metaconsole'} eq '1'){
  174         
  175             # If events history is enabled, save the new events (not validated or in process) to history database
  176             if(defined($conf->{'_metaconsole_events_history'}) && $conf->{'_metaconsole_events_history'} eq '1') {
  177                 log_message ('PURGE', "Moving old not validated events to history table (More than " . $conf->{'_event_purge'} . " days).");
  178 
  179                 my @events = get_db_rows ($dbh, 'SELECT * FROM tmetaconsole_event WHERE estado = 0 AND utimestamp < ?', $event_limit);
  180                 foreach my $event (@events) {
  181                     db_process_insert($dbh, 'id_evento', 'tmetaconsole_event_history', $event);
  182                     db_do($dbh, "DELETE FROM tmetaconsole_event WHERE id_evento =".$event->{'id_evento'});
  183                 }
  184             }
  185             
  186             $events_table = 'tmetaconsole_event';
  187         }
  188         
  189         log_message ('PURGE', "Deleting old event data at $events_table table (More than " . $conf->{'_event_purge'} . " days).", '');
  190 
  191         # Delete with buffer to avoid problems with performance
  192         my $events_to_delete = get_db_value ($dbh, "SELECT COUNT(*) FROM $events_table WHERE utimestamp < ?", $event_limit);
  193         while($events_to_delete > 0) {
  194             db_delete_limit($dbh, $events_table, "utimestamp < ?", $BIG_OPERATION_STEP, $event_limit);
  195             $events_to_delete = $events_to_delete - $BIG_OPERATION_STEP;
  196             
  197             # Mark the progress
  198             log_message ('', ".");
  199             
  200             # Do not overload the MySQL server
  201             usleep (10000);
  202         }
  203         log_message ('', "\n");
  204 
  205         if (defined($conf->{'_enterprise_installed'}) && $conf->{'_enterprise_installed'} eq '1' &&
  206             defined($conf->{'_metaconsole'}) && $conf->{'_metaconsole'} eq '1'){
  207             log_message ('PURGE', "Deleting validated events from tmetaconsole_event_history.", '');
  208             $events_to_delete = get_db_value ($dbh, "SELECT COUNT(*) FROM tmetaconsole_event_history WHERE estado = 1");
  209             while($events_to_delete > 0) {
  210                 db_delete_limit($dbh, 'tmetaconsole_event_history',  'estado = 1', $BIG_OPERATION_STEP);
  211                 $events_to_delete = $events_to_delete - $BIG_OPERATION_STEP;
  212             
  213                 # Mark the progress
  214                 log_message ('', ".");
  215             
  216                 # Do not overload the MySQL server
  217                 usleep (10000);
  218             }
  219             log_message ('', "\n");
  220         }
  221     }
  222     else {
  223         log_message ('PURGE', 'event_purge is set to 0. Old events will not be deleted.');
  224     }
  225 
  226     # Delete audit data
  227     $conf->{'_audit_purge'}= 7 if (!defined($conf->{'_audit_purge'}));
  228     if ($conf->{'_audit_purge'} > 0) {
  229         log_message ('PURGE', "Deleting old audit data (More than " . $conf->{'_audit_purge'} . " days).");
  230         my $audit_limit = time() - 86400 * $conf->{'_audit_purge'};
  231         db_do($dbh, "DELETE FROM tsesion WHERE utimestamp < $audit_limit");
  232     }
  233     else {
  234         log_message ('PURGE', 'audit_purge is set to 0. Old audit data will not be deleted.');
  235     }
  236 
  237     # Delete SNMP trap data
  238     $conf->{'_trap_purge'}= 7 if (!defined($conf->{'_trap_purge'}));
  239     if ($conf->{'_trap_purge'} > 0) {
  240         log_message ('PURGE', "Deleting old SNMP traps (More than " . $conf->{'_trap_purge'} . " days).");
  241 
  242         my $trap_limit = strftime ("%Y-%m-%d %H:%M:%S", localtime(time() - 86400 * $conf->{'_trap_purge'}));
  243         db_do($dbh, "DELETE FROM ttrap WHERE timestamp < '$trap_limit'");
  244     }
  245     else {
  246         log_message ('PURGE', 'trap_purge is set to 0. Old SNMP traps will not be deleted.');
  247     }
  248     
  249     # Delete policy queue data
  250     enterprise_hook("pandora_purge_policy_queue", [$dbh, $conf]);
  251 
  252     # Delete policy queue data
  253     enterprise_hook("pandora_purge_service_elements", [$dbh, $conf]);
  254 
  255     # Delete GIS  data
  256     $conf->{'_gis_purge'}= 15 if (!defined($conf->{'_gis_purge'}));
  257     if ($conf->{'_gis_purge'} > 0) {
  258         log_message ('PURGE', "Deleting old GIS data (More than " . $conf->{'_gis_purge'} . " days).");
  259         my $gis_limit = strftime ("%Y-%m-%d %H:%M:%S", localtime(time() - 86400 * $conf->{'_gis_purge'}));
  260         db_do($dbh, "DELETE FROM tgis_data_history WHERE end_timestamp < '$gis_limit'");
  261     }
  262     else {
  263         log_message ('PURGE', 'gis_purge is set to 0. Old GIS data will not be deleted.');
  264     }
  265 
  266     # Delete pending modules
  267     log_message ('PURGE', "Deleting pending delete modules (data table).", '');
  268     my @deleted_modules = get_db_rows ($dbh, 'SELECT id_agente_modulo FROM tagente_modulo WHERE delete_pending = 1');
  269     foreach my $module (@deleted_modules) {
  270         
  271         my $buffer = 1000;
  272         my $id_module = $module->{'id_agente_modulo'};
  273 
  274         db_do ($dbh, 'UPDATE tagente_modulo SET parent_module_id=0 WHERE parent_module_id=?', $id_module);
  275 
  276         log_message ('', ".");
  277         
  278         while(1) {
  279             my $nstate = get_db_value ($dbh, 'SELECT count(id_agente_modulo) FROM tagente_estado WHERE id_agente_modulo=?', $id_module);            
  280             last if($nstate == 0);
  281             
  282             db_delete_limit ($dbh, 'tagente_estado', 'id_agente_modulo=?', $buffer, $id_module);
  283         }
  284     }
  285     log_message ('', "\n");
  286 
  287     log_message ('PURGE', "Deleting pending delete modules (status, module table).");
  288     db_do ($dbh, "DELETE FROM tagente_estado WHERE id_agente_modulo IN (SELECT id_agente_modulo FROM tagente_modulo WHERE delete_pending = 1)");
  289     db_do ($dbh, "DELETE FROM tagente_modulo WHERE delete_pending = 1");
  290 
  291     log_message ('PURGE', "Deleting old access data (More than 24hr)");
  292 
  293     $first_mark =  get_db_value_limit ($dbh, 'SELECT utimestamp FROM tagent_access ORDER BY utimestamp ASC', 1);
  294     if (defined ($first_mark)) {
  295         $total_time = $ulimit_access_timestamp - $first_mark;
  296         $purge_steps = int( $total_time / $BIG_OPERATION_STEP);
  297         if ($purge_steps > 0) {
  298             for (my $ax = 1; $ax <= $BIG_OPERATION_STEP; $ax++){ 
  299                 db_do ($dbh, "DELETE FROM tagent_access WHERE utimestamp < ". ( $first_mark + ($purge_steps * $ax)) . " AND utimestamp >= ". $first_mark);
  300                 log_message ('PURGE', "Agent access deletion progress %$ax", "\r");
  301                 # Do a nanosleep here for 0,01 sec
  302                 usleep (10000);
  303             }
  304             log_message ('', "\n");
  305         } else {
  306             log_message ('PURGE', "No agent access data to purge.");
  307         }
  308     } else {
  309         log_message ('PURGE', "No agent access data.");
  310     }
  311     
  312     
  313     
  314     # Purge the reports
  315     if (defined($conf->{'_enterprise_installed'}) && $conf->{'_enterprise_installed'} eq '1' &&
  316         defined($conf->{'_metaconsole'}) && $conf->{'_metaconsole'} eq '1'){
  317         log_message ('PURGE', "Metaconsole enabled, ignoring reports.");
  318     } else {
  319         my @blacklist_types = ("'SLA_services'", "'custom_graph'", "'sql_graph_vbar'", "'sql_graph_hbar'",
  320             "'sql_graph_pie'", "'database_serialized'", "'sql'", "'inventory'", "'inventory_changes'",
  321             "'netflow_area'", "'netflow_data'", "'netflow_summary'");
  322         my $blacklist_types_str = join(',', @blacklist_types);
  323         
  324         # Deleted modules
  325         log_message ('PURGE', "Delete contents in report that have some deleted modules.");
  326         db_do ($dbh, "DELETE FROM treport_content
  327                       WHERE id_agent_module != 0
  328                         AND id_agent_module NOT IN (SELECT id_agente_modulo FROM tagente_modulo)
  329                         AND ${RDBMS_QUOTE}type${RDBMS_QUOTE} NOT IN ($blacklist_types_str)");
  330         db_do ($dbh, "DELETE FROM treport_content_item
  331                       WHERE id_agent_module != 0
  332                         AND id_agent_module NOT IN (SELECT id_agente_modulo FROM tagente_modulo)
  333                         AND id_report_content NOT IN (SELECT id_rc FROM treport_content WHERE ${RDBMS_QUOTE}type${RDBMS_QUOTE} IN ($blacklist_types_str))");
  334         db_do ($dbh, "DELETE FROM treport_content_sla_combined
  335                       WHERE id_agent_module != 0
  336                         AND id_agent_module NOT IN (SELECT id_agente_modulo FROM tagente_modulo)
  337                         AND id_report_content NOT IN (SELECT id_rc FROM treport_content WHERE ${RDBMS_QUOTE}type${RDBMS_QUOTE} = 'SLA_services')");
  338         
  339         # Deleted agents
  340         log_message ('PURGE', "Delete contents in report that have some deleted agents.");
  341         db_do ($dbh, "DELETE FROM treport_content
  342                       WHERE id_agent != 0
  343                         AND id_agent NOT IN (SELECT id_agente FROM tagente)
  344                         AND ${RDBMS_QUOTE}type${RDBMS_QUOTE} NOT IN ($blacklist_types_str)");
  345         
  346         # Empty contents
  347         log_message ('PURGE', "Delete empty contents in report (like SLA or Exception).");
  348         db_do ($dbh, "DELETE FROM treport_content
  349                       WHERE ${RDBMS_QUOTE}type${RDBMS_QUOTE} LIKE 'exception'
  350                         AND id_rc NOT IN (SELECT id_report_content FROM treport_content_item)");
  351         db_do ($dbh, "DELETE FROM treport_content
  352                       WHERE ${RDBMS_QUOTE}type${RDBMS_QUOTE} IN ('SLA', 'SLA_monthly', 'SLA_services')
  353                         AND id_rc NOT IN (SELECT id_report_content FROM treport_content_sla_combined)");
  354     }
  355     
  356     
  357     # Delete disabled autodisable agents after some period
  358     log_message ('PURGE', 'Delete autodisabled agents where last contact is bigger than ' . $conf->{'_days_autodisable_deletion'} . ' days.');
  359     db_do ($dbh, "DELETE FROM tagente 
  360                   WHERE UNIX_TIMESTAMP(ultimo_contacto) + ? < UNIX_TIMESTAMP(NOW())
  361                    AND disabled=1
  362                    AND modo=2", $conf->{'_days_autodisable_deletion'}*8600);
  363     
  364     
  365     # Delete old netflow data
  366     if ($conf->{'_netflow_max_lifetime'} > 0) {
  367         log_message ('PURGE', "Deleting old netflow data.");
  368         if (! defined ($conf->{'_netflow_path'}) || ! -d $conf->{'_netflow_path'}) {
  369             log_message ('!', "Netflow data directory does not exist, skipping.");
  370         }
  371         elsif (! -x $conf->{'_netflow_nfexpire'}) {
  372             log_message ('!', "Cannot execute " . $conf->{'_netflow_nfexpire'} . ", skipping.");
  373         }
  374         else {
  375             `yes 2>/dev/null | $conf->{'_netflow_nfexpire'} -e "$conf->{'_netflow_path'}" -t $conf->{'_netflow_max_lifetime'}d`;
  376         }
  377     }
  378     else {
  379         log_message ('PURGE', 'netflow_max_lifetime is set to 0. Old netflow data will not be deleted.');
  380     }
  381     
  382     # Delete old log data
  383     log_message ('PURGE', "Deleting old log data.");
  384     if (defined($conf->{'_days_purge_old_information'}) && $conf->{'_days_purge_old_information'} > 0) {
  385         log_message ('PURGE', 'Deleting log data older than ' . $conf->{'_days_purge_old_information'} . ' days.');
  386     enterprise_hook ('pandora_purge_logs', [$dbh, $conf]);
  387     }
  388     else {
  389         log_message ('PURGE', 'days_purge_old_data is set to 0. Old log data will not be deleted.');
  390     }
  391 
  392     # Delete old log data
  393     log_message ('PURGE', "Deleting old network configuration manager data.");
  394     if (defined($conf->{'days_purge_ncm'}) && $conf->{'days_purge_ncm'} > 0) {
  395         log_message ('PURGE', 'Deleting NCM data older than ' . $conf->{'days_purge_ncm'} . ' days.');
  396     enterprise_hook ('pandora_purge_ncm', [$dbh, \&log_message, $conf->{'days_purge_ncm'}, $conf->{'_history_db_step'}, $conf->{'_history_db_delay'}]);
  397     } else {
  398         log_message ('PURGE', 'days_purge_ncm is set to 0. Old network configuration manager data will not be deleted.');
  399     }
  400 
  401     # Delete old special days
  402     log_message ('PURGE', "Deleting old special days.");
  403     if ($conf->{'_num_past_special_days'} > 0) {
  404         log_message ('PURGE', 'Deleting special days older than ' . $conf->{'_num_past_special_days'} . ' days.');
  405         if (${RDBMS} eq 'oracle') {
  406             db_do ($dbh, "DELETE FROM talert_special_days
  407                 WHERE \"date\" < SYSDATE - $conf->{'_num_past_special_days'} AND \"date\" > '0001-01-01'");
  408         }
  409         elsif (${RDBMS} eq 'mysql') { 
  410             db_do ($dbh, "DELETE FROM talert_special_days
  411                 WHERE date < CURDATE() - $conf->{'_num_past_special_days'} AND date > '0001-01-01'");
  412         }
  413     }
  414 
  415     # Delete old tgraph_source data
  416     log_message ('PURGE', 'Deleting old tgraph_source data.');
  417     db_do ($dbh,"DELETE FROM tgraph_source WHERE id_graph NOT IN (SELECT id_graph FROM tgraph)");
  418 
  419 
  420     # Delete network traffic old data.
  421     log_message ('PURGE', 'Deleting old network matrix data.');
  422     if ($conf->{'_delete_old_network_matrix'} > 0) {
  423         my $matrix_limit = time() - 86400 * $conf->{'_delete_old_network_matrix'};
  424         db_do ($dbh, "DELETE FROM tnetwork_matrix WHERE utimestamp < ?", $matrix_limit);
  425     }
  426 
  427     # Delete old messages
  428     log_message ('PURGE', "Deleting old messages.");
  429     if ($conf->{'_delete_old_messages'} > 0) {
  430         my $message_limit = time() - 86400 * $conf->{'_delete_old_messages'};
  431         db_do ($dbh, "DELETE FROM tmensajes WHERE timestamp < ?", $message_limit);
  432     }
  433 
  434     # Delete old cache data
  435     log_message ('PURGE', "Deleting old cache data.");
  436     db_do ($dbh, "DELETE FROM `tvisual_console_elements_cache` WHERE (UNIX_TIMESTAMP(`created_at`) + `expiration`) < UNIX_TIMESTAMP()");
  437 }
  438 
  439 ###############################################################################
  440 # Compact agent data.
  441 ###############################################################################
  442 sub pandora_compactdb ($$$) {
  443     my ($conf, $dbh, $dbh_conf) = @_;
  444 
  445     my %count_hash;
  446     my %id_agent_hash;
  447     my %value_hash;
  448     my %module_proc_hash;
  449     
  450     return if ($conf->{'_days_compact'} == 0 || $conf->{'_step_compact'} < 1);
  451     
  452     # Convert compact interval length from hours to seconds
  453     my $step = $conf->{'_step_compact'} * 3600;
  454 
  455     # The oldest timestamp will be the lower limit
  456     my $limit_utime = get_db_value ($dbh, 'SELECT min(utimestamp) as min FROM tagente_datos');
  457     return unless (defined ($limit_utime) && $limit_utime > 0);
  458 
  459     # Calculate the start date
  460     my $start_utime = time() - $conf->{'_days_compact'} * 24 * 60 * 60;
  461     my $last_compact = $start_utime;
  462     my $stop_utime;
  463 
  464     # Do not compact the same data twice!
  465     if (defined ($conf->{'_last_compact'}) && $conf->{'_last_compact'} > $limit_utime) {
  466         $limit_utime  = $conf->{'_last_compact'};
  467     }
  468     
  469     if ($start_utime <= $limit_utime || ( defined ($conf->{'_last_compact'}) && (($conf->{'_last_compact'} + 24 * 60 * 60) > $start_utime))) {
  470         log_message ('COMPACT', "Data already compacted.");
  471         return;
  472     }
  473     
  474     log_message ('COMPACT', "Compacting data from " . strftime ("%Y-%m-%d %H:%M:%S", localtime($limit_utime)) . " to " . strftime ("%Y-%m-%d %H:%M:%S", localtime($start_utime)) . '.', '');
  475 
  476     # Prepare the query to retrieve data from an interval
  477     while (1) {
  478 
  479             # Calculate the stop date for the interval
  480             $stop_utime = $start_utime - $step;
  481 
  482             # Out of limits
  483             last if ($start_utime < $limit_utime);
  484 
  485             # Mark the progress
  486             log_message ('', ".");
  487             
  488             my @data = get_db_rows ($dbh, 'SELECT * FROM tagente_datos WHERE utimestamp < ? AND utimestamp >= ?', $start_utime, $stop_utime);
  489             # No data, move to the next interval
  490             if ($#data == 0) {
  491                 $start_utime = $stop_utime;
  492                 next;
  493             }
  494 
  495             # Get interval data
  496             foreach my $data (@data) {
  497                 my $id_module = $data->{'id_agente_modulo'};
  498                 if (! defined($module_proc_hash{$id_module})) {
  499                     my $module_type = get_db_value ($dbh, 'SELECT id_tipo_modulo FROM tagente_modulo WHERE id_agente_modulo = ?', $id_module);
  500                     next unless defined ($module_type);
  501 
  502                     # Mark proc modules.
  503                     if ($module_type == 2 || $module_type == 6 || $module_type == 9 || $module_type == 18 || $module_type == 21 || $module_type == 31 || $module_type == 35 || $module_type == 100) {
  504                         $module_proc_hash{$id_module} = 1;
  505                     }
  506                     else {
  507                         $module_proc_hash{$id_module} = 0;
  508                     }
  509                 }
  510 
  511                 # Skip proc modules!
  512                 next if ($module_proc_hash{$id_module} == 1);
  513 
  514                 if (! defined($value_hash{$id_module})) {
  515                     $value_hash{$id_module} = 0;
  516                     $count_hash{$id_module} = 0;
  517 
  518                     if (! defined($id_agent_hash{$id_module})) {
  519                         $id_agent_hash{$id_module} = $data->{'id_agente'};
  520                     }
  521                 }
  522 
  523                 $value_hash{$id_module} += $data->{'datos'};
  524                 $count_hash{$id_module}++;
  525             }
  526 
  527             # Delete interval from the database
  528             db_do ($dbh, 'DELETE ad FROM tagente_datos ad
  529                 INNER JOIN tagente_modulo am ON ad.id_agente_modulo = am.id_agente_modulo AND am.id_tipo_modulo NOT IN (2,6,9,18,21,31,35,100)
  530                 WHERE ad.utimestamp < ? AND ad.utimestamp >= ?', $start_utime, $stop_utime);
  531 
  532             # Insert interval average value
  533             foreach my $key (keys(%value_hash)) {
  534                 $value_hash{$key} /= $count_hash{$key};
  535                 db_do ($dbh, 'INSERT INTO tagente_datos (id_agente_modulo, datos, utimestamp) VALUES (?, ?, ?)', $key, $value_hash{$key}, $stop_utime);
  536                 delete($value_hash{$key});
  537                 delete($count_hash{$key});
  538             }
  539 
  540             usleep (1000); # Very small usleep, just to don't burn the DB
  541             # Move to the next interval
  542             $start_utime = $stop_utime;
  543     }
  544     log_message ('', "\n");
  545 
  546     # Mark the last compact date
  547     if (defined ($conf->{'_last_compact'})) {
  548         db_do ($dbh_conf, 'UPDATE tconfig SET value=? WHERE token=?', $last_compact, 'last_compact');
  549     } else {
  550         db_do ($dbh_conf, 'INSERT INTO tconfig (value, token) VALUES (?, ?)', $last_compact, 'last_compact');
  551     }
  552 }
  553 
  554 ########################################################################
  555 # Check command line parameters.
  556 ########################################################################
  557 sub pandora_init_pdb ($) {
  558     my $conf = shift;
  559     
  560     log_message ('', "Pandora FMS DB Tool v$version\n\n");
  561     log_message ('', "This program is Free Software, licensed under the terms of GPL License v2\n");
  562     log_message ('', "You can download latest versions and documentation at official web\n\n");
  563     
  564     # Load config file from command line
  565     help_screen () if ($#ARGV < 0);
  566     
  567     $conf->{'_pandora_path'} = shift(@ARGV);
  568     $conf->{'_onlypurge'} = 0;
  569     $conf->{'_force'} = 0;
  570     
  571     # If there are valid parameters store it
  572     foreach my $param (@ARGV) { 
  573         # help!
  574         help_screen () if ($param =~ m/--*h\w*\z/i );
  575         if ($param =~ m/-p\z/i) {
  576             $conf->{'_onlypurge'} = 1;
  577         }
  578         elsif ($param =~ m/-v\z/i) {
  579             $conf->{'_verbose'} = 1;
  580         }
  581         elsif ($param =~ m/-q\z/i) {
  582             $conf->{'_quiet'} = 1;
  583         }
  584         elsif ($param =~ m/-d\z/i) {
  585             $conf->{'_debug'} = 1;
  586         }
  587         elsif ($param =~ m/-f\z/i) {
  588             $conf->{'_force'} = 1;
  589         }
  590     }
  591     
  592     help_screen () if ($conf->{'_pandora_path'} eq '');
  593 }
  594 
  595 ########################################################################
  596 # Prepares conf read from historical database settings.
  597 ########################################################################
  598 sub pandoradb_load_history_conf($) {
  599     my $dbh = shift;
  600 
  601     my @options = get_db_rows($dbh, 'SELECT * FROM `tconfig`');
  602 
  603     my %options = map 
  604     {
  605         '_' . $_->{'token'} => $_->{'value'}
  606     } @options;
  607 
  608     $options{'_days_autodisable_deletion'} = 0 unless defined ($options{'_days_autodisable_deletion'});
  609     $options{'_num_past_special_days'} = 0 unless defined($options{'_num_past_special_days'});
  610     $options{'_delete_old_network_matrix'} = 0 unless defined($options{'_delete_old_network_matrix'});
  611     $options{'_delete_old_messages'} = 0 unless defined($options{'_delete_old_messages'});
  612     $options{'_netflow_max_lifetime'} = 0 unless defined($options{'_netflow_max_lifetime'});
  613     $options{'claim_back_snmp_modules'} = 0 unless defined($options{'claim_back_snmp_modules'});
  614 
  615     return \%options;
  616 }
  617 
  618 ########################################################################
  619 # Read external configuration file.
  620 ########################################################################
  621 sub pandora_load_config_pdb ($) {
  622     my $conf = shift;
  623 
  624     # Read conf file
  625     open (CFG, '< ' . $conf->{'_pandora_path'}) or die ("[ERROR] Could not open configuration file: $!\n");
  626     while (my $line = <CFG>){
  627         next unless ($line =~ /^(\S+)\s+(.*)\s+$/);
  628         $conf->{$1} =  clean_blank($2);
  629     }
  630     close (CFG);
  631 
  632     # Check conf tokens
  633     foreach my $param ('dbuser', 'dbpass', 'dbname', 'dbhost', 'log_file') {
  634         die ("[ERROR] Bad config values. Make sure " . $conf->{'_pandora_path'} . " is a valid config file.\n\n") unless defined ($conf->{$param});
  635     }
  636     $conf->{'dbengine'} = 'mysql' unless defined ($conf->{'dbengine'});
  637     $conf->{'dbport'} = '3306' unless defined ($conf->{'dbport'});
  638     $conf->{'claim_back_snmp_modules'} = '1' unless defined ($conf->{'claim_back_snmp_modules'});
  639     $conf->{'verbosity'} = '3' unless defined ($conf->{'verbosity'});
  640 
  641     # Configure SSL.
  642     set_ssl_opts($conf);
  643 
  644     # Dynamic interval configuration.                                                                                                                             
  645     $conf->{"dynamic_constant"} = 0.10 unless defined($conf->{"dynamic_constant"});
  646     $conf->{"dynamic_warning"} = 0.10 unless defined($conf->{"dynamic_warning"});
  647     $conf->{"dynamic_updates"} = 5 unless defined($conf->{"dynamic_updates"});
  648 
  649     $conf->{'servername'} = $conf->{'servername'};
  650     $conf->{'servername'} = `hostname` unless defined ($conf->{'servername'});
  651     $conf->{"servername"} =~ s/\s//g;
  652 
  653     # workaround for name unconsistency (corresponding entry at pandora_server.conf is 'errorlog_file')
  654     $conf->{'errorlogfile'} = $conf->{'errorlog_file'};
  655     $conf->{'errorlogfile'} = "/var/log/pandora_server.error" unless defined ($conf->{'errorlogfile'});
  656 
  657     # Read additional tokens from the DB
  658     my $dbh = db_connect ($conf->{'dbengine'}, $conf->{'dbname'}, $conf->{'dbhost'}, $conf->{'dbport'}, $conf->{'dbuser'}, $conf->{'dbpass'});
  659 
  660     $conf->{'_event_purge'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'event_purge'");
  661     $conf->{'_trap_purge'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'trap_purge'");
  662     $conf->{'_audit_purge'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'audit_purge'");
  663     $conf->{'_string_purge'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'string_purge'");
  664     $conf->{'_gis_purge'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'gis_purge'");
  665 
  666     $conf->{'_days_purge'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'days_purge'");
  667     $conf->{'_days_compact'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'days_compact'");
  668     $conf->{'_last_compact'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'last_compact'");
  669     $conf->{'_step_compact'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'step_compact'");
  670     $conf->{'_history_db_enabled'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_db_enabled'");
  671     $conf->{'_history_event_enabled'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_event_enabled'");
  672     $conf->{'_history_db_host'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_db_host'");
  673     $conf->{'_history_db_port'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_db_port'");
  674     $conf->{'_history_db_name'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_db_name'");
  675     $conf->{'_history_db_user'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_db_user'");
  676     $conf->{'_history_db_pass'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_db_pass'");
  677     $conf->{'_history_db_days'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_db_days'");
  678     $conf->{'_history_event_days'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_event_days'");
  679     $conf->{'_history_db_step'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_db_step'");
  680     $conf->{'_history_db_delay'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'history_db_delay'");
  681     $conf->{'_days_delete_unknown'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'days_delete_unknown'");
  682     $conf->{'_inventory_purge'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'inventory_purge'");
  683     $conf->{'_delete_old_messages'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'delete_old_messages'");
  684     $conf->{'_delete_old_network_matrix'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'delete_old_network_matrix'");
  685     $conf->{'_enterprise_installed'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'enterprise_installed'");
  686     $conf->{'_metaconsole'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'metaconsole'");
  687     $conf->{'_metaconsole_events_history'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'metaconsole_events_history'");
  688     $conf->{'_netflow_max_lifetime'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'netflow_max_lifetime'");
  689     $conf->{'_netflow_nfexpire'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'netflow_nfexpire'");
  690     $conf->{'_netflow_path'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'netflow_path'");
  691     $conf->{'_delete_notinit'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'delete_notinit'");
  692     $conf->{'_session_timeout'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'session_timeout'");
  693 
  694     $conf->{'_big_operation_step_datos_purge'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'big_operation_step_datos_purge'");
  695     $conf->{'_small_operation_step_datos_purge'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'small_operation_step_datos_purge'");
  696     $conf->{'_days_autodisable_deletion'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'days_autodisable_deletion'");
  697     $conf->{'_days_purge_old_information'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'Days_purge_old_information'");
  698     $conf->{'_elasticsearch_ip'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'elasticsearch_ip'");
  699     $conf->{'_elasticsearch_port'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'elasticsearch_port'");
  700     $conf->{'_server_unique_identifier'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'server_unique_identifier'");
  701 
  702     $BIG_OPERATION_STEP = $conf->{'_big_operation_step_datos_purge'}
  703                     if ( $conf->{'_big_operation_step_datos_purge'} );
  704     $SMALL_OPERATION_STEP = $conf->{'_small_operation_step_datos_purge'}
  705                     if ( $conf->{'_small_operation_step_datos_purge'} );
  706 
  707     $conf->{'_num_past_special_days'} = get_db_value ($dbh, "SELECT value FROM tconfig WHERE token = 'num_past_special_days'");
  708     
  709     db_disconnect ($dbh);
  710 
  711     log_message ('', "DB Tool now initialized and running (PURGE=" . $conf->{'_days_purge'} . " days, COMPACT=$conf->{'_days_compact'} days, STEP=" . $conf->{'_step_compact'} . ") . \n\n");
  712 }
  713 
  714 
  715 ###############################################################################
  716 # Check database integrity
  717 ###############################################################################
  718 
  719 sub pandora_checkdb_integrity {
  720     my ($conf, $dbh) = @_;
  721 
  722     log_message ('INTEGRITY', "Cleaning up group stats.");
  723 
  724     # Delete all records on tgroup_stats
  725     db_do ($dbh, 'DELETE FROM tgroup_stat');
  726 
  727     
  728     #print "[INTEGRITY] Deleting non-used IP addresses \n";
  729     # DISABLED - Takes too much time and benefits of this are unclear..
  730     # Delete all non-used IP addresses from taddress
  731     #db_do ($dbh, 'DELETE FROM taddress WHERE id_a NOT IN (SELECT id_a FROM taddress_agent)');
  732 
  733     log_message ('INTEGRITY', "Deleting orphan alerts.");
  734 
  735     # Delete alerts assigned to inexistant modules
  736     db_do ($dbh, 'DELETE FROM talert_template_modules WHERE id_agent_module NOT IN (SELECT id_agente_modulo FROM tagente_modulo)');
  737 
  738     log_message ('INTEGRITY', "Deleting orphan modules.");
  739     
  740     # Delete orphan modules in tagente_modulo
  741     db_do ($dbh, 'DELETE FROM tagente_modulo WHERE id_agente NOT IN (SELECT id_agente FROM tagente)');
  742 
  743     # Delete orphan modules in tagente_estado
  744      while (defined (get_db_value ($dbh, 'SELECT id_agente FROM tagente_estado WHERE id_agente NOT IN (SELECT id_agente FROM tagente)'))) {
  745         db_delete_limit ($dbh, 'tagente_estado', 'id_agente NOT IN (SELECT id_agente FROM tagente)', $BIG_OPERATION_STEP);
  746     }
  747 
  748     # Delete orphan data_inc reference records
  749     db_do ($dbh, 'DELETE FROM tagente_datos_inc WHERE id_agente_modulo NOT IN (SELECT id_agente_modulo FROM tagente_modulo)');
  750     
  751     # Check enterprise tables
  752     enterprise_hook ('pandora_checkdb_integrity_enterprise', [$conf, $dbh]);
  753 }
  754 
  755 ###############################################################################
  756 # Check database consistency.
  757 ###############################################################################
  758 sub pandora_checkdb_consistency {
  759     my ($conf, $dbh) = @_;
  760     
  761     #-------------------------------------------------------------------
  762     # 1. Check for modules that do not have tagente_estado but have
  763     #    tagente_module
  764     #-------------------------------------------------------------------
  765     if (defined($conf->{'_delete_notinit'}) && $conf->{'_delete_notinit'} ne "" && $conf->{'_delete_notinit'} eq "1") {
  766         log_message ('CHECKDB', "Deleting not-init data.");
  767         my @modules = get_db_rows ($dbh,
  768             'SELECT id_agente_modulo, id_agente
  769             FROM tagente_estado
  770             WHERE estado = 4');
  771         
  772         foreach my $module (@modules) {
  773             my $id_agente_modulo = $module->{'id_agente_modulo'};
  774             my $id_agente = $module->{'id_agente'};
  775             
  776             # Skip policy modules
  777             my $is_policy_module = enterprise_hook('is_policy_module',
  778                 [$dbh, $id_agente_modulo]);
  779             next if (defined($is_policy_module) && $is_policy_module);
  780             
  781             # Skip if agent is disabled
  782             my $is_agent_disabled = get_db_value ($dbh,
  783                 'SELECT disabled
  784                 FROM tagente
  785                 WHERE id_agente = ?', $module->{'id_agente'});
  786             next if (defined($is_agent_disabled) && $is_agent_disabled);
  787             
  788             # Skip if module is disabled
  789             my $is_module_disabled = get_db_value ($dbh,
  790                 'SELECT disabled
  791                 FROM tagente_modulo
  792                 WHERE id_agente_modulo = ?', $module->{'id_agente_modulo'});
  793             next if (defined($is_module_disabled) && $is_module_disabled);
  794             
  795             
  796             #---------------------------------------------------------------
  797             # Delete the module
  798             #---------------------------------------------------------------
  799             # Mark the agent for module and alert counters update
  800             db_do ($dbh,
  801                 'UPDATE tagente
  802                 SET update_module_count = 1, update_alert_count = 1
  803                 WHERE id_agente = ?', $id_agente);
  804             
  805             # Delete the module
  806             db_do ($dbh,
  807                 'DELETE FROM tagente_modulo
  808                 WHERE id_agente_modulo = ?', $id_agente_modulo);
  809             
  810             # Do a nanosleep here for 0,001 sec
  811             usleep (100000);
  812             
  813             # Delete any alerts associated to the module
  814             db_do ($dbh,
  815                 'DELETE FROM talert_template_modules
  816                 WHERE id_agent_module = ?', $id_agente_modulo);
  817         }
  818     } else {
  819         log_message ('CHECKDB', "Ignoring not-init data.");
  820     }
  821     
  822     if (defined($conf{'_days_delete_unknown'}) && $conf{'_days_delete_unknown'} > 0) {
  823         log_message ('CHECKDB',
  824             "Deleting unknown data (More than " . $conf{'_days_delete_unknown'} . " days).");
  825     
  826         my @modules = get_db_rows($dbh,
  827             'SELECT tagente_modulo.id_agente_modulo, tagente_modulo.id_agente
  828             FROM tagente_modulo, tagente_estado
  829             WHERE tagente_modulo.id_agente_modulo = tagente_estado.id_agente_modulo
  830                 AND estado = 3
  831                 AND utimestamp < UNIX_TIMESTAMP() - ?',
  832             86400 * $conf{'_days_delete_unknown'});
  833         
  834         foreach my $module (@modules) {
  835             my $id_agente = $module->{'id_agente'};
  836             my $id_agente_modulo = $module->{'id_agente_modulo'};
  837             
  838             # Skip policy modules
  839             my $is_policy_module = enterprise_hook('is_policy_module',
  840                 [$dbh, $id_agente_modulo]);
  841             next if (defined($is_policy_module) && $is_policy_module);
  842             
  843             # Mark the agent for module and alert counters update
  844             db_do ($dbh,
  845                 'UPDATE tagente
  846                 SET update_module_count = 1, update_alert_count = 1
  847                 WHERE id_agente = ?', $id_agente);
  848 
  849             # Delete the module
  850             db_do ($dbh,
  851                 'DELETE FROM tagente_modulo
  852                 WHERE disabled = 0
  853                     AND id_agente_modulo = ?', $id_agente_modulo);
  854             
  855             # Do a nanosleep here for 0,001 sec
  856             usleep (100000);
  857             
  858             # Delete any alerts associated to the module
  859             db_do ($dbh, 'DELETE FROM talert_template_modules
  860                 WHERE id_agent_module = ?
  861                     AND NOT EXISTS (SELECT id_agente_modulo
  862                         FROM tagente_modulo
  863                         WHERE id_agente_modulo = ?)',
  864                 $id_agente_modulo, $id_agente_modulo);
  865             
  866             # Do a nanosleep here for 0,001 sec
  867             usleep (100000);
  868         }
  869     }
  870     log_message ('CHECKDB',
  871         "Checking database consistency (Missing status).");
  872     
  873     my @modules = get_db_rows ($dbh, 'SELECT * FROM tagente_modulo');
  874     foreach my $module (@modules) {
  875         my $id_agente_modulo = $module->{'id_agente_modulo'};
  876         my $id_agente = $module->{'id_agente'};
  877         
  878         # check if exist in tagente_estado and create if not
  879         my $count = get_db_value ($dbh,
  880             'SELECT COUNT(*)
  881             FROM tagente_estado
  882             WHERE id_agente_modulo = ?', $id_agente_modulo);
  883         next if (defined ($count) && $count > 0);
  884         
  885         db_do ($dbh,
  886             'INSERT INTO tagente_estado (id_agente_modulo, datos, timestamp, estado, id_agente, last_try, utimestamp, current_interval, running_by, last_execution_try) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', $id_agente_modulo, 0, '1970-01-01 00:00:00', 1, $id_agente, '1970-01-01 00:00:00', 0, 0, 0, 0);
  887         log_message ('CHECKDB',
  888             "Inserting module $id_agente_modulo in state table.");
  889     }
  890     
  891     log_message ('CHECKDB',
  892         "Checking database consistency (Missing module).");
  893     
  894     #-------------------------------------------------------------------
  895     # 2. Check for modules in tagente_estado that do not have
  896     #    tagente_modulo, if there is any, delete it
  897     #-------------------------------------------------------------------
  898     
  899     @modules = get_db_rows ($dbh, 'SELECT * FROM tagente_estado');
  900     foreach my $module (@modules) {
  901         my $id_agente_modulo = $module->{'id_agente_modulo'};
  902         
  903         # check if exist in tagente_estado and create if not
  904         my $count = get_db_value ($dbh,
  905             'SELECT COUNT(*)
  906             FROM tagente_modulo
  907             WHERE id_agente_modulo = ?', $id_agente_modulo);
  908         next if (defined ($count) && $count > 0);
  909         
  910         db_do ($dbh, 'DELETE FROM tagente_estado
  911             WHERE id_agente_modulo = ?', $id_agente_modulo);
  912         
  913         # Do a nanosleep here for 0,001 sec
  914         usleep (100000);
  915         
  916         log_message ('CHECKDB',
  917             "Deleting non-existing module $id_agente_modulo in state table.");
  918     }
  919 
  920     #-------------------------------------------------------------------
  921     # 3. Update empty aliases.
  922     #-------------------------------------------------------------------
  923     log_message ('CHECKDB', "Updating empty aliases.");
  924     db_do ($dbh, "UPDATE tagente SET alias=nombre WHERE alias=''");
  925 }
  926 
  927 ##############################################################################
  928 # Print a help screen and exit.
  929 ##############################################################################
  930 sub help_screen{
  931     log_message ('', "Usage: $0 <path to configuration file> [options]\n\n");
  932     log_message ('', "\t\t-p   Only purge and consistency check, skip compact.\n");
  933     log_message ('', "\t\t-f   Force execution event if another instance of $0 is running.\n\n");
  934     exit -1;
  935 }
  936 
  937 ##############################################################################
  938 # Delete old module data.
  939 ##############################################################################
  940 sub pandora_delete_old_module_data {
  941     my ($dbh, $table, $ulimit_access_timestamp, $ulimit_timestamp) = @_;
  942     
  943     my $first_mark;
  944     my $total_time;
  945     my $purge_steps;
  946     my $purge_count;
  947 
  948     my $mark1;
  949     my $mark2;
  950 
  951     # This could be very timing consuming, so make this operation in $BIG_OPERATION_STEP 
  952     # steps (100 fixed by default)
  953     # Starting from the oldest record on the table
  954 
  955     # WARNING. This code is EXTREMELLY important. This block (data deletion) could KILL a database if 
  956     # you alter code and you don't know exactly what are you doing. Please take in mind this code executes each hour
  957     # and has been patches MANY times. Before altering anything, think twice !
  958 
  959     $first_mark =  get_db_value_limit ($dbh, "SELECT utimestamp FROM $table ORDER BY utimestamp ASC", 1);
  960     if (defined ($first_mark)) {
  961         $total_time = $ulimit_timestamp - $first_mark;
  962         $purge_steps = int($total_time / $BIG_OPERATION_STEP);
  963         if ($purge_steps > 0) {
  964             for (my $ax = 1; $ax <= $BIG_OPERATION_STEP; $ax++){
  965     
  966                 $mark1 = $first_mark + ($purge_steps * $ax);
  967                 $mark2 = $first_mark + ($purge_steps * ($ax -1));   
  968 
  969                 # Let's split the intervals in $SMALL_OPERATION_STEP deletes each
  970                 $purge_count = get_db_value ($dbh, "SELECT COUNT(id_agente_modulo) FROM $table WHERE utimestamp < $mark1 AND utimestamp >= $mark2");
  971                 while ($purge_count > 0){
  972                     db_delete_limit ($dbh, $table,  'utimestamp < ? AND utimestamp >= ?', $SMALL_OPERATION_STEP, $mark1, $mark2);
  973                     # Do a nanosleep here for 0,001 sec
  974                     usleep (10000);
  975                     $purge_count = $purge_count - $SMALL_OPERATION_STEP;
  976                 }
  977                 
  978                 log_message ('PURGE', "Deleting old data from $table. $ax%", "\r");
  979             }
  980             log_message ('', "\n");
  981         } else {
  982             log_message ('PURGE', "No data to purge in $table.");
  983         }
  984     } else {
  985         log_message ('PURGE', "No data in $table.");
  986     }
  987 }
  988 
  989 ##############################################################################
  990 # Delete old export data.
  991 ##############################################################################
  992 sub pandora_delete_old_export_data {
  993     my ($dbh, $ulimit_timestamp) = @_;
  994 
  995     log_message ('PURGE', "Deleting old export data from tserver_export_data");
  996     while((my $rc = db_delete_limit ($dbh, 'tserver_export_data', 'UNIX_TIMESTAMP(timestamp) < ?', $SMALL_OPERATION_STEP, $ulimit_timestamp)) ne '0E0') {
  997         print "RC:$rc\n";
  998         usleep (10000);
  999     };
 1000 }
 1001 
 1002 ##############################################################################
 1003 # Delete old session data.
 1004 ##############################################################################
 1005 sub pandora_delete_old_session_data {
 1006     my ($conf, $dbh, $ulimit_timestamp) = @_;
 1007 
 1008     my $session_timeout = $conf->{'_session_timeout'};
 1009 
 1010     # DO not erase anything if session_timeout is not set.
 1011     return unless (defined($session_timeout) && $session_timeout ne '');
 1012 
 1013     if ($session_timeout == 0) {
 1014         # As defined in console.
 1015         $session_timeout = 90;
 1016     }
 1017 
 1018     if ($session_timeout == -1) {
 1019         # The session expires in 10 years
 1020         $session_timeout = 315576000;
 1021     } else {
 1022         $session_timeout *= 60;
 1023     }
 1024 
 1025     $ulimit_timestamp = time() - $session_timeout;
 1026 
 1027     log_message ('PURGE', "Deleting old session data from tsessions_php\n");
 1028     while(db_delete_limit ($dbh, 'tsessions_php', 'last_active < ?', $SMALL_OPERATION_STEP, $ulimit_timestamp) ne '0E0') {
 1029         usleep (10000);
 1030     };
 1031 
 1032     db_do ($dbh, "DELETE FROM tsessions_php WHERE data IS NULL OR id_session REGEXP '^cron-'");
 1033 }
 1034 
 1035 ###############################################################################
 1036 # Main
 1037 ###############################################################################
 1038 sub pandoradb_main ($$$;$) {
 1039     my ($conf, $dbh, $history_dbh, $running_in_history) = @_;
 1040 
 1041     log_message ('', "Starting at ". strftime ("%Y-%m-%d %H:%M:%S", localtime()) . "\n");
 1042 
 1043     # Purge
 1044     pandora_purgedb ($conf, $dbh);
 1045 
 1046     # Consistency check
 1047     pandora_checkdb_consistency ($conf, $dbh);
 1048 
 1049     # Maintain Referential integrity and other stuff
 1050     pandora_checkdb_integrity ($conf, $dbh);
 1051 
 1052     # Move old data to the history DB
 1053     if (defined ($history_dbh)) {
 1054         undef ($history_dbh) unless defined (enterprise_hook ('pandora_historydb', [$dbh, $history_dbh, $conf->{'_history_db_days'}, $conf->{'_history_db_step'}, $conf->{'_history_db_delay'}]));
 1055         if (defined($conf{'_history_event_enabled'}) && $conf->{'_history_event_enabled'} ne "" && $conf->{'_history_event_enabled'} == 1) {
 1056             undef ($history_dbh) unless defined (enterprise_hook ('pandora_history_event', [$dbh, $history_dbh, $conf->{'_history_event_days'}, $conf->{'_history_db_step'}, $conf->{'_history_db_delay'}]));
 1057         }
 1058     }
 1059 
 1060     # Only active database should be compacted. Disabled for historical database.
 1061     # Compact on if enable and DaysCompact are below DaysPurge 
 1062     if (!$running_in_history
 1063         && ($conf->{'_onlypurge'} == 0)
 1064         && ($conf->{'_days_compact'} < $conf->{'_days_purge'})
 1065     ) {
 1066         pandora_compactdb ($conf, defined ($history_dbh) ? $history_dbh : $dbh, $dbh);
 1067     }
 1068 
 1069     # Update tconfig with last time of database maintance time (now)
 1070     db_do ($dbh, "DELETE FROM tconfig WHERE token = 'db_maintance'");
 1071     db_do ($dbh, "INSERT INTO tconfig (token, value) VALUES ('db_maintance', '".time()."')");
 1072 
 1073     # Move SNMP modules back to the Enterprise server
 1074     enterprise_hook("claim_back_snmp_modules", [$dbh, $conf]);
 1075 
 1076     # Check if there are discovery tasks with wrong id_recon_server
 1077     pandora_check_forgotten_discovery_tasks ($conf, $dbh);
 1078 
 1079     # Recalculating dynamic intervals.
 1080     enterprise_hook("update_min_max", [$dbh, $conf]);
 1081 
 1082     # Metaconsole database cleanup.
 1083     enterprise_hook("metaconsole_database_cleanup", [$dbh, $conf]);
 1084 
 1085     log_message ('', "Ending at ". strftime ("%Y-%m-%d %H:%M:%S", localtime()) . "\n");
 1086 }
 1087 
 1088 ###############################################################################
 1089 # Check for discovery tasks configured with servers down
 1090 ###############################################################################
 1091 
 1092 sub pandora_check_forgotten_discovery_tasks {
 1093     my ($conf, $dbh) = @_;
 1094 
 1095     log_message ('FORGOTTEN DISCOVERY TASKS', "Check for discovery tasks bound to inactive servers.");
 1096 
 1097         my @discovery_tasks = get_db_rows ($dbh, 'SELECT id_rt, id_recon_server, name FROM trecon_task');
 1098         my $discovery_tasks_count = @discovery_tasks;
 1099 
 1100         # End of the check (this server has not discovery tasks!).
 1101         if ($discovery_tasks_count eq 0) {
 1102             log_message('FORGOTTEN DISCOVERY TASKS', 'There are not defined discovery tasks. Skipping.');
 1103             return;
 1104         }
 1105 
 1106         my $master_server = get_db_value ($dbh, 'SELECT id_server FROM tserver WHERE server_type = ? AND status != -1', DISCOVERYSERVER);
 1107 
 1108         # Goes through all the tasks to check if any have the server down.
 1109         foreach my $task (@discovery_tasks) {
 1110             if ($task->{'id_recon_server'} ne $master_server) {
 1111                 my $this_server_status = get_db_value ($dbh, 'SELECT status FROM tserver WHERE id_server = ?', $task->{'id_recon_server'});
 1112                 if (!defined($this_server_status) || $this_server_status eq -1) {
 1113                     my $updated_task = db_process_update ($dbh, 'trecon_task', { 'id_recon_server' => $master_server }, { 'id_rt' => $task->{'id_rt'} });
 1114                     log_message('FORGOTTEN DISCOVERY TASKS', 'Updated discovery task '.$task->{'name'});
 1115                 }
 1116             }
 1117         }
 1118 
 1119         log_message('FORGOTTEN DISCOVERY TASKS', 'Step ended');
 1120 }
 1121 
 1122 
 1123 # Init
 1124 pandora_init_pdb(\%conf);
 1125 
 1126 # Read config file
 1127 pandora_load_config_pdb (\%conf);
 1128 
 1129 # Load enterprise module
 1130 if (enterprise_load (\%conf) == 0) {
 1131     log_message ('', " [*] " . pandora_get_initial_product_name() . " Enterprise module not available.\n\n");
 1132 }
 1133 else {
 1134     log_message ('', " [*] " . pandora_get_initial_product_name() . " Enterprise module loaded.\n\n");
 1135 }
 1136 
 1137 # Connect to the DB
 1138 my $dbh = db_connect ($conf{'dbengine'}, $conf{'dbname'}, $conf{'dbhost'}, $conf{'dbport'}, $conf{'dbuser'}, $conf{'dbpass'});
 1139 my $history_dbh = undef;
 1140 is_metaconsole(\%conf);
 1141 if (defined($conf{'_history_db_enabled'}) && $conf{'_history_db_enabled'} eq '1') {
 1142     eval {
 1143         $conf{'encryption_key'} = enterprise_hook('pandora_get_encryption_key', [\%conf, $conf{'encryption_passphrase'}]);
 1144         $history_dbh = db_connect ($conf{'dbengine'}, $conf{'_history_db_name'}, $conf{'_history_db_host'}, $conf{'_history_db_port'}, $conf{'_history_db_user'}, pandora_output_password(\%conf, $conf{'_history_db_pass'}));
 1145     };
 1146     if ($@) {
 1147         if (is_offline(\%conf)) {
 1148             log_message ('!', "Cannot connect to the history database. Skipping.");
 1149         } else {
 1150             die ("$@\n");
 1151         }
 1152     }
 1153 }
 1154 
 1155 # Only run on master servers.
 1156 pandora_set_master(\%conf, $dbh);
 1157 if ($conf{'_force'} == 0 && pandora_is_master(\%conf) == 0) { 
 1158     log_message ('', " [*] Not a master server.\n\n");
 1159     exit 1;
 1160 }
 1161 
 1162 # Get a lock on dbname.
 1163 my $lock_name = $conf{'dbname'};
 1164 my $lock = db_get_lock ($dbh, $lock_name);
 1165 if ($lock == 0 && $conf{'_force'} == 0) { 
 1166     log_message ('', " [*] Another instance of DB Tool seems to be running.\n\n");
 1167     exit 1;
 1168 }
 1169 
 1170 # Main
 1171 pandoradb_main(\%conf, $dbh, $history_dbh);
 1172 
 1173 # history_dbh is unset in pandoradb_main if not in use.
 1174 if (defined($history_dbh)) {
 1175     log_message('', " [>] DB Tool running on historical database.\n");
 1176     my $h_conf = pandoradb_load_history_conf($history_dbh);
 1177 
 1178     # Keep base settings.
 1179     $h_conf->{'_onlypurge'} = $conf{'_onlypurge'};
 1180 
 1181     # Re-launch maintenance process for historical database.
 1182     pandoradb_main(
 1183         $h_conf,
 1184         $history_dbh,
 1185         undef,
 1186         1 # Disable certain funcionality while runningn in historical database.
 1187     );
 1188 
 1189     # Handle partitions.
 1190     enterprise_hook('handle_partitions', [$h_conf, $history_dbh]);
 1191     
 1192 }
 1193 
 1194 # Keep integrity between PandoraFMS agents and IntegriaIMS inventory objects.
 1195 pandora_sync_agents_integria($dbh);
 1196 
 1197 # Get Integria IMS ticket types for alert commands.
 1198 my @types = pandora_get_integria_ticket_types($dbh);
 1199 
 1200 if (scalar(@types) != 0) {
 1201     my $query_string = '';
 1202     foreach my $type (@types) {
 1203             $query_string .= $type->{'id'} . ',' . $type->{'name'} . ';';
 1204     }
 1205 
 1206     $query_string = substr $query_string, 0, -1;
 1207 
 1208     db_do($dbh, "UPDATE talert_commands SET fields_descriptions='[\"Ticket&#x20;title\",\"Ticket&#x20;group&#x20;ID\",\"Ticket&#x20;priority\",\"Ticket&#x20;owner\",\"Ticket&#x20;type\",\"Ticket&#x20;status\",\"Ticket&#x20;description\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\"]' WHERE name=\"Integria&#x20;IMS&#x20;Ticket\"");
 1209     db_do($dbh, "UPDATE talert_commands SET fields_values='[\"\", \"\", \"\",\"\",\"" . $query_string . "\",\"\",\"\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\",\"_integria_type_custom_field_\"]' WHERE name=\"Integria&#x20;IMS&#x20;Ticket\"");
 1210 }
 1211 
 1212 # Release the lock
 1213 if ($lock == 1) {
 1214     db_release_lock ($dbh, $lock_name);
 1215 }
 1216 
 1217 # Cleanup and exit
 1218 db_disconnect ($history_dbh) if defined ($history_dbh);
 1219 db_disconnect ($dbh);
 1220 
 1221 exit 0;