Wednesday, October 28, 2015

Using SQL to Query SIP Trunks

This post is in response to a query I received on Twitter:
@ucguerrilla - Would you have a SQL query in your toolbox to list SIP trunks with ip address, or point me in the right direction?
This is an interesting question because the tables you need to look at may not be as obvious as seen with other queries where we need to join tables. So, let's take a look at what is involved with this query and possibly touch on some related queries.


Primer

A brief primer is provided in the first blog of this series.

Background

For this installment we are going to look at how we can generate a recordset that shows individual SIP trunks with the assigned SIP destination(s). Starting with CUCM version 8.5(1), a feature was added that allowed more than one SIP destination to be added to a single SIP trunk. This necessitated a database schema modification in order to preserve referential integrity and keep the database tables optimized. The updated schema introduced some new tables to the database, which we are going to need to answer the original question.

The queries provided in this entry are going to focus on UCM 10.5 but the queries should also work with UCM 8.5 and later. 
Moving Parts

The queries provided will leverage the following database tables:

  • Device: This table contains all of the information concerning devices provisioned on the system. Phones, gateways, trunks, CTI route points, media resources, and CTI ports are common devices. It is also worth noting that Route Lists and Hunt Lists are considered devices, too. 
  • Sipdevice: This table contains data specific to SIP trunk devices such as calling/called IE transformations, normalization script references, preferred codecs, QSIG parameters, and the like.
  • Siptrunkdestination: This table contains destination details for SIP trunk destinations associated with the entries in sipdevice



Example: Displaying SIP Trunks with Destinations

The original question was to provide a way to list all of the SIP trunks with their associated destinations. The basic device information is stored in the device table, as one would expect. This includes the fields you would see on the SIP trunk configuration page under the "Device Information" section (such as MRGL, CSS, etc.). 

We'll also need to use the sipdevice table, which contains data that is more specific to the SIP trunk parameters. The data in this table is similar to the data in the digitalaccesspri table used when querying MGCP trunks or the h323device table when querying H.323 gateways/trunks. For our basic query, we only use the sipdevice table to map our trunk device to the destination information stored in the siptrunkdestination table. 


The Query

A basic query that lists the device name, description, and destination information is provided below: 

select d.name as device, d.description, std.sortorder, std.address, std.port
from device d
    inner join sipdevice sd on sd.fkdevice=d.pkid
    inner join siptrunkdestination std on std.fksipdevice=sd.pkid
order by d.name, std.sortorder

The d.name field identifies the name of the trunk as provisioned in UCM. I like to pull descriptions (e.g. d.description) whenever I can just to provide some contextual information. Since there can be more than one destination, it is a good idea to pull the "sort order" field along with the address and port. The address will either be an IP address, FQDN, or SRV record. 

We are joining tables where there is a one-to-many relationship. Given that, it is a good idea to use the "order by" clause to ensure everything is presented in a deterministic way. 

Additional Information

When I query for SIP trunks, I also grab other information such as:

  • The calling/called IE parameters in the sipdevice table
  • MRGL and CSS information from the device table (with joins to other tables, as needed)
  • Information from the securityprofile table (SIP trunk security profile)
  • Information from the sipprofile table
You can see what is available in these tables by querying the systables and syscolumns system tables. I provide basic information on how to explore the system tables in this supplemental series entry on the Informix DB.




Thanks for reading. If you have time, post a comment!

6 comments:

  1. Hello,

    thanks for sharing this info about the SQL statements on CUCM.
    We're running version 10.5 and I'm trying to query the DB to obtain the following parameters: app user name, if is standar app user or not and the dir group name X (there're 6)

    I appreciate all your support.
    Thanks

    ReplyDelete
  2. How would one pull the following in with the above query as well? Looking to generate some Trunk Health reports.

    SIP Trunk Status
    SIP Trunk Duration

    Many thanks.
    Greg.

    ReplyDelete
    Replies
    1. Greg,

      AFAIK, that information is not stored in the Informix DB. You need to query RIS (Serviceability API) using the selectCMDevice call. If you are developing a script to collect this information then you could query UCM (AXL/SOAP) to collect the db config information and then query UCM-RIS (RISPort) to get the realtime status information.

      HTH.

      -Bill

      Delete
  3. Hello, thank you again for sharing this information. I am able to query all of my SIP trunks using a combination of the above example and an internal source document. My question is, how would you take that information and create a sql update to delete those trunks it found.

    Thank you for any information you have on this.

    ReplyDelete
    Replies
    1. Josh,

      With deletion actions, you need to ensure that you pay attention to referential integrity. You have to walk back the configuration by reconfiguring db elements that have a dependency on the SIP trunk(s) in question. Namely, Route Groups and Route Patterns.

      -Bill

      Delete
  4. Hi , thank you for your hard work, I have learned and use a lot in my practice. Recently I had task to add speed dial for phones with no EM logged in users, so I created sql subrequest to pull them from then put it to another request to filter by location and phone model. And Last request was to insert speed dial values to those 5xx phones. I used excel to create 5xx line of commands and send them one by one, it works. But I was looking for if this kinda multiple request with loop/condition could be written by SOAP API xml 5-10 lines of code? If yes, can you please share simple example?
    Thank you

    ReplyDelete