Hello All,
So this script below is supposed to go to the Url listed which is a direct link to webpage which will open an excel formatted spreadsheet. Im then taking the data out of the spreadsheet and placing them within the specified variables so I can then send an email alert out on the tickets. But I get the following error after it runs for about 5 minutes: Failed to GET 'https://alacrity.secureextrasheet.com/uview?call=dg.reporting.custom.RHBgReqAlacrity&851_locale=-6.0&action=xmlapi&baseviewid=4&key=Y60f24cdc00ad0dde0c4f127cbedebbc0&reportid=8753&lob=300&resultsformat=formattedexcel': 500 Can't connect to alacrity.secureextrasheet.com:443 (connect: Connection timed out) at alacrity_defined.pl line 30. I don't have the module use Crypt::SSLeay, do I need this to connect internally to a proxy perhaps or WWW::Mechanize? Any ideas?
#!/opt/perl588/bin/perl
use strict;
use warnings;
## Required Non Standard Modules
use LWP::UserAgent;#LWP provides an objet for the application LWP. which is UserAgent for clients. The UserAgent acts as a browser it connects to a server, sends request
#recieves repsonses and manages the recieved data.
#use Crypt::SSLeay;
use DBI;
use URI::URL;#possibly needed to be able to specify the URL for this code
use IO::Compress::Gzip qw(gzip $GzipError);
use MIME::Lite;
use Config::Std;
read_config "/var/apache2/cgi-bin/l2cgi.cfg" => my %cgi_config_hash;
#Might need the HTPP PM
$ENV{'http_proxy'} = 'http://proxy.wonu.net:8080';
$ENV{'https_proxy'} = 'http://proxy.wonu.net:8443';
## Get the URL for your flexible report from the URLs link in ConQuest Flexible Reporting
my $url = 'https://alacrity.secureextrasheet.com/uview?call=dg.reporting.custom.RHBgReqAlacrity&851_locale=-6.0&action=xmlapi&baseviewid=4&key=Y60f24cdc00ad0dde0c4f127cbedebbc0&reportid=8753&lob=300&resultsformat=formattedexcel';
#DEBUG print "Url to $url\n"; You need the URI::URL class to be able to utilize the url specification perhaps
my $ua = LWP::UserAgent->new or die "Problem with the new Useragent\n";#user agent
#DEBUG print "New LWP\n";
my $response = $ua->get($url) or die "Problem with the get $url\n";
#DEBUG print "Getting responce\n";
$response->is_success or die "Failed to GET '$url': ", $response->status_line;#is_success returns true when the response code is 200 - 299
#DEBUG print "About to print the html\n";
my $flexreport;
# example
#Id #APP #Description
#15667354 CTRM Headroom Breach - HR Expiry: 31-DEC-10 - Retirement Date: 29-OCT-10
if ($response->is_success)
{
$flexreport = $response->decoded_content; # returns the content with any content-Encoding undone(UTF-8) and the raw content encoded to perl's
#
}
else
{
die $response->status_line;#returns the string "<code><message>". if the message attribute is not set then the offical name of code is sub'ed
}
my @information = split(/\n/, $flexreport);#split each one where there is a blank line move down to the next line and assign it to the array @information
for (my $i=0;$i<=$#information;$i++) # $# references the information array to take it within the information scalar $information
{
my ($app, $id, ,$user, $time_open, $description) = split(/\t/, $information[$i]);#the date from the array @information is split by the blank space and tab'd to the next line for each
#one and assign the data to each variable
insert_information($app, $id, $description);#calls the subroutine insert_information with the data set in the scalar variables id, app and description which were
#split into the scalar variables.
#
}
exit 0;
sub insert_information
{
my $app = shift;#removes the last value of the scalar that was listed
my $cq_id = shift;
my $description = shift;
my $dbh = DBI->connect_cached( "dbi:Sybase:TACMON_CMT", "tacmon_write", "itkgkh9" ) or die "Can't connect to database: ", $DBI::errstr, "\n";#if another call is
#made to connect_cached with the same parameter values then the corresponding $dbh will be returned if it is valid. Th dbh is replaced with a new connection if disconnected
$dbh->{syb_show_eed} = 1;#extended error information is is included in the string returned by $dbh-->errstr.
$dbh->{AutoCommit} = 0;#Turns AutoCommit off. But it might not need to be turned off.
my $insert_string = "insert into dbo.TMAlerts " . "(application, environment, event_date, error_level, debug, tivoli_alert, jabber_alert, nagios_alert, email, name_space, host_name, summary) " . " VALUES (?, ?, getdate(), ?, ?, ?, ?, ?, ?, ?, ?, ?)\n";
#$insert_string .= 'select @@identity';
my $sth = $dbh->prepare_cached($insert_string) or return fail_sql("Can't prepare sql statement: " . $dbh->errstr );
my $insert_result = $sth->execute( "CMT", "CONQUEST", 3, 0, 0, 1, 0, "saintclaire\@wonu.com", $cq_id, "CONQUEST", "$app - Unassigned Alacrity $cq_id" );#error level -> 3, debug -> 0 and etc
or return fail_sql( "Can't execute SQL statement: " . $sth->errstr );
my $test = $sth->errstr;
my $ref = $sth->fetchall_arrayref; # grabs all the data from the 1, 2 and 3 columns.
my $id = $ref->[0]->[0];#it's data is multidimensional so the first parameter is from all of the data returned and then it points to the first data. The pkey is the first
#column in the query.
my $blh = $dbh->prepare("select body from dbo.TMAlerts where pkey = $id")#body is an image so we would have to use the sybase connect
or return fail_sql( "Can't prepare sql statement: " . $dbh->errstr );
$blh->execute() or return fail_sql( "Can't execute SQL statement: " . $blh->errstr );
while ( $blh->fetch )
{ # don't care about the data!
$blh->syb_ct_data_info( 'CS_GET', 1 );
}
$blh->syb_ct_prepare_send();
my $compressed_body;
my $body = "$app - Unassigned Alacrity $cq_id\n\n$description";# this will have the app for each row from the output??
gzip(\$body => \$compressed_body,
Name => '', # Blank out the name of the file to save space (stored else where in table)
'-Level' => 9)
or fail_sql("Couldn't compress message to put into db? $GzipError");
my $body_size = length($compressed_body);#this is needed for total_txlen variable to get the count in bytes.
$blh->syb_ct_data_info( 'CS_SET', 1, { total_txtlen => $body_size, log_on_update => 1 } )#transfering the data in a single chunk($image, $bytes)
or return fail_sql( "Bad blob pointer. Null?" . $dbh->errstr() );
#body is an image so we would have to use the blob sybase connection. And
#set the log_on_update to TRUE by setting it to 1.
$blh->syb_ct_send_data( $compressed_body, $body_size )
or return fail_sql( "Can't execute blob update: " . $sth->errstr() . "----");
$blh->syb_ct_finish_send();#commits the process
$dbh->commit() # Commit tran once the unlink has completed so re-run never hits a dupe row as we turned off Auto Commit on line 71
# Commit tran once the unlink has completed so re-run never hits a dupe row.
or return fail_sql( "Commit failed!" . $dbh->errstr );
return 1;
$dbh->disconnect
or print "Disconnect Error: $dbh->errstr()";
warn "Failed to disconnect: ", $dbh->errstr(), "\n" if $dbh->errstr();
}