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.

Using SQL To Survey Phone Station Line Appearances

A reader comment on one of the entries in the SQL Query series asks the question:

I have multiple lines associated to the same phone and i'm trying to write a query to get only Line [1] "main line" ,any help please ??

We talked about querying line appearances associated phones in one of the early installments. Now we want to turn some extra knobs to focus on specific data views. I want to provide an example query to address the readers question and also touch on another, related query to show an example of how we can find anomalous data in our UCM solution. 

There are lots of ways to look at Device/Line associations. Especially if you get into the business of identifying user/line and Directory URI associations. We won't get into all of that in this installment but I think it is a good thread to follow. So, let's consider this a "Part 1" for the time being.

Using SQL to Reconfigure a Dial Plan - Updating Directory Numbers

I have had this blog entry in the draft folder for quite some time. I decided to dust it off and bring it to the front of the queue after receiving the following query on Twitter:

@ucguerrilla got one for you.  Trying to update 1xxxx and 3xxxx in pt-Internal to 401xxxxx and 403xxxxx... any idea of sql query? :)

Can you accomplish this via SQL? Why, yes you can. About a year ago I completely rebuilt a customer's dial plan using 100% SQL. While I won't be discussing the ins and outs of all of that in this entry. I do plan on getting into the mechanics of doing broad changes to digit patterns using SQL.

My Latest Project - Guerrilla Tools Sneak Peak

I came into 2014 with a goal to blog more frequently than I did in 2013. While I didn't have as lofty a goal as Tom over at networkingnerd.net, I was, shall we say, inspired. I planned on trolling through my "blog ideas" list to churn out some content.  Well, clearly the universe (or fate or whatever) had different plans and I had to adjust priorities. Free time was at a premium and I opted to work on a side project more often than adding content to the blog. Both would have been nice but coding has a calming affect. 

Yeah, I am that breed of nerd that finds solace tinkering with things like coding to center myself. We all need hobbies. Anyway, the side project I have been working on is starting to evolve into the real boy I hope it to be some day. I think it is far enough along to share with readers.

Right now, I am just calling the project "Guerrilla Tools". I'll probably rename it but that is a decision for later. The initial version is focused on functionality that complements one of the prominent series in this blog: the SQL Query Series.

Using SQL To Query Translation Patterns

For this installment of the SQL Query Series I am going to address a question posed in one of the comments I received from a reader. The reader asked: "how can I run a numplan query that shows all translation patterns with called party transformations".  Excellent question and it touches on one of the queries I use on a fairly regular basis. 

Using SQL to Report on Hunt Pilots and Line Groups

For this installment of the SQL Query Series I am going to address a question posed in one of the comments I received from a reader. The reader asked: "how can I get a list of hunt pilots and the line groups they are using".  So, we are going to provide a quick tour of how one can get at that information using SQL. 

Checking Peer Firmware Sharing using SQL

For this installment of the SQL Query Series I am going to keep it short and sweet. I was recently doing implementation planning for a project where we need to update the firmware on a few thousand phones. One of the things we like to do is leverage Peer Firmware Sharing to shorten the time needed to push out firmware upgrades. 

One of the pre-requisites to leverage Peer Firmware Sharing is to actually verify it is enabled. This is the perfect job for SQL.

CUCM SQL Queries: Finding that CSS Dependency

Sometime in May (or was it April?) I was working on a project to migrate a customer's dial plan from their legacy design to a new "normalized" (my flavor of Cisco's "globalized") dial plan. I did most of the conversion leveraging AXL/SOAP and SQL queries. One of the "interesting" issues I came across was during the clean up process. 

There was this one CSS that just didn't want to leave the party. The CUCM barked at me and said the CSS was in use. However, the Dependency Records report said it wasn't. Why? Well, the CSS table is one of the cool kids on campus. It is heavily referenced by other tables in the database. It may even be more popular than the device table. The core issue is that the dependency report is not checking every table reference.  

Apparently, I am not the only one to stumble across this issue. One of the readers of my blog posed a similar question. I figured that instead of burying my response in an obscure comment I'd post it as a new entry. I needed to add something to blog anyway. 

Using SQL to Look at EM Profile MOH Sources

In this installment of the SQL query series I wanted to actually explore a request that came by way of the Cisco Support Community. The scenario/question:

What would the query be to obtain the MOH source for DN's that are assigned to EM Profiles?

The individual posing the question was primarily interested in listing MOH sources for directory numbers associated with Extension Mobility profiles. The query to grab this data is a simple variation on a query I presented a few months ago in the blog entry: Using SQL to Look at MOH Source Configurations.

Using SQL to Look at MOH Source Configurations

In this installment of the SQL query series I wanted to actually explore a request that came from one of my customers. The scenario/question:

Is there a query that could show me the music on hold audio source configurations for phones?

The driver behind the question is that my customer has been adding a lot of phones to his Cisco Unified Communications Manager (CUCM) environment. He has a very specific MoH configuration and lately has "heard" (sometimes, literally) issues with the MoH that is being played out. So, he wants to dump some data to look for inconsistencies.  

This is pretty straightforward and may be handy to other readers. So, in the words of Ben Grimm: "It's clobberin' time" (yeah, I'm a dork).

Using SQL to Verify Configurations for CCIE-V Lab

It was bound to happen: two separate blog series have collided. Ever since I started down the path of getting the CCIE-V I have been thinking of ways to leverage the
Cisco Unified Communications Manager (CUCM) CLI to speed up the provisioning process. In most cases, I have determined that while I could do some complex provisioning from the CLI it didn't really save much time (or, in many cases, it cost more time).

That isn't to say there aren't some shortcuts available via the CLI. In fact, I think using SQL select queries from the CLI can save time when doing verification. A case in point: this past week I had a problem with RSVP between two sites that resulted from a configuration issue. I used SQL to identify the configuration issue and eventual fix in about 1.5 minutes.

Using SQL to Report on Route Lists and Groups in CUCM

In this installment of the SQL query series I wanted to actually explore a query that is inspired by one of the comments on my blog. The reader asked:

Is there a command that will show the route list, route group, and gateway a pattern points to?

Seems like a reasonable request to me. Cisco Unified Communications Manager (CUCM) doesn't provide a command that will dump this information but there are a couple of ways to get at the data and using SQL is one.  

In the toolkit I developed to survey customer sites, I have a query that does exactly that. Using SQL, or more accurately using AXL/SOAP API to access SQL queries, is the best option when bulk surveying a site for the purpose of gathering data that will be processed off line. However, there is another method that is more straightforward for a quick view of the path a pattern will take through your dial plan.

Using SQL to Fix CUCM Enterprise Services

In this installment of the SQL query series I wanted to actually explore some queries that came in handy during a CCIE-V mock lab. One of the lab questions was to disable the Corporate Directory look-up functionality on one of the phones in the lab. There are several ways to approach this. I did a write up discussing how to disable the Corporate Directory on my NetCraftsmen blog some time ago.  

It just so happens that one of my fellow classmates went the path of deleting the Enterprise Subscription completely. Of course, this broke other phones in the lab. So, what do you do? You re-add the Enterprise services and all is well, right? Not quite, this creates a problem that can only be resolved by using your handy-dandy SQL commands.

Using SQL to Validate CUCM Extension Mobility

In this installment of the SQL query series I wanted to expand on the last installment where we explored a query that listed phone subscriptions to Cisco IP Phone Services . An administrator provisions IP phone services on the Cisco Unified Communications Manager (CUCM). 

For this week's queries, we are going to take a look at ways to view a summary of how services are provisioned and an example leveraging SQL queries to identify configuration issues.

Querying IP Phone Service Subscriptions

In this installment of the SQL query series I wanted to explore IP phone service subscriptions. An administrator provisions IP phone services on the Cisco Unified Communications Manager (CUCM). To leverage these services on an IP phone, the phone must subscribe to the appropriate service, with the appropriate parameters. On this initial foray into the IP phone service arena, we will focus on reporting on service subscriptions.

I typically leverage this type of query when doing deployments or upgrades. For deployments, I have had instances where I needed to double check how devices or device profiles were provisioned. For upgrades where we are migrating to a new CUCM version, we'll use queries like this to prepare for the data move. We also query phone services when doing clean up.

Querying Fast Dials Using SQL Queries in CUCM

We suffered a lapse in our weekly series on Cisco Unified Communications Manager (CUCM) SQL Queries. Getting back on track, I wanted to take a spin off of the installment I presented on querying speed dials and dive into a related CUCM feature: Fast Dials and its partner in crime Personal Address Book (PAB).

I typically leverage this query when doing upgrades for people. I also query this data when customers feel they need to do some sort of house cleaning. Fast Dials have this uncanny way of being forgotten. I can see why. Anyway, on with the show...

Updating Speed Dials Using SQL Queries on CUCM

In this week's installment of my Cisco Unified Communications Manager (CUCM) SQL Queries series I wanted to build on last week's query with a special treat: Using the update SQL query to make changes to database entries.

This type of query is handy whenever you are looking to make system wide dial plan changes. For instance, you are going to move from a 4-digit dialing solution to a 5-digit solution or you are going to change your off-net dialing prefix for some reason. Yeah, this actually happens. In fact, it is a recent query from a colleague concerning off-net dialing prefix changes that inspired this week's installment.

Querying Speed Dials Using SQL Queries on CUCM

In this week's installment of my Cisco Unified Communications Manager (CUCM) SQL Queries series I wanted to cover querying for speed dial configurations.

This type of query is handy whenever you are looking to make system wide dial plan changes. For instance, you are going to move from a 4-digit dialing solution to a 5-digit solution or you are going to change your off-net dialing prefix for some reason. Yeah, this actually happens. In fact, it is a recent query from a colleague about change off-net dialing prefixes that inspired this week's installment.

CUCM SQL Queries: Listing Call Forward All Settings

In this week's installment of my Cisco Unified Communications Manager (CUCM) SQL Queries series I wanted to dive into the call forward all (CFA) configurations in a production environment. This seems to be a "favorite" for folks who follow my NetCraftsmen blog.

I have found that being able to see where phones are forwarded is a common need. I have seen the question come up often in the Cisco support forums and I need to query CFA status on a pretty regular basis in my own job.

CUCM SQL Query Series: List Calling Search Spaces

In last week's installment of my series on CUCM SQL queries we did a quick dip in the CDR pool. This week we are going to meander our way back to some basic configuration gathering. Specifically, we are going to run a basic query that dumps a list of calling search spaces with associated partitions.