in reply to Re: Comparing csv text file to mysql db in order to look for missing entries within the db.
in thread Comparing csv text file to mysql db in order to look for missing entries within the db.

Thanks again everyone and esp scorpio17 for their patience! :) Well I was asked to consult the senior IT guy to get this project done. Unfortunatly he just re-wrote my code. He tried to explain but I wasn't really able to follow as I couldn't see his screen LOL (Unfortunatly my contortionist skills aren't quite to par so bending around his "fortress of death" was a bit difficult. I honestly don't know how he gets in and out of his office with all the towers of computers around him and on his desk LOL)

Here is the new code:

#!/usr/bin/perl -w use strict; use DBI; use Net::Ping; use CGI qw(:all) # This script (should) compare a copy of the master host file to the s +izehist database # in order to determine what systems are currently not reporting their + pstsize. my $dbh = DBI->connect( 'DBI:mysql:pstsize' ); my $sth = $dbh->prepare( 'select * from sizehist where ipaddr = ?' ); my $csv_inputfile = "/temp/use_me.txt"; my $NFcount = 0; my $NPcount = 0; my $ping = new Net::Ping; my $time_input_file = "/temp/lasttime.txt"; open my $fh, $time_input_file or die "can't open $time_input_file: $! +\n"; while (my $line = <> ) { chomp ( $line ); my ( $time ) = $line; close $fh print header("text/plain"), start_html("Computers not reporting pstsize"); print <<EndHTML; <h2><b><u><center>Computers not reporting Outlook PST size</h2></b></u +></center> <center>This will also show which of these are not responding to pings + (useful for troubleshooting)<br><br></center> open my $fh, $csv_inputfile or die "can't open $csv_inputfile: $! \n"; while ( my $line = <$fh> ) { chomp( $line ); my ( $name, $ip ) = split /,/, $line; next if $ip =~ /[^\d\.]/; next if $name =~ /^dhcp-/; next if $ip =~ /^192\.168\.8\./; $sth->execute( $ip ); my $row = $sth->fetchrow_hashref; $sth->finish; unless ( $row ) { my $canPing = $ping->ping( $ip, 2 ); ++$NPcount unless $canPing; printf "%s (%s)%s\n", $name, $ip, $canPing ? '' : ' NO PING'; ++$NFcount; } } print "Hosts not found in DB: $NFcount\n"; print "Hosts not responding to PING: $NPcount\n"; print "The host file was last updated on $time\n"; <br> close $fh; EndHTML print end_html;
Edit

Well my internship is now complete. The code works! YAY!

Now I can read those books I baught from begining to end rather than hunt and pecking through the indexes hoping that I will find something useful. I have to admit that even though that this has been trying, it has renewed my interest in studying Perl, MySQL,and PHP :) Thanks again and take care!

  • Comment on Re^2: Comparing csv text file to mysql db in order to look for missing entries within the db.
  • Download Code