#############################################################3
# DB Entry
# --------
# This program manages the "ConfigDB" database.
# It runs the scripts defined by the ConfigDB "Scripts" Table, and cap
+tures
# Devices configuration data, and saves it in the database.
#
# April 2002, by Vijay Anand
#
# Run Like this :
# perl F:\Vijay\scripts\DB-Entry.pl
# This will trash errors, and send only good stuff into THIS program
# April 2003 - Update logic for checking prev checksum
##################################################################
use strict;
use Digest::MD5;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
use IPC::Run qw(run timeout);
use Net::TFTP;

# General Use globals

my $TFTP_Server = '10.00.00.00;  # IP deliberaty obscured
my $inConfig=0;
#my $ext_Prog = "perl F:\\Vijay\\scripts\\Cisco-config.pl";
#my $DATABASE = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\\Doc
+uments and Settings\\vijay\\My Documents\\ConfigDB.mdb";
my $DATABASE = "driver={SQL Server};server=WWMONITOR3;database=ConfigD
+B;";
my $output_count=0;
my $md5 = Digest::MD5->new;
my $MD5_CheckSum;
my $DB_Data;

# Database related globals
 my ($Errors,$error);
 my ($DB_Connection, $Device_RS);
# ADO Constants ...
 my $adLockOptimistic=3;
 my $adOpenForwardOnly=0;
 my $adOpenDynamic=2;
 my $adCmdTable=2;
 my $adAffectCurrent= 1;
 my $adResyncAllValues= 2;
 my $TestDevice = $ARGV[0]; # Optional - to process a single named dev
+ice


  &Open_Database;
  # Find devices configured in the DB, and process them
  $Device_RS = &Open_TableRS('[Device]') or die "Could not open table 
+'Device'\n";
  
  while(! $Device_RS->EOF){
      if ($TestDevice){
            $TestDevice eq $Device_RS->Fields('Name')->{Value}
                ? &Process_this_Device
                : print $Device_RS->Fields('Name')->{Value}," ignored\
+n";
      }else{
         &Process_this_Device;
      }
      $Device_RS->moveNext;
  }
  
  $DB_Connection->Close;
  # End of Program

######################################################

sub Process_this_Device{
    my $Device_ID     = $Device_RS->Fields('ID')->{Value};
    #my $Device_Script =  $Device_RS->Fields('script')->{Value};
    my $Device_ScriptID =  $Device_RS->Fields('scriptID')->{Value} 
             or die "ERROR: No script specified for device $Device_ID 
+\n";
    my $Device_Name   = $Device_RS->Fields('Name')->{Value};
    my $Device_IP     =  &AllowNull($Device_RS->Fields('IP')->{Value})
+;
    my $Device_UserID     =  &AllowNull($Device_RS->Fields('UserID')->
+{Value});
    my $Device_Password    =  &AllowNull($Device_RS->Fields('Password'
+)->{Value});    
    my $Device_En_Password =  &AllowNull($Device_RS->Fields('Password_
+Enable')->{Value});
    
    my $Script_RS = &Run_Query("SELECT * From Script where ID=$Device_
+ScriptID");
    
    my $Script_Params = $Device_IP eq 'NONE' ? $Device_Name : $Device_
+IP;
    $Script_Params .= " $Device_UserID  $Device_Password  $Device_En_P
+assword";
         
    
    print "=================\nProcessing Device $Device_ID=" , $Device
+_Name , "\n";
    $Script_RS->EOF and die "ERROR: No Script found for $Device_Name.\
+n";
    
    my @interpreter= ($Script_RS->Fields('Interpreter')->{value}, # Pe
+rl, Cmd or Cscript etc..
                      "--", #  The "--" says "here end options for per
+l".
                      "-",  # The "-" says "read the script from STDIN
+".
                      $Device_IP,        $Device_UserID,
                      $Device_Password,  $Device_En_Password,
                      $TFTP_Server);
        
    $interpreter[0]=~s/\s//g; # Zap all whitespace
    
    my $Program_Output = '';
    my $Program_Err  = '';
    my $code =   $Script_RS->Fields('Code')->{value};
    # SQL Server messes with the quote,  "<" and ">" characters. Resto
+re these
    $code =~s/&gt;/>/g;
    $code =~s/&lt;/</g;
    $code =~s/&quot;/\"/g;
    
 $TestDevice and print  #"Code ====\n$code\n--------\n" .
                        "Params=@interpreter\n";
    
    run \@interpreter, 
          '<',  \$code, 
          '>',  \$Program_Output ,
          '2>', \$Program_Err 
        or die "*ERROR* RUN Failed: $?\n";
 
 $TestDevice and print "--output\n$Program_Output\n---Errors=\n$Progra
+m_Err\n-----\n";    
    $output_count = 0;
    $MD5_CheckSum = 0;
    $md5 = Digest::MD5->new; # Clear out  Checksum
    $DB_Data = '';
    $inConfig = 0;

    
    foreach(split /\n/, $Program_Output) {
       m/<\/config>/ and $inConfig=0;
       $inConfig and &Store_it($_);
       m/<config>/ and $inConfig = 1;
       m/<tftp\s.+\/>/ and &Handle_TFTP_Params($_);
    }
    if ($output_count == 0){
       # The output did not contain <config> and </config>
       # We give him another chance, and save what it DID produce
       foreach(split /\n/, $Program_Output){
          &Store_it($_);
       }
       if ($Program_Err){
      print "--- Standard Error Output ---\n$Program_Err\n-----\n";
      &Store_it('--- Standard Error Output ---');
             foreach(split /\n/, $Program_Err){
              &Store_it($_);
          }
       }
    }
    print "\n$output_count lines output;  ";
    # Note - the 'digest' function returns a 128-bit STRING!! We use &
+ store the HEX version
    $MD5_CheckSum = $md5->hexdigest();
    print "MD5_CheckSum is $MD5_CheckSum\n";
    &Save_Data($Device_ID);
}

######################################################
sub Handle_TFTP_Params{
    my $line=shift;
    my ($file, $server,$fh);
    print "TFTP:$line\n";
    # Extract filename and server name, un-quoted
    # Use "Non-Greedy" i.e. the "+?" operator.
    $line =~   m/file\S*=\s*[\'\"](.+?)[\'\"]/i and $file=$1;
    $line =~ m/server\S*=\s*[\'\"](.+?)[\'\"]/i and $server=$1;
    print "File:$file;  Server:$server;\n";
    
    $server or die "No TFTP server specified";
    $file or die "No TFTP file specified";
    
    my $tftp = Net::TFTP->new("$server", BlockSize => 1024);
    $fh = $tftp->get($file);
    print "\n",$tftp->error,"---done---\n";
    
    while (<$fh>){
       &Store_it($_);
    }
    close($fh);
}
   
######################################################

sub AllowNull{
   # Return Empty string, if nothing is passed.
   my $value = shift() or return 'NONE'; 
   
   if ($value eq '' or $value eq 'NONE'){
      return 'NONE';
   }
      return $value;   
}
######################################################

sub Store_it{
   my $line = shift();
   
   ##print "\]\]$line";
   $DB_Data .= "$line\n" ;
   $md5->add($line);

   $output_count ++; 
    
}   

######################################################
sub Save_Data{
    my $DeviceID = shift;
    my $Previous_Checksum = 0;
    my $Config_RS;
    
    #Initialize_Config_Storage_and_Time
    # Check to see if this device & Checksum are already there

    my $Prev_rs = &Run_Query("SELECT TOP 1 CheckSum from Config" .
                            " WHERE (deviceid=$DeviceID) ORDER by [Dat
+e] desc");
    $Prev_rs->EOF or $Previous_Checksum = $Prev_rs->Fields('CheckSum')
+->{Value};
     
    if ($Previous_Checksum eq $MD5_CheckSum){
       print "Configuration not changed. Nothing saved.\n";
       &Run_Query("Update Config Set LastAttempted=CURRENT_TIMESTAMP" 
+.
           " WHERE ID =(select TOP 1  ID from config" .
                       " WHERE deviceid=$DeviceID order by Date desc)"
+);
       return 0;
    }   

    ## New record needs to be added ...
    $Config_RS = &Open_TableRS('Config');
     print "Config Table open. Field count=", $Config_RS->Fields->{Cou
+nt} , " chksm=$MD5_CheckSum\n";
    
   
    # Need to add a record to save this ..
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =    loca
+ltime(time);
    $year = 1900 + $year;     # Perl year starts at 1900
    $mon = $mon + 1;         # Crazy perl uses "0' based month numbers
+                                                
    my $DB_Timestamp = sprintf('%4u-%02u-%02u %02u:%02u:%02u',
                                $year, $mon, $mday,  $hour, $min, $sec
+);
    $Config_RS->AddNew;
    $Config_RS->Fields('DeviceID')->{Value} = $DeviceID;
    $Config_RS->Fields('Date')->{Value}     = $DB_Timestamp;
    # If you don't force the type to STRING, a zero gets stored ...
    $Config_RS->Fields('CheckSum')->{Value} = '' . $MD5_CheckSum;
    $Config_RS->Fields('Data')->{Value}     = $DB_Data;
    $Config_RS->Fields('Comment')->{Value}  = "LineCount=" . $output_c
+ount;
    # Cant set this : $Config_RS->Fields('Options')->{Value}  = '';
    $Config_RS->Update;
}
######################################################



######################################################
sub CheckError{
    #if(!(@_[1] || $RecordSet)) {
        $Errors = $DB_Connection->Errors();
   if ($Errors->{Count} > 0){
        # Ignore "Cursor type changed" errors ...
    $Errors->{Count} == 1 and $Errors->item(0)->{Number} == 0 and retu
+rn;
        print "** $Errors->{Count} DB Errors at line $_[0]:\n";
        my $i;
        for ( $i=0; $i < $Errors->{Count}; $i++) {
            print $Errors->item($i)->{Number},$Errors->item($i)->{Desc
+ription}, "\n";
        }
    die;
    }
}

######################################################
sub Open_Database{
   print "Opening DB ...";
   $DB_Connection = Win32::OLE->new('ADODB.Connection') or die "Cant c
+reate DB_Connection"; # creates a connection object
   $DB_Connection->Open($DATABASE);
   $DB_Connection or         die "Cannot open Database\n"; 

    print " DB_Connection opened ..";

    $DB_Connection->Errors->Clear; # Drop warnings
    print "DB Open completed.\n";
}
######################################################
sub Run_Query{
    # Query is passed as a parameter.
    # (Recordset) is returned
    my $SQL=shift(@_);

    my $RecordSet = $DB_Connection->Execute($SQL);
    &CheckError (__LINE__ . ":Sql=" . $SQL);
    return $RecordSet;
}
######################################################
sub Open_TableRS{
   my ($Table_Name)= shift(@_);
   my ($my_RS);
    
   $my_RS =  Win32::OLE->new('ADODB.Recordset');
   $my_RS->Open($Table_Name, $DB_Connection, $adOpenDynamic , $adLockO
+ptimistic, $adCmdTable);
   &CheckError (__LINE__, $my_RS);
   return $my_RS;
}
[download]