CUCM SQL Magic (well really just queries) - Part 2

So last time we figured out how to read from the CUCM DB directly using SQL statements. This time we're going to start changing things. So like last time I recommend you take a backup and don't do anything you think could end in creating a fissure in the space time continuum.

Last time we learned that DN 1234 had a voice mail profile of 16c584ea-6a81-4138-9ea1-af452de8f75a. But maybe we want to change this, to say no voice mail. We learned that this had a PKID of NoVoiceMail 00000000-1111-0000-0000-000000000000. So we just need to update the DN to reflect this. With an easy SQL statement.


run sql update numplan set fkvoicemessagingprofile = '00000000-1111-0000-0000-000000000000' where dnorpattern = '1234'

What we've said above is update the numplan table and set the fkvoicemessagingprofile field to the value 00000000-1111-0000-0000-000000000000 where the DN is 1234. If all is well you should get the following output:

Rows: 1

Meaning that 1 row in the table was updated. If you go and check in the GUI your new voice mail profile change is reflected. Now it would be easy to break this into a nice long excel document output that in plane text and you're a copy paste away from easy DB updates that you otherwise couldn't do in the GUI.

CUCM SQL Magic (well really just queries) - Part 1

Have you ever been trying to do a mass edit of some sort in CUCM and realized that the field or way you wanted to do it wasn't available to you via the Bulk Administration menu. Or more aptly like me today "Are you @#$@ing kidding me Cisco you are epic fail!". Well then you're in luck my suffering is your gain.

The Resources
The Data Dictionary
In order to work with the CUCM DB one must know thy enemy. That's where the data dictionaries come in. If you go to Cisco.com and do a search for "Data Dictionary" you'll come up with a list of hits select the data dictionary that applies closest to your version of CUCM. Each version of CUCM does not come with data dictionary only when the Devs decide to make schema changes do you get a new one. The horribly created PDF lists each table in the CUCM DB and each column in it. With descriptions of what, who, blah blah.

SQL Cheat Sheet
If you're also like me knowing SQL is near the bottom of things I'd like to know. I'm not a programmer I'm a hack programmer. I can take something rip it apart and hope for the best. That being the case I can't remember SQL syntax from Expect syntax to PHP to HTML, so on so forth and god only knows how many other things are floating around in my brain.

So why know when I can cheat. After all it's not about knowing the answers it's about knowing where to find them. The following site is a nice condensed command reference for SQL which we'll need as CUCM uses a SQL to Informix interrupter.
http://www.sql-tutorial.net/SQL-Cheat-Sheet.pdf

The Task
Look don't touch
Now I highly recommend before you go mucking about in your CUCM DB you 1) take a backup. You might BREAK THE WORLD. 2) Why not look around before we go hacking off limbs willy nilly. So with that in mind lets use the following example.

Find a users voicemail profile based on assigned DN. Example DN = 1234

1.  Login to the CUCM publisher via SSH. (if you don't know what I'm talking about this whole blog post isn't for you probably)
2. Build our query - the syntax in the CLI is as follows: run sql
3. So in our case we want to find the voicemail profile on lines that have DN 1234 assigned. So our statement would be:
   run sql select fkvoicemessagingprofile from numplan where dnorpattern = '1234'

admin:run sql select fkvoicemessagingprofile from numplan where dnorpattern = '1234'
fkvoicemessagingprofile            
====================================
16c584ea-6a81-4138-9ea1-af452de8f75a 

What we've said above is show me (select) the field fkvoicemessagingprofile from the numplan table, where the DN (dnorpattern) is equal to 1234.

This returned the system unique identifier for the lines voicemail profile. But that's not too helpful unless you happened to remember each ID in the system. But we can look this up with another query. Instead of just looking up one lets get a list of all of them in the system. The statement would be:
run sql select name,pkid from voicemessagingprofile where pkid like '%'

admin:run sql select name,pkid from voicemessagingprofile where pkid like '%'
name        pkid                              
=========== ====================================
Default     16c584ea-6a81-4138-9ea1-af452de8f75a
NoVoiceMail 00000000-1111-0000-0000-000000000000

What we can see is above we've said. select the fields Name and PKID(unique system identifier) from the voicemessagingprofile table where the PKID is like %. If you look up SQL operators you'll learn that % is a wildcard meaning one or more characters so anything really.

What we've learned here is our number above is assigned the default voicemail profile. as the PKID matches.

Part two will cover how we change this via the CLI. Stay tuned.... Part 2

HoneyPot Go

Just a simple post to see how long my test Honey Pot takes to get compromised.

ftp://67.171.169.222

CCNP Voice Notes - Dials Peers and Matching OH MY!

Inbound Dial Peer Matching
3 elements in call setup message:

1. Called Number (DNIS)
2. Calling Number (ANI)
3. Voice Port - port the call is coming in on

Plus 5 configurable dial peer attributes processed top down only if one above fails to match:

1. Called number with incoming called-number.
• Tries matching on DNIS if multiple longest match wins (YAY)
2. Calling number with answer-address.
• Tries matching on ANI again if multiple longest match wins.
3. Calling number with destination-pattern.
• Tries to match DNIS to destination-pattern (think route pattern) if multiple yada yada.
4. Voice-port associated with the port parameter.
• Tries to match on port parameter if multiple dial peer first added in the config wins.
5. Last ditch, default dial peer.

*NOTES*

Use the answer-address command when matching the region of a caller, recommended situations:

• Callers from a given country should be directed to an appropriate group.
• Directing callers from a specific region to a regional support group.

Use the incoming called-number command whenever possible. All call setup messages include DNIS.

Outbound Dial Peer Matching

When a call arrives at a voice gateway, it uses the incoming dial string to match the destination-pattern on the outbound dial-peer. Once a match is found the port on POTS dial peers or session-target on VoIP dial peers is used to forward the call. MOST SPECIFIC MATCH WINS

Default Dial Peer (Don't be sad little guy you're important too)

Default dial peer referred to as dial-peer 0 (sounds ominous) it is used if no inbound dial peer can be matched, see list above. ONLY USED FOR INBOUND NEVER EVER EVER, (DON'T EVEN THINK IT) used for outbound. Characteristics of DP0 (Dial peer 0, DP0 sounds even more ominous) cannot be changed.

DP0 VoIP Characteristics

• G.729 / G.711
• IP precedence set to 0.
• VAD enabled
• RSVP not supported
• Fax-rate service supported

DP0 POTS Characteristics

• No applications supported
• No DID support

Avoid using DP0 when possible.

CCNP Voice Notes - Call Legs / Dial Peers

Call Legs
Every voice call to be completed must have a minimum of two call legs. An Incoming call leg and an Outgoing call leg.

POTS Call Leg – Connected to a non-VoIP network such as POTS line, PRI, BRI etc.
VoIP Call Leg – Connected to a VoIP network such as SIP, H.323.

Dial Peers 
Destination Pattern – Used to match the called telephone number.
Incoming Called-Number – ONLY Considered on selecting inbound dial-peer when it matches the original dialed/called number.
Answer-address – ONLY Considered on selecting inbound dial-peer when it matches the original calling number. 

Call Number aka DNIS (Dialed Number Identification Service)
Calling Number aka ANI (Automatic Number Identification)

Pattern	Description
0-9 A-D * #	All numbers that exist on the phone keypad.
Plus +	First character designates E.164 number; otherwise it specifies that the digit before occurred one or more times.
Period .	Matches any entered digit
%	The digit before occurred either zero or more times.
?	Repeats the digit before zero or one time. Press ctrl-v to disable context help to enter the ?
Circumflex ^	Indicates a match to the beginning of the string.
$	Matches the null string at the end of the string.
T	Makes router wait till all digits are collected. Means variable length.
Backslash \	Followed by a single character, matches that character.
Brackets [ ]	Indicates a range
Parentheses ( )	Indicates a pattern

Examples

Pattern	Matches
5555	Explicitly matches 5555
555.	Matches 5550 – 55559
555[3-9]	Matches 5553 – 5559
(555)?5	Matches 5 or 5555
5%555	Matches 555 or 55555…555
^5555$	Matches 5555
9T	Matches 9 – 999999999999 until digits are done being entered

CCNP Voice Notes - Call Processing / Deployment Models

Call Processing Agents

Agent	Recommended Size	Comments
Cisco CME	Up to 240 Phones	Dependent on Cisco IOS Platform
CUCM	50 – 30,000 Phones
Legacy PBX	Dependent on PBX

IPT Deployment Models

·         Single Site

o   Design Characteristics

§  30k SCCP / SIP phones per cluster

§  2,100 H.323 / MGCP gateways per cluster

§  DSP resources for transcoding / MTP as needed

o   Benefits

§  Ease of deployment

§  Common infrastructure for a converged solution

§  Simplified dial plan

§  No transcoding resources needed because of the use of a single high band-width codec.

·         Multi-site with centralized call processing

o   Design Characteristics

§  All single site Characteristics

§  Max 1000 locations per CUCM cluster

§  Max 150ms one-way latency to phones

o   Benefits

§  Single point of management

§  Cost savings using IP WAN for calls between sites

·         Multi-site with distributed call processing

o   Design Characteristics

§  All single site Characteristics

§  Max 1000 locations per CUCM cluster

§  Max 150ms one-way latency to phones

o   Benefits

§  Cost savings using IP WAN for calls between sites

§  Use of IP WAN for TEHO

§  No loss of functionality during WAN outage

·         CoW – Cluster over WAN

o   Design Characteristics

§  All single site Characteristics

§  Max 1000 locations per CUCM cluster

§  Max 150ms one-way latency to phones

§  Max 40ms one-way latency between cluster nodes

o   Benefits

§  Single point of Administration

§  Feature transparency

§  Extension mobility within cluster

§  Unified dial plan

The Road to CCNP Voice

So it's been a while I'll admit. Things have changed I have a new job with more focus VoIP / UC. In fact it's my task to cut over multiple CUCM clusters to 8.6 and along the way ditch Cisco Unity in favor of Microsoft Exchange 2010 UM.

So during this journey I plan to make some postings of useful tips I run across and also while I study for CCNP Voice I will use the blog to help myself retain some information.

That being said. PowerShell is pretty cool, never thought I'd say that but I've run across some useful commands to help me extract data from the AD side of the house to compare against Unity.

If you're in our boat you have Lync but your AD forest is still old. That being said PowerShell against AD is then off limits. Unless you cheat and use Lync to get the information for you.

Export list of Users from Lync

Import-Module Lync
Get-CsAdUser -OU "ou=Users,ou=Location,dc=blah,dc=domain,dc=com" | Select-Object SamAccountName,FirstName,LastName,Phone | Out-File C:\Users\person\Desktop\users.txt

What that lovely  command above will do is, extract all the users from the OU in a nice Tab separated file on the desktop with the following fields: Account Name, First Name, Last Name, Primary Phone Number.

Stay tuned for more details as I lose my mind during the migration and cram some CCNP Voice knowledge in. First up is the CVoice book.