SQL Query via AXL on CUCM 11.5

[alan.giadosh1]

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

[dstaudt]

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]

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.

[dstaudt]

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]

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