cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
997
Views
4
Helpful
14
Replies

Need help pulling detailed information of ports on vg350 using AXL

dhoskins
Level 1
Level 1

I'm in the process of developing a Python script using zeep aimed at extracting comprehensive data from all analog-programmed ports on a VG350. This data extraction includes not only the device details but also the directory number information linked to each programmed port. My objective is to seamlessly transfer this data into an Excel file, which will then be utilized for configuring our newly acquired VG450.


Despite dedicating significant effort towards scripting this functionality in Python, I've encountered some roadblocks and am yet to achieve the desired outcome. One concern is whether the AXL Api possesses the complete range of schemas necessary to retrieve the entirety of this information.


I would greatly value any guidance or assistance in this matter, as my attempts so far have been quite challenging, consuming much of my day without yielding progress.

We are using CUCM 12.5

1 Accepted Solution

Accepted Solutions

The Configure VG244 Ports white paper in the AXL docs area has some background on this area - it is pretty ancient, but still relevant: https://developer.cisco.com/docs/axl/#!what-is-axl/what-is-administrative-xml

I believe you can get all of the details from a sequence simlar to the AXL requests shown below.  The tricky step is that apparently there is no good way to find out which endpoints are associated with a MGCP gateway using standard AXL requests, so we have to resort to SQL:

1. Get the MGCP gateway details, searching by domainName

 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:getGateway sequence="1">
         <domainName>example.com</domainName>
      </ns:getGateway>
   </soapenv:Body>
</soapenv:Envelope>
----------------------
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <ns:getGatewayResponse xmlns:ns="http://www.cisco.com/AXL/API/14.0">
         <return>
            <gateway uuid="{0630F965-0F35-8C26-006F-902B8CD78DCD}">
               <domainName>example.com</domainName>
               <description>example.com</description>
               <product>VG320</product>
               <protocol>MGCP</protocol>
               <callManagerGroupName uuid="{D13C4201-7802-11D3-BDF0-00108302EAD1}">Default</callManagerGroupName>
               <units>
                  <unit>
                     <index>0</index>
                     <product>VG-3VWIC-MBRD</product>
                     <subunits>
                        <subunit>
                           <index>0</index>
                           <product>24FXS</product>
                           <beginPort>0</beginPort>
                        </subunit>
                        <subunit>
                           <index>1</index>
                           <product>24FXS</product>
                           <beginPort>0</beginPort>
                        </subunit>
                     </subunits>
                  </unit>
               </units>
               <scratch/>
               <vendorConfig>
                  <globalISDNSwitchType>4ESS</globalISDNSwitchType>
                  <switchBack>Graceful</switchBack>
                  <switchBackDelay>10</switchBackDelay>
                  <switchBackSchedule>12:00</switchBackSchedule>
                  <DtmfRelay>NoChange</DtmfRelay>
...

 

2. Execute a SQL query to get the endpoints associated to the gateway - the fkmgcp is the uuid (removing braces) from #1

 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:executeSQLQuery sequence="1">
         <sql>select * from mgcpdevicemember where fkmgcp="0630f965-0f35-8c26-006f-902b8cd78dcd"</sql>
      </ns:executeSQLQuery>
   </soapenv:Body>
</soapenv:Envelope>
---------------------
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <ns:executeSQLQueryResponse xmlns:ns="http://www.cisco.com/AXL/API/14.0">
         <return>
            <row>
               <pkid>0d35bd8e-eeae-e35a-94cd-5a79702bcd90</pkid>
               <fkmgcp>0630f965-0f35-8c26-006f-902b8cd78dcd</fkmgcp>
               <fkdevice>7078d70b-e151-1ed2-5351-a3faed4376f0</fkdevice>
               <slot>0</slot>
               <subunit>1</subunit>
               <port>0</port>
            </row>
            <row>
               <pkid>e85fc8e4-04de-dbcf-f94c-bc2c24f35704</pkid>
               <fkmgcp>0630f965-0f35-8c26-006f-902b8cd78dcd</fkmgcp>
               <fkdevice>d2ea2a9c-bd7e-fada-1fe0-c7c73f7861a1</fkdevice>
               <slot>0</slot>
               <subunit>1</subunit>
               <port>1</port>
            </row>
            <row>
               <pkid>27924749-bb39-1af9-ed63-638ffe9c5501</pkid>
               <fkmgcp>0630f965-0f35-8c26-006f-902b8cd78dcd</fkmgcp>
               <fkdevice>da64389b-0956-89c3-a80f-1d68d11af356</fkdevice>
               <slot>0</slot>
               <subunit>0</subunit>
               <port>0</port>
            </row>
            <row>
               <pkid>c517fc57-14b9-79d2-58ec-f660648ee8ed</pkid>
               <fkmgcp>0630f965-0f35-8c26-006f-902b8cd78dcd</fkmgcp>
               <fkdevice>b871cf58-2a3b-a5cf-ba42-4f40135e3a0e</fkdevice>
               <slot>0</slot>
               <subunit>0</subunit>
               <port>1</port>
            </row>
         </return>
      </ns:executeSQLQueryResponse>
   </soapenv:Body>
</soapenv:Envelope>

 

3. Get the endpoint analog access details, uuid is fkdevice from #2

 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:getGatewayEndpointAnalogAccess sequence="1">
         <uuid>{7078d70b-e151-1ed2-5351-a3faed4376f0}</uuid>
      </ns:getGatewayEndpointAnalogAccess>
   </soapenv:Body>
</soapenv:Envelope>
---------------------
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <ns:getGatewayEndpointAnalogAccessResponse xmlns:ns="http://www.cisco.com/AXL/API/14.0">
         <return>
            <gatewayEndpointAnalogAccess uuid="{7078D70B-E151-1ED2-5351-A3FAED4376F0}">
               <domainName>example.com</domainName>
               <unit>0</unit>
               <subunit>1</subunit>
               <endpoint ctiid="190" uuid="{7078D70B-E151-1ED2-5351-A3FAED4376F0}">
                  <index>0</index>
                  <name>AALN/S0/SU1/0@example.com</name>
                  <description>AALN/S0/SU1/0@example.com</description>
                  <product>Cisco MGCP FXS Port</product>
                  <model>MGCP Station</model>
                  <class>Gateway</class>
                  <protocol>Analog Access</protocol>
                  <protocolSide>User</protocolSide>
                  <callingSearchSpaceName/>
                  <devicePoolName uuid="{1B1B9EB6-7803-11D3-BDF0-00108302EAD1}">Default</devicePoolName>
                  <commonDeviceConfigName/>
                  <networkLocale/>
                  <locationName uuid="{29C5C1C4-8871-4D1E-8394-0B9181E8C54D}">Hub_None</locationName>
                  <mediaResourceListName/>
                  <automatedAlternateRoutingCssName/>
                  <aarNeighborhoodName/>
                  <vendorConfig/>
                  <mlppDomainId/>
                  <useTrustedRelayPoint>Default</useTrustedRelayPoint>
                  <retryVideoCallAsAudio>true</retryVideoCallAsAudio>
                  <cgpnTransformationCssName/>
                  <useDevicePoolCgpnTransformCss>true</useDevicePoolCgpnTransformCss>
                  <geoLocationName/>
                  <geoLocationFilterName/>
                  <port>
                     <portNumber>1</portNumber>
                     <attendantDn/>
                     <unattendedPort>false</unattendedPort>
                     <callerIdDn/>
                     <callerIdEnable>false</callerIdEnable>
                     <callingPartySelection>Originator</callingPartySelection>
                     <digitSending>DTMF</digitSending>
                     <expectedDigits>0</expectedDigits>
                     <sigDigits enable="false">0</sigDigits>
                     <lines>
                        <line>
                           <index>1</index>
                           <label/>
                           <display/>
                           <dirn uuid="{D74FE96D-FC0C-3A37-A3D6-A29235867181}">
                              <pattern>9992</pattern>
                              <routePartitionName/>
                           </dirn>
                           <ringSetting>Ring</ringSetting>
                           <consecutiveRingSetting>Use System Default</consecutiveRingSetting>
...

 

as there are 4 entries in#2, you'll do this 4 times, once for each fkdevice

View solution in original post

14 Replies 14

npetrele
Cisco Employee
Cisco Employee

Could you provide a little more information?  Specifically, what AXL API call are you using?  In addition, if you could post the actual SOAP query line of code that would help. 

dhoskins
Level 1
Level 1

Let me first start off by saying I'm a beginner when it comes to writing Python code. I don't know the exact Axl API calls I should be using to do the task I'm trying to achieve or the Python code that will do it. I did a query on getting the gateway info, but it returns very limited information and not the information I'm hoping to export.

from zeep import Client, Settings
from zeep.transports import Transport
from requests import Session
from requests.auth import HTTPBasicAuth
import urllib3

# Disable the InsecureRequestWarning
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

# Define AXL settings and your CUCM credentials
USERNAME = "administrator"
PASSWORD = "ciscopsdt"
WSDL_PATH = "C:\\Users\\ddhnht\\Downloads\\axlsqltoolkit\\schema\\current\\axlapi.wsdl"

session = Session()
session.verify = False
session.auth = HTTPBasicAuth(USERNAME, PASSWORD)

transport = Transport(session=session, timeout=10)
settings = Settings(strict=False, xml_huge_tree=True)

client = Client(WSDL_PATH, transport=transport, settings=settings)

def get_gateways_info():
service = client.create_service("{http://www.cisco.com/AXLAPIService/}AXLAPIBinding", "https://10.10.20.1/axl/")

# Using listGateway to retrieve all gateways
response = service.listGateway(searchCriteria={'domainName': '%'}, returnedTags={'domainName': ''})
gateways = response['return']['gateway']

# Extracting the names of the gateways
gateway_names = [gateway['domainName'] for gateway in gateways]

return len(gateways), gateway_names

 


if __name__ == "__main__":
try:
gateway_count, gateway_names = get_gateways_info()
print(f"Total Number of Gateways: {gateway_count}")
print("Names of the Gateways:")
for name in gateway_names:
print(name)
except Exception as e:
print(f"Error: {e}")
sys.exit(1)

input("Press Enter to exit...")

 

 

npetrele
Cisco Employee
Cisco Employee

Thanks, that's great information. I don't think listGateway is going to get you the information you want. Unfortunately for me, I had to refresh my work PC, so I need to install some things to investigate further. I'll get back to you tomorrow, hopefully with useful information. 

Thanks appreciate you.

npetrele
Cisco Employee
Cisco Employee

In the meantime, I recommend you download and install SoapUI (www.soapui.org). Download the open source version, it's free. It's a great way to test AXL and Serviceability operations before trying to code them in Python. 

I have SoapUI. I’ve played with it a little.

How is the gateway configured? If it is MGCP then you could pull all the information from CUCM. The real power of python is all the libraries that exist so you (almost) never have to build anything from the ground up. I am sure there are lots of others, but a quick Google search for "cisco router python get config" turned up this amond other things.

https://github.com/hoelsner/python-script-examples/blob/master/cisco-ios-config-parameter-extraction/ciscoconfiparse_example.py 

It is configured as MGCP, I couldn't find anywhere in CUCM that would allow me to export out all the configured ports and their configurations.

Are you familiar with SQL?  Here's where to get the database schema: https://developer.cisco.com/docs/axl/#!14-cucm-data-dictionary

Go to SoapUI and run this:

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

Or include a "where" clause for your specific gateway. From there, you can grab the pkid for the mgcp gateway and run a query like this:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:executeSQLQuery sequence="?">
         <sql>select * from mgcpdevicemember where fkmgcp="d9555d37-600e-bceb-07c0-60993635ad6f"</sql>
      </ns:executeSQLQuery>
   </soapenv:Body>
</soapenv:Envelope>

I'm not all that familiar with gateways, so I don't know if that gets you the info you want, but it should get you started. 

It shows the two ports that I configured on the gateway but doesn't show all the information on the ports I configured like the device information and the directory number information.

 

<pkid>0ad606f7-0129-cde1-9fd4-9acebfbfe07c</pkid>
<fkmgcp>1de25e22-dd0c-97a6-6f65-d24729b92cd9</fkmgcp>
<fkdevice>742a5c3d-3285-3ea9-b375-6eece1ba53e8</fkdevice>
<slot>2</slot>
<subunit>0</subunit>
<port>28</port>
</row>
<row>
<pkid>5f4535b5-8270-f956-af54-1dec69aed36b</pkid>
<fkmgcp>1de25e22-dd0c-97a6-6f65-d24729b92cd9</fkmgcp>
<fkdevice>f6d6cbe5-d79c-ed2e-a2e4-c0c09b81e49f</fkdevice>
<slot>2</slot>
<subunit>0</subunit>
<port>38</port>
</row>
</return>
</ns:executeSQLQueryResponse>
</soapenv:Body>
</soapenv:Envelope>

It's showing you the fkdevice key.  It's like following a trail of bread crumbs, I know, but then you get info from devicenumplanmap

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:executeSQLQuery sequence="?">
         <sql>select * from devicenumplanmap where fkdevice="f6d6cbe5-d79c-ed2e-a2e4-c0c09b81e49f"</sql>
      </ns:executeSQLQuery>
   </soapenv:Body>
</soapenv:Envelope>

That will give you the fknumplan id. Then use that to get the numplan info, which shows you the info you need. 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:executeSQLQuery sequence="?">
         <sql>select * from numplan where pkid="<the fknumplan id from previous query>"</sql>
      </ns:executeSQLQuery>
   </soapenv:Body>
</soapenv:Envelope>

 Of course, you can build a complex query over multiple tables to dive directly into the numplan info, but that's up to you. 

The Configure VG244 Ports white paper in the AXL docs area has some background on this area - it is pretty ancient, but still relevant: https://developer.cisco.com/docs/axl/#!what-is-axl/what-is-administrative-xml

I believe you can get all of the details from a sequence simlar to the AXL requests shown below.  The tricky step is that apparently there is no good way to find out which endpoints are associated with a MGCP gateway using standard AXL requests, so we have to resort to SQL:

1. Get the MGCP gateway details, searching by domainName

 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:getGateway sequence="1">
         <domainName>example.com</domainName>
      </ns:getGateway>
   </soapenv:Body>
</soapenv:Envelope>
----------------------
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <ns:getGatewayResponse xmlns:ns="http://www.cisco.com/AXL/API/14.0">
         <return>
            <gateway uuid="{0630F965-0F35-8C26-006F-902B8CD78DCD}">
               <domainName>example.com</domainName>
               <description>example.com</description>
               <product>VG320</product>
               <protocol>MGCP</protocol>
               <callManagerGroupName uuid="{D13C4201-7802-11D3-BDF0-00108302EAD1}">Default</callManagerGroupName>
               <units>
                  <unit>
                     <index>0</index>
                     <product>VG-3VWIC-MBRD</product>
                     <subunits>
                        <subunit>
                           <index>0</index>
                           <product>24FXS</product>
                           <beginPort>0</beginPort>
                        </subunit>
                        <subunit>
                           <index>1</index>
                           <product>24FXS</product>
                           <beginPort>0</beginPort>
                        </subunit>
                     </subunits>
                  </unit>
               </units>
               <scratch/>
               <vendorConfig>
                  <globalISDNSwitchType>4ESS</globalISDNSwitchType>
                  <switchBack>Graceful</switchBack>
                  <switchBackDelay>10</switchBackDelay>
                  <switchBackSchedule>12:00</switchBackSchedule>
                  <DtmfRelay>NoChange</DtmfRelay>
...

 

2. Execute a SQL query to get the endpoints associated to the gateway - the fkmgcp is the uuid (removing braces) from #1

 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:executeSQLQuery sequence="1">
         <sql>select * from mgcpdevicemember where fkmgcp="0630f965-0f35-8c26-006f-902b8cd78dcd"</sql>
      </ns:executeSQLQuery>
   </soapenv:Body>
</soapenv:Envelope>
---------------------
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <ns:executeSQLQueryResponse xmlns:ns="http://www.cisco.com/AXL/API/14.0">
         <return>
            <row>
               <pkid>0d35bd8e-eeae-e35a-94cd-5a79702bcd90</pkid>
               <fkmgcp>0630f965-0f35-8c26-006f-902b8cd78dcd</fkmgcp>
               <fkdevice>7078d70b-e151-1ed2-5351-a3faed4376f0</fkdevice>
               <slot>0</slot>
               <subunit>1</subunit>
               <port>0</port>
            </row>
            <row>
               <pkid>e85fc8e4-04de-dbcf-f94c-bc2c24f35704</pkid>
               <fkmgcp>0630f965-0f35-8c26-006f-902b8cd78dcd</fkmgcp>
               <fkdevice>d2ea2a9c-bd7e-fada-1fe0-c7c73f7861a1</fkdevice>
               <slot>0</slot>
               <subunit>1</subunit>
               <port>1</port>
            </row>
            <row>
               <pkid>27924749-bb39-1af9-ed63-638ffe9c5501</pkid>
               <fkmgcp>0630f965-0f35-8c26-006f-902b8cd78dcd</fkmgcp>
               <fkdevice>da64389b-0956-89c3-a80f-1d68d11af356</fkdevice>
               <slot>0</slot>
               <subunit>0</subunit>
               <port>0</port>
            </row>
            <row>
               <pkid>c517fc57-14b9-79d2-58ec-f660648ee8ed</pkid>
               <fkmgcp>0630f965-0f35-8c26-006f-902b8cd78dcd</fkmgcp>
               <fkdevice>b871cf58-2a3b-a5cf-ba42-4f40135e3a0e</fkdevice>
               <slot>0</slot>
               <subunit>0</subunit>
               <port>1</port>
            </row>
         </return>
      </ns:executeSQLQueryResponse>
   </soapenv:Body>
</soapenv:Envelope>

 

3. Get the endpoint analog access details, uuid is fkdevice from #2

 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:getGatewayEndpointAnalogAccess sequence="1">
         <uuid>{7078d70b-e151-1ed2-5351-a3faed4376f0}</uuid>
      </ns:getGatewayEndpointAnalogAccess>
   </soapenv:Body>
</soapenv:Envelope>
---------------------
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <ns:getGatewayEndpointAnalogAccessResponse xmlns:ns="http://www.cisco.com/AXL/API/14.0">
         <return>
            <gatewayEndpointAnalogAccess uuid="{7078D70B-E151-1ED2-5351-A3FAED4376F0}">
               <domainName>example.com</domainName>
               <unit>0</unit>
               <subunit>1</subunit>
               <endpoint ctiid="190" uuid="{7078D70B-E151-1ED2-5351-A3FAED4376F0}">
                  <index>0</index>
                  <name>AALN/S0/SU1/0@example.com</name>
                  <description>AALN/S0/SU1/0@example.com</description>
                  <product>Cisco MGCP FXS Port</product>
                  <model>MGCP Station</model>
                  <class>Gateway</class>
                  <protocol>Analog Access</protocol>
                  <protocolSide>User</protocolSide>
                  <callingSearchSpaceName/>
                  <devicePoolName uuid="{1B1B9EB6-7803-11D3-BDF0-00108302EAD1}">Default</devicePoolName>
                  <commonDeviceConfigName/>
                  <networkLocale/>
                  <locationName uuid="{29C5C1C4-8871-4D1E-8394-0B9181E8C54D}">Hub_None</locationName>
                  <mediaResourceListName/>
                  <automatedAlternateRoutingCssName/>
                  <aarNeighborhoodName/>
                  <vendorConfig/>
                  <mlppDomainId/>
                  <useTrustedRelayPoint>Default</useTrustedRelayPoint>
                  <retryVideoCallAsAudio>true</retryVideoCallAsAudio>
                  <cgpnTransformationCssName/>
                  <useDevicePoolCgpnTransformCss>true</useDevicePoolCgpnTransformCss>
                  <geoLocationName/>
                  <geoLocationFilterName/>
                  <port>
                     <portNumber>1</portNumber>
                     <attendantDn/>
                     <unattendedPort>false</unattendedPort>
                     <callerIdDn/>
                     <callerIdEnable>false</callerIdEnable>
                     <callingPartySelection>Originator</callingPartySelection>
                     <digitSending>DTMF</digitSending>
                     <expectedDigits>0</expectedDigits>
                     <sigDigits enable="false">0</sigDigits>
                     <lines>
                        <line>
                           <index>1</index>
                           <label/>
                           <display/>
                           <dirn uuid="{D74FE96D-FC0C-3A37-A3D6-A29235867181}">
                              <pattern>9992</pattern>
                              <routePartitionName/>
                           </dirn>
                           <ringSetting>Ring</ringSetting>
                           <consecutiveRingSetting>Use System Default</consecutiveRingSetting>
...

 

as there are 4 entries in#2, you'll do this 4 times, once for each fkdevice

npetrele
Cisco Employee
Cisco Employee

I'd obviously go with David Staudt's response. I may be a SQL guy but I have very little experience with gateways. He's the expert here!

sjdamme
Level 1
Level 1

You can roll this all into one (admittedly complicated) SQL query instead of having to do multiple steps.

SELECT gw.domainname, gw.description as gwdescription, mgcpdm.slot, mgcpdm.subunit, mgcpdm.port, d.name as devicename, d.description as devicedescription, dnorpattern as dn, n.description as dndescription, dmap.e164mask as mask
FROM mgcpdevicemember mgcpdm
INNER JOIN mgcp as gw on gw.pkid=mgcpdm.fkmgcp
INNER JOIN device as d on d.pkid=mgcpdm.fkdevice
INNER JOIN devicenumplanmap as dmap on dmap.fkdevice=d.pkid
INNER JOIN numplan as n on dmap.fknumplan=n.pkid

Leave it like that to get information on all the ports of all the gateways configured in CUCM. If you want to narrow it down to just one, add this at the end:

WHERE  gw.domainname='gateway.domain.name'

Put the domain name of your gateway in place of gateway.domain.name, obviously. 

Grab the output, save it as an XML file, then import the XML into Excel and you can sort and manipulate the data as your heart desires.