05-12-2015 01:56 AM
Hello all,
I am newbie to perl, still in learning phase. I need help in below Perl/AXL/SOAP update script for phone owner user id.
Below is my script, customized script which collected from this comunities. when I ran this script, I am getting below output, in general it says : syntax error. But when I ran this update in CLI mode it is working fine
e.g :run sql update Device set fkenduser = ( select pkid from EndUser where userid = 'dannywon' ) where name = 'SEPAABBAABBAABB'
CUCM verison 9.1
Need expertise help in this.
Thanks,
Renga.
scrip output :
-----------------------
*************************************************************************************************************
SOAP::Transport::HTTP::Client::send_receive: POST https://141.121.1.4:8443/axl/ HTTP/1.1
Accept: text/xml
Accept: multipart/*
Accept: application/soap
Authorization: Basic Y3VjbWFkbWluOjZjbSEwbi1uOXQ8a3M=
Content-Length: 474
Content-Type: text/xml; charset=utf-8
SOAPAction: "http://www.cisco.com/AXL/API/1.0#executeSQLQuery"
<?xml version="1.0" encoding="UTF-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><soap:Body><executeSQLQuery xmlns="http://www.cisco.com/AXL/API/1.0"><sql xsi:type="xsd:string">sql update Device set fkenduser =(select pkid from EndUser where userid = GXL-Site-PNG-1) where name = SEP00270D3DAD16 </sql></executeSQLQuery></soap:Body></soap:Envelope>
SOAP::Transport::HTTP::Client::send_receive: HTTP/1.1 200 OK
Connection: close
Date: Tue, 12 May 2015 08:31:31 GMT
Accept: text/xml, text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2
Server:
Content-Length: 539
Content-Type: text/xml;charset=utf-8
Client-Date: Tue, 12 May 2015 08:26:58 GMT
Client-Peer: 141.121.1.4:8443
Client-Response-Num: 1
Client-SSL-Cert-Issuer: /C=SG/O=Keysight Technologies/OU=Network IT/CN=SGPCMP1/ST=Singapore/L=Singapore
Client-SSL-Cert-Subject: /C=SG/O=Keysight Technologies/OU=Network IT/CN=SGPCMP1/ST=Singapore/L=Singapore
Client-SSL-Cipher: AES128-SHA
Client-SSL-Socket-Class: IO::Socket::SSL
Client-SSL-Warning: Peer certificate not verified
Set-Cookie: JSESSIONIDSSO=D1B02192FA838BA431435CC750D7311B; Path=/; Secure; HttpOnly
Set-Cookie: JSESSIONID=C6D239529ED53DBCC8041EC7FFFAA523; Path=/axl/; Secure; HttpOnly
SOAPAction: CUCM:DB ver=6.1
X-Frame-Options: SAMEORIGIN
<SOAP-ENV:Envelope SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"><SOAP-ENV:Header/><SOAP-ENV:Body><SOAP-ENV:Fault><faultcode>SOAP-ENV:Client</faultcode><faultstring>A syntax error has occurred.</faultstring><detail><axl:Error xmlns:axl="http://www.cisco.com/AXL/API/1.0"><axl:code>-201</axl:code><axl:message>A syntax error has occurred.</axl:message><request>executeSQLQuery</request></axl:Error></detail></SOAP-ENV:Fault></SOAP-ENV:Body></SOAP-ENV:Envelope>
FAULTCODE: SOAP-ENV:Client, FAULTSTRING: A syntax error has occurred.netops@Agilent-Test-Server:~$
******************************************************************************************************************
#!/usr/bin/perl
use strict;
use warnings;
use SOAP::Lite +trace => 'debug';
use Data::Dumper;
use MIME::Base64;
use Text::CSV;
my $axl_port = "8443";
my $cucmip = "X.X.X.X";
my $user = "XXXXX";
my $password = "XXXXXXX";
my $filename = "users.csv";
#disable certificate check
$ENV{PERL_LWP_SSL_VERIFY_HOSTNAME} = 0;
# Make sure the file opens
open(INPUT, $filename) or die "Cannot open $filename";
# Read the header line.
my $line = <INPUT>;
# Read the lines one by one.
while($line = <INPUT>)
{
chomp($line);
my ($devname, $owner) = split(',', $line);
print "$devname zzz $owner\n";
#set up soap
my $cm = new SOAP::Lite
encodingStyle => '',
uri => 'http://www.cisco.com/AXL/API/1.0',
proxy => "https://$cucmip:$axl_port/axl/";
#authenticate to CUCM
$cm = Login($cm, $user, $password);
#axl request
my $sql = "sql update Device set fkenduser =(select pkid from EndUser where userid = '$owner') where name = '$devname' ";
my $axldata = SOAP::Data->name("sql" => $sql);
my $res = $cm->executeSQLQuery($axldata);
unless ($res->fault) {
print Dumper($res->paramsall());
} else {
print join ', ',
"FAULTCODE: " . $res->faultcode,
"FAULTSTRING: " . $res->faultstring;
}
################################################
sub Login {
$cm->transport->http_request->header('Authorization' => 'Basic ' . encode_base64("$user:$password", ''));
return $cm;
}
}
close(INPUT);
05-12-2015 09:37 AM
At first glance, I think the problem is that your Perl script isn't embedding the quotes around the name. What you're sending is this:
where name = SEP00270D3DAD16
What you should be sending is this:
where name = "SEP00270D3DAD16"
I'm not a Perl programmer, but I assume it allows you to escape quotes like most other languages. For example,
where name = '\"$devname'\" ";
You probably need to do the same thing for userid, too. It is also not quoted properly in the Perl.
05-13-2015 03:44 AM
Hi Nicholas,
Thanks for response. Not so sure how ' ' was missed in my earlier output.
This is how, I noticed output with present script, end up in syntax error, but works via Call manager CLI mode.
sql update Device set fkenduser =(select pkid from EndUser where userid = 'GXL-Site-PNG-1') where name = 'SEPACA0166EBF5B'
When I used your suggestion, query was firing as below :
userid = '"GXL-Site-PNG-1'") where name = '"SEP58971E28F867'" which again ends up in syntax error.
Thanks,
Renga.
05-13-2015 04:34 AM
Hello,
I have probably found what causing this issue. AXL -->executeSQLQuery is used only sql select statements, and no more its going to work for database update. Hence we suppose to use executeSQLUpdate for the same. I have changed accordingly but error is still same. Not so sure, what I am missing.
CUCM verison 9.1
Updated script :
---------------------------
#!/usr/bin/perl
use strict;
use warnings;
use SOAP::Lite +trace => 'debug';
use Data::Dumper;
use MIME::Base64;
use Text::CSV;
my $axl_port = "8443";
my $cucmip = "X.X.X.X";
my $user = "axltest";
my $password = "axltest";
my $filename = "users.csv";
#disable certificate check
$ENV{PERL_LWP_SSL_VERIFY_HOSTNAME} = 0;
# Make sure the file opens
open(INPUT, $filename) or die "Cannot open $filename";
# Read the header line.
my $line = <INPUT>;
# Read the lines one by one.
while($line = <INPUT>)
{
chomp($line);
my ($devname, $owner) = split(',', $line);
print "$devname zzz $owner\n";
#set up soap
my $cm = new SOAP::Lite
encodingStyle => '',
uri => 'http://www.cisco.com/AXL/API/1.0',
proxy => "https://$cucmip:$axl_port/axl/";
#authenticate to CUCM
$cm = Login($cm, $user, $password);
#axl request
my $sql = "sql update Device set fkenduser =(select pkid from EndUser where userid = '$owner') where name = '$devname'";
my $axldata = SOAP::Data->name("sql" => $sql);
my $res = $cm->executeSQLUpdate($axldata);
unless ($res->fault) {
print Dumper($res->paramsall());
} else {
print join ', ',
"FAULTCODE: " . $res->faultcode,
"FAULTSTRING: " . $res->faultstring;
}
#Loop closure braclet
################################################
sub Login {
$cm->transport->http_request->header('Authorization' => 'Basic ' . encode_base64("$user:$password", ''));
return $cm;
}
}
close(INPUT);
Thanks,
Renga.
05-14-2015 08:20 AM
userid = '"GXL-Site-PNG-1'") where name = '"SEP58971E28F867'" which again ends up in syntax error
That's because now you have too many quotes. What you want is:
userid = 'GXL-Site-PNG-1') where name = 'SEP58971E28F867'
Whether you end up with single or double quotes shouldn't make a difference, but you need only one or the other.
Yes, you need to use executeSQLUpdate for updates. Sorry I didn't notice that right away.
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: