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.
For this week's post we are going to dump a list of calling search spaces (CSS) along with assigned partitions. We are going to keep things pretty straightforward this week.
The Moving Parts
From a db perspective the following Informix database tables store information relevant to our query:
- CallingSearchSpace: As the name implies, this table contains the master list of CSS configurations on the system.
- RoutePartition: This table contains the master list of partitions on the system.
- CallingSearchSpaceMember: This is what I call a "mapping" table. Like the deviceNumPlanMap table we have seen in previous queries, the CallingSearchSpaceMember table maps partitions to calling search spaces.
The information we are looking to present is pretty straightforward. We want to list out CSS and partition configurations using the CallingSearchSpaceMember table to give us some necessary context on the relationships provisioned on the system.
The Query
Now that we have some information, we can build the query. You can run the query from the CLI using run sql [insert query]:
The following is a sample of the output one could expect from the above query:
Each partition has a single and unique entry in the RoutePartition table just as each CSS has a unique entry in the CallingSearchSpace table. The CallingSearchSpaceMember table will actually show multiple instances of both partitions and CSS. Further, the CallingSearchSpaceMember table defines the calling search spaces are partition is linked too. The sortorder field is gives us a way to order the results so that they line up with the actual priority order of individual partitions in a CSS.
There really isn't much more to it than that.
The Query
Now that we have some information, we can build the query. You can run the query from the CLI using run sql [insert query]:
select css.name as css, css.description, csm.sortorder, rp.name as partition, rp.description from callingsearchspace as css inner join callingsearchspacemember as csm on csm.fkcallingsearchspace = css.pkid inner join routepartition as rp on csm.fkroutepartition = rp.pkid order by css.name, csm.sortorder
The following is a sample of the output one could expect from the above query:
css description sortorder partition description ===================== ======================================== ========= =================== ==================================== HQ_User-Std_CSS HQ Standard User CSS 0 HQ_Tenant-Std_PT HQ Office Tenant Control Patterns HQ_User-Std_CSS HQ Standard User CSS 1 CL_Tenant-Std_PT Standard Tenant Control Patterns HQ_User-Std_CSS HQ Standard User CSS 2 CL_Svcs-Priv_PT Private Services HQ_User-Std_CSS HQ Standard User CSS 3 CL_Svcs-Corp_PT Corporate Services HQ_User-Std_CSS HQ Standard User CSS 4 HQ_Block-Std_PT COR Standard Blocking Patterns HQ_User-Std_CSS HQ Standard User CSS 5 CL_IST-Std_PT InterSwitch Call Routing Patterns HQ_User-Std_CSS HQ Standard User CSS 6 HQ_PSTN-Std_PT HQ Office Users PSTN Routing IPVC_Tenant-Std_CSS Standard CSS for IPVC-VCS 0 CL_Tenant-IST_PT PBX Tenant Control Patterns IPVC_Tenant-Std_CSS Standard CSS for IPVC-VCS 1 CL_Svcs-Corp_PT Corporate Services IPVC_Tenant-Std_CSS Standard CSS for IPVC-VCS 2 CL_PSTN-Std_PT Default PSTN Routing for Cluster
Each partition has a single and unique entry in the RoutePartition table just as each CSS has a unique entry in the CallingSearchSpace table. The CallingSearchSpaceMember table will actually show multiple instances of both partitions and CSS. Further, the CallingSearchSpaceMember table defines the calling search spaces are partition is linked too. The sortorder field is gives us a way to order the results so that they line up with the actual priority order of individual partitions in a CSS.
There really isn't much more to it than that.
Thanks for reading. If there is a specific query you are interested in seeing, let me know using the comments!
Is there a query to display the phone firmware version each phone in the cluster ?
ReplyDeleteAre you looking for firmware versions that are configured or are you looking for firmware that is actually running on the phone. They aren't always the same.
DeleteTo get a list of phones that have a special firmware assigned use the following:
run sql select name,specialloadinformation from device where specialloadinformation != ''
To see the default load:
run sql select tm.name as model,dd.loadinformation from defaults dd inner join typemodel tm on dd.tkmodel=tm.enum
The above queries give you data on what is CONFIGURED. To find out what is running I usually use a custom tool I built that queries the phones directly. Not an easy thing to explain via comments as it is all custom code. Basically, you can web to the IP phones and scrape the HTML code to parse out the relevant information.
-Bill (@ucguerrilla)
I'm looking for firmware that is actually running on the phone. Is there way to run sql for Active Load ID = firmware version name?
ReplyDeleteHey Bill,
ReplyDeleteLooking for something similar if you could give more information on what how you do these scrapes that would be great.
We have some 7925's that we need to audit.
In general, I enable HTTP access for phones from UCM. Then I have a script that webs to the phone and pulls the html. You have to customize the script to parse out the HTML. Most phone models can be parsed using the same logic. Some (like the older 7940/60/etc.) are parsed differently.
DeleteI haven't incorporated conference phones and VoWLAN devices into my script. This is because when I first put it together, these devices required authentication and I couldn't figure out how to do that correctly. So, I skipped them. I never got around to fixing or re-testing that.
-Bill
Hey Bill,
ReplyDeleteI'm looking for a similar query. I want to get a list of CSS' that have a particular name (CSS which contains "XLate_OnNet" in the name) which do not contain a certain partition.
Bill,
ReplyDeleteI am looking for a query that will give me a list of CSS that have a specific naming convention (containing "XLate_OnNet") which do not contain a particular partition (given the name).
Thanks!
Thanks for your question. For this request, you could use a query that uses the following tables:
Deletecallingsearchspace - where you will find the name of your CSS
routepartition - where you will find the name of your partition
callingsearchspacemember - where you find the linkage between the two
You may also be able to do this with just a query to the CSS table. I'd start with just the CSS first. You would use the 'name' field (name of your CSS) and the 'clause' field (contains a list of partitions in the CSS). I believe the clause field is still present in the current schema. AFAIK, the clause field is used by the digit analyzer when making a routing decision. In some ways, it is redundant to the data you can extrapolate from the callingsearchspacemember table, which is just a mapping table.
Anyway, try a query like this (assuming the partition you care about is named: MyPartition. ::
select name,description from callingsearchspace where name like '%XLate_OnNet%' and clause like '%MyPartition%'
To see what you are working with, I recommend dumping a query where you look at the native format of the clause field. You'll see what I mean when you run a query like:
select name,clause from callingsearchspace
HTH.
-Bill (@ucguerrilla)