in reply to Need to compare 2 hash values

Here's how I would do it: 


use strict;

use Text::CSV_XS;
use DBI;

my $csv_inputfile = "input.csv"; # change me

my %data; # hash for storing csv data

my $csv = Text::CSV_XS->new;
open my $fh, '<', $csv_inputfile or die "can't open $csv_inputfile: $!
+ \n";
while (my $line = <$fh>) {
  $line =~ s/^\s+//;  # strip leading whitespace
  next unless $line;  # ignore blank lines

  # assume CSV file looks like: ip, user, nbname
  if ( $csv->parse($line) ) {
    my ($ip, $user, $nbname) = $csv->fields();
    next unless $ip;
    $data{$ip} = {
      USER    => $user   || '',
      NBNAME  => $nbname || '',
      
    };
  } else {
    my $error = $csv->error_diag();
    print "Parse error: line $. : $error.\n";
  }

}
close $fh;

my $user = "db_user";  # change me
my $pass = "db_pass";  # change me
my $dbh = DBI->connect(
  "dbi:mysql:pstsize",
  $user,
  $pass,
  {RaiseError => 1, AutoCommit => 1},
);
unless ($dbh) {
  die "Could not get database handle.";
}

my $sth = $dbh->prepare( select_data() );

eval {
  $sth->execute();
};
if ($@) {
  die "Database error: ", $dbh->errstr;
}

print "<html><body><pre>\n";

while ( my ($ip, $user, $nbname) = $sth->fetchrow ) {

  unless ($user) {
    print "DB missing USER for $ip.";
    if ($data{$ip}{'USER'}) {
      print ".. but CSV file has value: $data{$ip}{'USER'}.";
    }
    print "\n";
  }

  unless ($nbname) {
    print "DB missing NBNAME for $ip.";
    if ($data{$ip}{'NBNAME'}) {
      print ".. but CSV file has value: $data{$ip}{'NBNAME'}.";
    }
    print "\n";
  }

}

print "</pre></body></html>";

$sth->finish;
$dbh->disconnect;

sub select_data {
  return <<"  END_SQL";
    SELECT DISTINCT
      ipaddr,
      user,
      nbname
    FROM
      sizehist
    ORDER BY
      ipaddr
  END_SQL
}

[download]

Notes:

Good luck!

  • Comment on Re: Need to search through a hashref array to find empty, null, or otherwise translucent key values.
  • Download Code

Replies are listed 'Best First'.
Re^2: Need to search through a hashref array to find empty, null, or otherwise translucent key values.
by carlo1973 (Initiate) on May 27, 2009 at 17:17 UTC
    Thank you so much!

    I never expected such an overwelming response to my querry. :)

    It was asked about the problem I'm trying to solve - well in a nutshell we have a system that utilizes ldap and a homebrew solution that collects data on outlook pst files for all the users in the company. The homebrew program was supposed to be checking user name, ip address, netbios name, pst size and location, and a few other categories and storing them into a database (pstsize).

    We recently noticed that this database has been missing information lately. Some pc's basically have not reported back with the information so it's not getting recorded. In order to determine who's systems are not reporting, I've been asked to come up with a solution as part of my internship.

    I've made a bash file that would make a stripped down version of the master host file (use_me.txt) in csv format. We chose the master host file for the reason that everything is logged that goes through the main server. This unfortunatly includes our network printers and camera's but I managed to filter those out. The idea is to compare the entries in the database to that of use_me.txt. Those entries that are in use_me.txt but not in the database will be reported. Anything else can be ignored.

    Originally we also noticed a lot of NULL's in the username field of the DB. We were going to filter against it till we realised that it was because of new systems just being initialised and no username was provided for them. We removed those from the database all together and are not worried about those any longer. Unfortunatly we didn't realise it till after closing, and this is the first chance I've had to update my querry to everyone.

    There are a few variables that we haven't taken into account until today. One being something as benign (but possible issue in perl) of case sensitivity from the data supplied back from the database vs that of the csv file. Another possible varriable to contend with is that some of the entries in the csv do not follow nbname, ip address schema. For example:

    carlo,192.168.1.210
    docindex2,docindex.com.

    However I don't think we're going to worry to much about the reports showing items similar to docindex2 as an item not found in the db. Its something that can be addressed later, perhaps with more filtering. But for now we'll leave them in

    Thanks again to everyone who posted back! It's much appreciated :)

    Oh as for why I commented out #strict - well yeah I was trying to cheat <slapping fingers with ruller> I will fix that right away :)

[reply]

In Section Seekers of Perl Wisdom