Run SQL query at CUCM via API python script. Zeep and Suds

MikhailChernyakov9628 — Thu, 18 Mar 2021 10:06:04 GMT

Hello!

We widely use python script for automation CUCM administration task

We used SUDS - SOAP python client library for make request

For example it works fine for SQL query

query ='select count(*) from enduser'

resp = client.service.executeSQLQuery(query)

But SUDS old project and several function does not work with new CUCM

I try to use more efficient ZEEP client - it works fine

(Zeep and SUDS very simply)

But sql query did not work

For Simple Query following received

query_3 = "select count(*) from enduser"

numplan = service.executeSQLQuery(query_3)
print(numplan)
{
'return': {
'row': [
[
<Element count at 0x18a056e7080>
]
]
},
'sequence': None

For more sophisticated query (what works fine via soap GUI client) syntax error received

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
<soapenv:Fault>
<faultcode>soapenv:Client</faultcode>
<faultstring>A syntax error has occurred.</faultstring>
<detail>
<axlError>
<axlcode>-201</axlcode>
<axlmessage>A syntax error has occurred.</axlmessage>
<request>executeSQLQuery</request>
</axlError>
</detail>
</soapenv:Fault>
</soapenv:Body>
</soapenv:Envelope>

Can any advice what wrong at request?

Or may be another path to run query

Re: Run SQL query at CUCM via API python script. Zeep and Suds

dstaudt — Thu, 18 Mar 2021 16:21:15 GMT

This repo includes several AXL/Python/Zeep samples including some covering <executeSqlQuery>: https://github.com/CiscoDevNet/axl-python-zeep-samples

If the problem seems to be the syntax of the SQL itself, you can post it here and someone may be able to spot an issue...

Re: Run SQL query at CUCM via API python script. Zeep and Suds

MikhailChernyakov9628 — Fri, 19 Mar 2021 05:16:20 GMT

>https://github.com/CiscoDevNet/axl-python-zeep-samples

Yes, I used exact this samples...

SQL query =

SELECT EU.pkid, EU.userid userid, EU.firstname firstname, EU.lastname lastname, LRU.snr snrenabled, (LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END) + (CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0  ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) + LRU.silver+LRU.gold+LRU.unknowndevice) devicecount, (CASE WHEN (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)+(GREATEST((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) - (LRU.tin + LRU.copper),0)) > 2) OR (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)+(GREATEST((CASE  WHEN  LRU.adjunct = (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) - (LRU.tin + LRU.copper),0)) >= 1)   AND (LRU.silver >= 1) AND (LRU.gold=0)))  THEN 'CUWL Standard' ELSE (CASE WHEN (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)+(GREATEST((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0   ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) - (LRU.tin + LRU.copper),0)) = 2) AND (LRU.silver = 0) AND (LRU.gold=0))  THEN 'Enhanced Plus' ELSE (CASE WHEN ((((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)=1) AND (((CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)=1) OR  (LRU.unknowndevice=1)) AND ((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 0)) OR (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)=1) AND ((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 1) AND (LRU.tin+LRU.copper=1)) OR (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice) = 0) AND ((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 1))) THEN 'Enhanced' ELSE (CASE  WHEN ((((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)=1) AND ((CASE WHEN LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 0) AND ((LRU.copper= 1)OR ((LRU.tin=1) AND (LRU.snr='t')))) OR (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice) = 0) AND ((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 0) AND (LRU.snr='t')))THEN 'Basic' ELSE (CASE WHEN (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)=1) AND ((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 0) AND (LRU.tin= 1) AND (LRU.snr='f')) THEN 'Essential'END) END) END) END) END)      licensetype, (LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+(CASE  WHEN  LRU.adjunct &lt;= (LRU.tin+LRU.copper + GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (LRU.tin+LRU.copper + GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END)+LRU.silver+LRU.gold+LRU.unknowndevice) licenses FROM LicensingResourceUsage LRU, EndUser EU WHERE LRU.fkenduser IS NOT NULL AND EU.pkid=LRU.fkenduser AND(((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+(CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END)+LRU.silver+LRU.gold+LRU.unknowndevice) > 0) OR (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+(CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END)+LRU.silver+LRU.gold+LRU.unknowndevice) = 0) AND (LRU.snr = 't'))) AND ((my_lower(firstname::lvarchar) LIKE my_lower('%')  OR firstname IS NULL OR my_lower(firstname::lvarchar) = '')) UNION SELECT EU.pkid, EU.userid userid, EU.firstname firstname, EU.lastname lastname, LRU.snr snrenabled,((CASE WHEN LRU.Telepresence>=LRU.Bronze THEN LRU.Bronze ELSE LRU.Telepresence END) + LRU.telepresence) devicecount, (CASE WHEN (LRU.telepresence>0) THEN 'TelePresence Room' END) licensetype, (LRU.telepresence) licenses FROM LicensingResourceUsage LRU, EndUser EU WHERE LRU.telepresence > 0  AND LRU.fkenduser IS NOT NULL AND EU.pkid=LRU.fkenduser    AND ((my_lower(firstname::lvarchar) LIKE my_lower('%')  OR firstname IS NULL OR my_lower(firstname::lvarchar) = '')) ORDER BY  firstname

:)))

But I find quick solution...

First

Test request at SOAPUI

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/12.5">
<soapenv:Header/>
<soapenv:Body>
<ns:executeSQLQuery sequence="?">
<sql>QUERY</sql>
</ns:executeSQLQuery>
</soapenv:Body>
</soapenv:Envelope>

after what test it via Postman

If it works fine go to Code Snippet and select Python

and code is ready

import http.client

conn = http.client.HTTPSConnection("spb33-itsk-uc1.gazprom-neft.local", 8443)
payload = "<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:ns=\"http://www.cisco.com/AXL/API/12.5\">\r\n   <soapenv:Header/>\r\n   <soapenv:Body>\r\n      <ns:executeSQLQuery sequence=\"?\">\r\n         <sql>SELECT EU.pkid, EU.userid userid, EU.firstname firstname, EU.lastname lastname, LRU.snr snrenabled, (LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END) + (CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0  ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) + LRU.silver+LRU.gold+LRU.unknowndevice) devicecount, (CASE WHEN (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)+(GREATEST((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) - (LRU.tin + LRU.copper),0)) > 2) OR (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)+(GREATEST((CASE  WHEN  LRU.adjunct = (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) - (LRU.tin + LRU.copper),0)) >= 1)\tAND (LRU.silver >= 1) AND (LRU.gold=0)))  THEN 'CUWL Standard' ELSE (CASE WHEN (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)+(GREATEST((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0\tELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) - (LRU.tin + LRU.copper),0)) = 2)\tAND (LRU.silver = 0) AND (LRU.gold=0))  THEN 'Enhanced Plus' ELSE (CASE WHEN ((((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)=1) AND (((CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)=1) OR  (LRU.unknowndevice=1)) AND ((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 0)) OR (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)=1) AND ((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 1) AND (LRU.tin+LRU.copper=1)) OR (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice) = 0) AND ((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 1))) THEN 'Enhanced' ELSE (CASE\tWHEN ((((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)=1) AND ((CASE WHEN LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 0) AND ((LRU.copper= 1)OR ((LRU.tin=1) AND (LRU.snr='t')))) OR (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice) = 0) AND ((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 0) AND (LRU.snr='t')))THEN 'Basic' ELSE (CASE WHEN (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+LRU.silver+LRU.gold+LRU.unknowndevice)=1) AND ((CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END) = 0) AND (LRU.tin= 1) AND (LRU.snr='f')) THEN 'Essential'END) END) END) END) END) \t\tlicensetype, (LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+(CASE  WHEN  LRU.adjunct &lt;= (LRU.tin+LRU.copper + GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (LRU.tin+LRU.copper + GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END)+LRU.silver+LRU.gold+LRU.unknowndevice) licenses FROM LicensingResourceUsage LRU, EndUser EU WHERE LRU.fkenduser IS NOT NULL AND EU.pkid=LRU.fkenduser AND(((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+(CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END)+LRU.silver+LRU.gold+LRU.unknowndevice) > 0) OR (((LRU.tin+LRU.copper+(CASE WHEN LRU.Telepresence>LRU.Bronze THEN 0 ELSE LRU.Bronze-LRU.Telepresence END)+(CASE  WHEN  LRU.adjunct &lt;= (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) THEN 0 ELSE LRU.adjunct - (GREATEST(LRU.bronze, LRU.telepresence) + LRU.silver + LRU.gold) END)+LRU.silver+LRU.gold+LRU.unknowndevice) = 0) AND (LRU.snr = 't'))) AND ((my_lower(firstname::lvarchar) LIKE my_lower('%')  OR firstname IS NULL OR my_lower(firstname::lvarchar) = '')) UNION SELECT EU.pkid, EU.userid userid, EU.firstname firstname, EU.lastname lastname, LRU.snr snrenabled,((CASE WHEN LRU.Telepresence>=LRU.Bronze THEN LRU.Bronze ELSE LRU.Telepresence END) + LRU.telepresence) devicecount, (CASE WHEN (LRU.telepresence>0) THEN 'TelePresence Room' END) licensetype, (LRU.telepresence) licenses FROM LicensingResourceUsage LRU, EndUser EU WHERE LRU.telepresence > 0\tAND LRU.fkenduser IS NOT NULL AND EU.pkid=LRU.fkenduser\tAND ((my_lower(firstname::lvarchar) LIKE my_lower('%')  OR firstname IS NULL OR my_lower(firstname::lvarchar) = '')) ORDER BY  firstname</sql>\r\n      </ns:executeSQLQuery>\r\n   </soapenv:Body>\r\n</soapenv:Envelope>"
headers = {
  'Authorization': 'Basic QVhMX0FQSTpRV0UxMjNhc2Q=',
  'Content-Type': 'text/plain',
  'Cookie': 'JSESSIONID=41CA30427EF876F9E0FEE634448A4EDE; JSESSIONIDSSO=EA4DBFA8CBE258A89A5D1F8C117A1495'
}
conn.request("POST", "/axl/", payload, headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))

topic Re: Run SQL query at CUCM via API python script. Zeep and Suds in Call Control

Run SQL query at CUCM via API python script. Zeep and Suds

Re: Run SQL query at CUCM via API python script. Zeep and Suds

Re: Run SQL query at CUCM via API python script. Zeep and Suds