Update owner userid for Phone not working via Perl

[agilent runprojects]

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);

[npetrele]

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.

[agilent runprojects]

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.

[agilent runprojects]

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.

[npetrele]

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.