Good afternoon!
I am a perl newbie. I hope you will be patient with me.
I have a script that needs to be written in perl. I can't do it in awk or shell scripting.
Here is the script:
#!/usr/bin/perl
use POSIX qw(strftime);
use FileHandle;
use Getopt::Long;
use IO::Handle; $MYSQL='/pathtomysql/mysql';
my ($dbh,$CONTAINER,$DEBUG,$DOMAIN,$DOMTYPE,$USER,$PASSWORD,$USAGE);
$Getopt::Long::autoabbrev=1; &GetOptions('-zdebug'=>\$DEBUG,'container:s'=>\$CONTAINER,'-user:s'=>\$USER,'-password:s'=>\$PASSWORD,'-x'=>\$USAGE,'v'=>\$VERSION);
if ($VERSION) {
print "Version 1.0 Build date: 23 May 2012 \n";
exit;
}
if (! $CONTAINER) {
print "missing container: $CONTAINER \n";
&usage;
}
if (! $USER) {
print "missing user: $USER\n";
&usage;
}
if (! $PASSWORD) {
print "missing password: $PASSWORD\n";
&usage;
}
if ($USAGE) {
&usage;
}
print "container: $CONTAINER \n" if $DEBUG;
# This is converting it to all uppercase
$CONTAINER =~ tr/a-z/A-Z/;
my $TMPSQLCONT="/tmp/sql";
my $TMPCONTLIST="/tmp/sql.contlist";
# Getting the container ID's for the subnets that we want to get data on
$SQLCONT="select user_defined_fld_value.objectid, inet_ntoa(block.STARTADDR),
user_defined_fld.tag, user_defined_fld_value.VALUE from user_defined_fld_value
left join user_defined_fld on user_defined_fld_value.USER_DEFINED_FLD_ID=user_defined_fld.id
left join container_block on container_block.blockid=user_defined_fld_value.objectid
left join block on block.id=container_block.blockid
left join container on container.id=container_block.containerid
where container.notes like '%$CONTAINER%'";
my $tmpcont=FileHandle->new;
$tmpcont->open(">$TMPSQLCONT");
print $tmpcont "$SQLCONT;\n";
$tmpcont->close;
system("$MYSQL -u $USER -p$PASSWORD instancename <$TMPSQLCONT > $TMPCONTLIST");
# Deleting file afterwards
unlink($TMPSQLCONT);
# Informing where the final file is located
print "Your file is located at $TMPCONTLIST\n";
#########################################################################
sub usage {
print "Usage: \n";
print " -c: Container \n";
print " -u: login \n";
print " -p: password\n";
print " -z: debug \n";
print " -x: This message \n"; exit 0;
}
#########################################################################
This is a sample of the $TMPSQLCONT (it is tab delimmited):
9885 10.10.9.48 Room 1105A
9885 10.10.9.48 Jack 1105A--05D
9885 10.10.9.48 org_code B703
9885 10.10.9.48 Building 1268A
114948 10.10.184.0 google_nets off
114948 10.10.184.0 blockSecName test name
114948 10.10.184.0 blockTechName brian test
114948 10.10.184.0 blockAdminName test admin
114949 10.10.184.0 blockSecName John G. Smooth
114949 10.10.184.0 blockTechPhone 222-555-1212
114949 10.10.184.0 blockAdminName Lucy P. Wallice
114949 10.10.184.0 blockAdminId 8878787
114949 10.10.184.0 block_name unknown
114949 10.10.184.0 blockSecId 787878
114949 10.10.184.0 blockAdminEmail lucy.p.wallice@google.com
114949 10.10.184.0 blockTechName TEST LAN
114949 10.10.184.0 blockSecPhone 222-555-3232
114949 10.10.184.0 blockTechEmail terCInternal@google.com
114949 10.10.184.0 blockSecEmail John.goody@google.com
114949 10.10.184.0 google_nets off
How can I go through this and split based on tabs, then join it based on the second field? Like this:
10.10.9.48|Room=1105A|Jack=1105A--05D|org_code=B703|Building=1268A
10.10.184.0|google_nets=off|blockSecName=test name|blockTechName=brian test|blockAdminName=test admin|blockSecName=John G. Smooth|blockTechPhone=222-555-1212|blockAdminName=Lucy P. Wallice|blockAdminId=8878787|block_name=unknown|blockSecId=787878|blockAdminEmail=lucy.p.wallice@google.com|blockTechName=TEST LAN|blockSecPhone=222-555-3232|blockTechEmail=terCInternal@google.com|blockSecEmail=John.goody@google.com|google_nets=Sof
---------- Post updated at 07:08 PM ---------- Previous update was at 04:04 PM ----------
Got it to work. Added this to the end:
my $TMPCONTLISTFINAL="/tmp/sql.contlist.final";
open (FILE2, ">$TMPCONTLISTFINAL") || die "problem opening $TMPCONTLISTFINAL\n"
;
open (FILE, $TMPCONTLIST);
my %output;
while (<FILE>) {
chomp( my ( $ip, $fld2, $fld3 ) = ( split /\t/ )[ 1 .. 3 ] );
push @{ $output{$ip} }, "$fld2=$fld3"; }
print FILE2 "$_|" . join( '|', @{ $output{$_} } ) . "\n\n" for sort keys %output
;
close(FILE);
close(FILE2);
# Deleting file afterwards
unlink($TMPSQLCONT);
unlink($TMPCONTLIST);