cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
5649
Views
0
Helpful
4
Replies

SQL Query via AXL on CUCM 11.5

alan.giadosh1
Level 1
Level 1

Hello, when I execute a SQL query via AXL API on CUCM 11.5 using python 3.x HEX values are returned , is there documentation somewhere on how to retrieve the values?  I like to use the API not CLI method

 

the CLI method works as expected(the SQL gives configured counts per Device Pool)

 

SQL statement: 

select count(d.name), dp.name as DevicePool from Device as d inner join DevicePool as dp on d.fkDevicePool=dp.pkid group by dp.name

 

via cli result:


(count) devicepool
======= ==========
35 Default

 

VIA AXL:

via axl [<Element count at 0x1a9c58bae00>, <Element devicepool at 0x1a9c58bad80>]

 

thanks

 

 

4 Replies 4

dstaudt
Cisco Employee
Cisco Employee

Looks like the hex values are pointers to Python objects.  Zeep returns structured Python objects, which you'll need to parse down into to get the actual values from.

E.g.:

 

# Create an object containing the raw SQL query to run
sql = '''select numplan.dnorpattern from numplan, pickupgrouplinemap, pickupgroup
            where numplan.pkid = pickupgrouplinemap.fknumplan_line and
            pickupgrouplinemap.fkpickupgroup = pickupgroup.pkid and
            pickupgroup.name = "testCallPickupGroup"'''

# Execute the executeSQLQuery request
try:
    resp = service.executeSQLQuery( sql )
except Fault as err:
    print('Zeep error: executeSQLQuery: {err}'.format( err = err ) )
else:
    print( '\nexecuteSQLQuery response:' )
    print( resp )

input( 'Press Enter to continue...' )


# Create a simple report of the SQL response
print( 'Directory Numbers belonging to testCallPickupGroup' )
print( '==================================================')

for rowXml in resp[ 'return' ][ 'row' ]:

    z = rowXml[ 0 ].text
    print( z )

From this sample/repo: https://github.com/CiscoDevNet/axl-python-zeep-samples/blob/master/axl_executeSQLQuery.py

alan.giadosh1
Level 1
Level 1

Okay this make sense, what I didn't mention was I not building this from scratch, I was using  was Jeff's L 'ciscoaxl' pre built python setup 'https://pypi.org/project/ciscoaxl/' to send this SQL via AXL so it appears he didn't parse this out, so do you recommend I just hand build for SQL queries or Im leaning to modify his axl.py so I dont have all the zeep in my code.

I haven't used that library myself, but I'm assuming you'll probably end up with the same Python object returned.  The best way to investigate the structure of the object and test how to parse it is by running the code in a debugger/IDE - I use Visual Studio Code for that.  You can take a look at the variable/data returned by the call, drill down into the lists/fields, and try parsing commands in the watch or debug windows...all of that is pretty 'developer-y', but are skills you will likely need if you will be working with Python code in future.

A couple of learning labs re Python/JSON parsing:

https://developer.cisco.com/learning/lab/04-rest-04-coding-python-json/step/1

https://developer.cisco.com/learning/labs/parsing-json/step/1

alan.giadosh1
Level 1
Level 1

here is the solution, this works best for me as a busy engineer to not learn all the zeep and python to work with CUCM automation.  

 

try:
# get all the configured devices per DP using Jeff L AXL.py
dpCounts = ucm.sql_query('select count(d.name), '
'dp.name as DevicePool from Device as d'
' inner join DevicePool as dp on d.fkDevicePool=dp.pkid '
'group by dp.name')

# convert dictionary (dpCounts) to text
dpDict = {}
for item in dpCounts['row']:
print(item[0].text, item[1].text) # print the DP counts and Device pool name to screen
dpDict[str(item[1].text)]=str(item[0].text) # save in a new dictionary to reference later

Getting Started

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: